文档建议反馈控制台
首页
学习
活动
专区
圈层
工具
MCP广场
文章/答案/技术大牛
发布
社区首页 >问答首页 >如何对应MySQL5.7行

问如何对应MySQL5.7行
EN

Stack Overflow用户
提问于 2020-08-26 12:35:54
回答 2查看 86关注 0票数 0

我有来自2个查询的2个结果，

这是查询1

代码语言：javascript
复制
(SELECT 
        COUNT(DISTINCT om.createdby) AS pul_org,
            SUM(om.quantity) AS order_pul_kg,
            COUNT(om.id) AS order_pul_x
    FROM
        order_match om
    Inner JOIN (SELECT 
        s1.createdby
    FROM
        order_match s1
    GROUP BY s1.createdby
    HAVING SUM(s1.order_status_id != 7
        AND s1.createdAt <= '2020-04-30 00:00:00') > 1
        AND SUM(s1.order_status_id != 7
        AND s1.createdAt BETWEEN '2020-04-01 00:00:00' AND '2020-04-30 00:00:00')) clients ON om.createdby = clients.createdby
    WHERE
        order_status_id IN (4 , 5, 6, 8));

查询1的结果是这样的(这只是一个虚拟结果)

代码语言：javascript
复制
+---------+--------------+-------------+
| pul_org | order_pul_kg | order_pul_x |
+---------+--------------+-------------+
|       6 |         2345 |         428 |
+---------+--------------+-------------+

这是查询2

代码语言：javascript
复制
(SELECT 
        COALESCE(customer_regency, 'total') AS `Kabupaten/Kota_PUL`,
            SUM(quantity) AS `Qty_PUL(kg)`,
            ROUND(SUM(quantity) / ANY_VALUE(totalsum) * 100, 1) AS `Qty_PUL(%)`,
            COUNT(order_match.id) AS `Jumlah Order_PUL`,
            ROUND(COUNT(order_match.id) / ANY_VALUE(totalcount) * 100, 1) AS `Jumlah Order_PUL(%)`
    FROM
        order_match
    INNER JOIN air_way_bills ON order_match.code_order = air_way_bills.code_order
    INNER JOIN (SELECT 
        s1.createdby
    FROM
        order_match s1
    GROUP BY s1.createdby
    HAVING SUM(s1.order_status_id != 7
        AND s1.createdAt <= '2020-04-30 00:00:00') > 1
        AND SUM(s1.order_status_id != 7
        AND s1.createdAt BETWEEN '2020-04-01 00:00:00' AND '2020-04-30 00:00:00')) clients ON order_match.createdby = clients.createdby
    JOIN (SELECT 
        SUM(quantity) totalsum, COUNT(order_match.id) totalcount
    FROM
        order_match
    INNER JOIN ((SELECT 
        s1.createdby
    FROM
        order_match s1
    GROUP BY s1.createdby
    HAVING SUM(s1.order_status_id != 7
        AND s1.createdAt <= '2020-04-30 00:00:00') > 1
        AND SUM(s1.order_status_id != 7
        AND s1.createdAt BETWEEN '2020-04-01 00:00:00' AND '2020-04-30 00:00:00'))) clients ON order_match.createdby = clients.createdby
    WHERE
        order_status_Id IN (4 , 5, 6, 8)
            AND createdAt BETWEEN '2020-04-01 00:00:00' AND '2020-04-30 00:00:00') totals
    WHERE
        order_status_Id IN (4 , 5, 6, 8)
        and order_match.createdAt BETWEEN '2020-04-01 00:00:00' AND '2020-04-30 00:00:00'
    GROUP BY customer_regency WITH ROLLUP) AS PULRegency;

查询2的结果如下所示(这只是一个虚拟结果)

代码语言：javascript
复制
+----------------+--------------+------------+-------------------+---------------------+
| kabupaten/kota | qty_PUL (kg) | Qty_PUL(%) | jumlah_order_PUL  | jumlah_order_pul(%) |
+----------------+--------------+------------+-------------------+---------------------+
| kota a         |           20 |         20 |                 1 |                  20 |
| kota b         |           40 |         40 |                 2 |                  40 |
| kota c         |           40 |         40 |                 2 |                  40 |
| total          |          100 |        100 |                 4 |                 100 |
+----------------+--------------+------------+-------------------+---------------------+

所以我想把这个结果合并到1个表中这是预期的结果

