使用GROUP BY统计各分支收发数量的SQL查询需求及问题

你的需求是按每个分支统计发送和接收的qty总和，但原查询的问题出在GROUP BY子句包含了多余的字段，导致分组逻辑错误，无法正确汇总数据。让我帮你修正这个问题，同时提供几种高效的解决方案：

问题分析 #

原查询中，你对t2做了两次左连接分别获取发送和接收记录，但GROUP BY t1.branch_name, send.from_br, receive.from_br会把每个分支的发送、接收记录拆分成独立的分组，甚至因为JOIN产生的笛卡尔积导致总和计算错误。我们需要调整分组逻辑，确保每个分支只作为一个分组来统计。

解决方案1：子查询分别统计发送/接收（直观易懂） #

先分别统计每个分支的发送总量和接收总量，再关联到t1表获取分支名称：

SELECT 
    t1.branch_name,
    COALESCE(send_sum.send, 0) AS send,
    COALESCE(receive_sum.received, 0) AS received
FROM t1
LEFT JOIN (
    -- 统计每个分支的发送总量
    SELECT from_br, SUM(qty) AS send
    FROM t2
    GROUP BY from_br
) send_sum ON t1.id = send_sum.from_br
LEFT JOIN (
    -- 统计每个分支的接收总量
    SELECT to_br, SUM(qty) AS received
    FROM t2
    GROUP BY to_br
) receive_sum ON t1.id = receive_sum.to_br
ORDER BY t1.id;

解决方案2：条件聚合（高效性能） #

使用CASE WHEN进行条件求和，只需要扫描t2表一次，性能更优，适合数据量较大的场景：

SELECT 
    t1.branch_name,
    -- 当当前分支是发送方时，累加qty，否则为0
    SUM(CASE WHEN t2.from_br = t1.id THEN t2.qty ELSE 0 END) AS send,
    -- 当当前分支是接收方时，累加qty，否则为0
    SUM(CASE WHEN t2.to_br = t1.id THEN t2.qty ELSE 0 END) AS received
FROM t1
LEFT JOIN t2 ON t1.id IN (t2.from_br, t2.to_br)
GROUP BY t1.id, t1.branch_name
ORDER BY t1.id;

解决方案3：关联子查询（简洁写法） #

直接在SELECT子句中通过子查询获取每个分支的发送和接收总量，代码更简洁：

SELECT 
    branch_name,
    (SELECT COALESCE(SUM(qty), 0) FROM t2 WHERE from_br = t1.id) AS send,
    (SELECT COALESCE(SUM(qty), 0) FROM t2 WHERE to_br = t1.id) AS received
FROM t1
ORDER BY id;

以上三种方法都能得到你期望的结果：

内容的提问来源于stack exchange，提问作者Kannan K