代码语言：javascript
复制
+---------+--------------+-------------+----------------+--------------+------------+-------------------+---------------------+
| pul_org | order_pul_kg | order_pul_x | kabupaten/kota | qty_PUL (kg) | Qty_PUL(%) | jumlah_order_PUL  | jumlah_order_pul(%) |
+---------+--------------+-------------+----------------+--------------+------------+-------------------+---------------------+
|       6 |      2345.31 |         428 | kota a         |           20 |         20 |                 1 |                  20 |
|         |              |             | kota b         |           40 |         40 |                 2 |                  40 |
|         |              |             | kota c         |           40 |         40 |                 2 |                  40 |
|         |              |             | total          |          100 |        100 |                 4 |                 100 |
+---------+--------------+-------------+----------------+--------------+------------+-------------------+---------------------+

根据预期结果，我进行以下查询

代码语言：javascript
复制
SELECT 
    *
FROM
    (SELECT 
        COUNT(DISTINCT om.createdby) AS pul_org,
            SUM(om.quantity) AS order_pul_kg,
            COUNT(om.id) AS order_pul_x
    FROM
        order_match om
    Inner JOIN (SELECT 
        s1.createdby
    FROM
        order_match s1
    GROUP BY s1.createdby
    HAVING SUM(s1.order_status_id != 7
        AND s1.createdAt <= '2020-04-30 00:00:00') > 1
        AND SUM(s1.order_status_id != 7
        AND s1.createdAt BETWEEN '2020-04-01 00:00:00' AND '2020-04-30 00:00:00')) clients ON om.createdby = clients.createdby
    WHERE
        order_status_id IN (4 , 5, 6, 8)) AS PULOrder
        cross JOIN
    (SELECT 
        COALESCE(customer_regency, 'total') AS `Kabupaten/Kota_PUL`,
            SUM(quantity) AS `Qty_PUL(kg)`,
            ROUND(SUM(quantity) / ANY_VALUE(totalsum) * 100, 1) AS `Qty_PUL(%)`,
            COUNT(order_match.id) AS `Jumlah Order_PUL`,
            ROUND(COUNT(order_match.id) / ANY_VALUE(totalcount) * 100, 1) AS `Jumlah Order_PUL(%)`
    FROM
        order_match
    INNER JOIN air_way_bills ON order_match.code_order = air_way_bills.code_order
    INNER JOIN (SELECT 
        s1.createdby
    FROM
        order_match s1
    GROUP BY s1.createdby
    HAVING SUM(s1.order_status_id != 7
        AND s1.createdAt <= '2020-04-30 00:00:00') > 1
        AND SUM(s1.order_status_id != 7
        AND s1.createdAt BETWEEN '2020-04-01 00:00:00' AND '2020-04-30 00:00:00')) clients ON order_match.createdby = clients.createdby
    JOIN (SELECT 
        SUM(quantity) totalsum, COUNT(order_match.id) totalcount
    FROM
        order_match
    INNER JOIN ((SELECT 
        s1.createdby
    FROM
        order_match s1
    GROUP BY s1.createdby
    HAVING SUM(s1.order_status_id != 7
        AND s1.createdAt <= '2020-04-30 00:00:00') > 1
        AND SUM(s1.order_status_id != 7
        AND s1.createdAt BETWEEN '2020-04-01 00:00:00' AND '2020-04-30 00:00:00'))) clients ON order_match.createdby = clients.createdby
    WHERE
        order_status_Id IN (4 , 5, 6, 8)
            AND createdAt BETWEEN '2020-04-01 00:00:00' AND '2020-04-30 00:00:00') totals
    WHERE
        order_status_Id IN (4 , 5, 6, 8)
        and order_match.createdAt BETWEEN '2020-04-01 00:00:00' AND '2020-04-30 00:00:00'
    GROUP BY customer_regency WITH ROLLUP) AS PULRegency;

但是为什么结果是这样的呢？

代码语言：javascript
复制
+---------+--------------+-------------+----------------+--------------+------------+-------------------+---------------------+
| pul_org | order_pul_kg | order_pul_x | kabupaten/kota | qty_PUL (kg) | Qty_PUL(%) | jumlah_order_PUL  | jumlah_order_pul(%) |
+---------+--------------+-------------+----------------+--------------+------------+-------------------+---------------------+
|       6 |      2345.31 |         428 | kota a         |           20 |         20 |                 1 |                  20 |
|       6 |      2345.31 |         428 | kota b         |           40 |         40 |                 2 |                  40 |
|       6 |      2345.31 |         428 | kota c         |           40 |         40 |                 2 |                  40 |
|       6 |      2345.31 |         428 | total          |          100 |        100 |                 4 |                 100 |
+---------+--------------+-------------+----------------+--------------+------------+-------------------+------------------

这不是我想要的结果，因为行与我的预期结果不同

mysql
EN

回答 2

Stack Overflow用户

回答已采纳

发布于 2020-08-31 21:26:18

我同意最好的解决方案是使用客户端软件，但您可以尝试这个查询。对于我来说，它可以使用更简单的查询，但仍然使用交叉连接

您将再有一个输出字段(OneIncrementingField)。在脚本的末尾，@a初始化

代码语言：javascript
复制
SELECT
    @a:=@a+1 OneIncrementingField ,
    if(@a=1,pul_org ,'') pul_org,
    if(@a=1,order_pul_kg ,'')  order_pul_kg,
    if(@a=1,order_pul_x ,'') order_pul_x,
    PULRegency.*
FROM
    (SELECT 
        COUNT(DISTINCT om.createdby) AS pul_org,
            SUM(om.quantity) AS order_pul_kg,
            COUNT(om.id) AS order_pul_x
    FROM
        order_match om
    Inner JOIN (SELECT 
        s1.createdby
    FROM
        order_match s1
    GROUP BY s1.createdby
    HAVING SUM(s1.order_status_id != 7
        AND s1.createdAt <= '2020-04-30 00:00:00') > 1
        AND SUM(s1.order_status_id != 7
        AND s1.createdAt BETWEEN '2020-04-01 00:00:00' AND '2020-04-30 00:00:00')) clients ON om.createdby = clients.createdby
    WHERE
        order_status_id IN (4 , 5, 6, 8)) AS PULOrder
        cross JOIN
    (SELECT 
        COALESCE(customer_regency, 'total') AS `Kabupaten/Kota_PUL`,
            SUM(quantity) AS `Qty_PUL(kg)`,
            ROUND(SUM(quantity) / ANY_VALUE(totalsum) * 100, 1) AS `Qty_PUL(%)`,
            COUNT(order_match.id) AS `Jumlah Order_PUL`,
            ROUND(COUNT(order_match.id) / ANY_VALUE(totalcount) * 100, 1) AS `Jumlah Order_PUL(%)`
    FROM
        order_match
    INNER JOIN air_way_bills ON order_match.code_order = air_way_bills.code_order
    INNER JOIN (SELECT 
        s1.createdby
    FROM
        order_match s1
    GROUP BY s1.createdby
    HAVING SUM(s1.order_status_id != 7
        AND s1.createdAt <= '2020-04-30 00:00:00') > 1
        AND SUM(s1.order_status_id != 7
        AND s1.createdAt BETWEEN '2020-04-01 00:00:00' AND '2020-04-30 00:00:00')) clients ON order_match.createdby = clients.createdby
    JOIN (SELECT 
        SUM(quantity) totalsum, COUNT(order_match.id) totalcount
    FROM
        order_match
    INNER JOIN ((SELECT 
        s1.createdby
    FROM
        order_match s1
    GROUP BY s1.createdby
    HAVING SUM(s1.order_status_id != 7
        AND s1.createdAt <= '2020-04-30 00:00:00') > 1
        AND SUM(s1.order_status_id != 7
        AND s1.createdAt BETWEEN '2020-04-01 00:00:00' AND '2020-04-30 00:00:00'))) clients ON order_match.createdby = clients.createdby
    WHERE
        order_status_Id IN (4 , 5, 6, 8)
            AND createdAt BETWEEN '2020-04-01 00:00:00' AND '2020-04-30 00:00:00') totals
    WHERE
        order_status_Id IN (4 , 5, 6, 8)
        and order_match.createdAt BETWEEN '2020-04-01 00:00:00' AND '2020-04-30 00:00:00'
    GROUP BY customer_regency WITH ROLLUP) AS PULRegency , (select @a:=0) as P;
票数 1
EN

Stack Overflow用户

发布于 2020-08-28 15:14:07

根据您共享的输入，您获得的交叉联接的输出是正确的。这就是交叉联接所做的事情；它将一个表中的所有行映射到其他表的每一行。

现在，如果您想进一步对行进行分组，可以执行groupby pul_org。您的查询应如下所示：

代码语言：javascript
复制
select pul_org, group_concat(kabupaten/kota)
from (
  <your big cross join query>
)
group by pul_org;

此外，如果您有大量数据，交叉连接本身就非常耗费资源；在其上执行group by将使查询性能变得更差。

票数 -1
EN
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：

https://stackoverflow.com/questions/63590577

复制
相关文章

相似问题

领券

腾讯云开发者

扫码关注腾讯云开发者

扫码关注腾讯云开发者

领取腾讯云代金券

热门产品

热门推荐

更多推荐

Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有 

深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 粤公网安备44030502008569号

腾讯云计算（北京）有限责任公司 京ICP证150476号 |  京ICP备11018762号

问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档