使用group-by获取值之和

zqdjd7g9  于 2021-06-21  发布在  Mysql
关注(0)|答案(1)|浏览(581)

这个问题在这里已经有答案了

同一列的两个where条件使用group by(2个答案)
两年前关门了。
我有一张t2桌。我需要 vqty 分组依据 from_br 以及 to_br 作为两列。我的table在下面。我的数据库架构在SQLFIDLE中。
t1级:

id    branch_name
-------------------
1     branch1
2     branch2
3     branch3
4     branch4
5     branch5
6     branch6

t2段:

id    from_br    to_br     qty
--------------------------------
1     1         3          10
2     2         4          20
3     3         2          30
4     2         3          40
5     1         4          50 
6     4         5          60

期望结果:

branch_name    send        received
-------------------------------
branch1        60          0          
branch2        60          30
branch3        30          50
branch4        60          70
branch5        0           60

我尝试的查询是:

SELECT t1.branch_name, 
   COALESCE(SUM(send.vqty), 0) AS send, 
   COALESCE(SUM(receive.vqty), 0) AS received 
FROM t1  
LEFT JOIN t2 AS send on t1.id = send.from_br  
LEFT JOIN t2 AS receive on t1.id = receive.to_br 
GROUP BY t1.branch_name, send.from_br, receive.from_br
agxfikkp

agxfikkp1#

我在下面感谢你可以帮助你

SELECT t.branch_name,  COALESCE(SUM(t.send), 0) AS send, 
    COALESCE(SUM(received), 0) AS received  from  (SELECT t1.branch_name, 
           COALESCE(SUM(send.vqty), 0) AS send, 
           COALESCE(SUM(receive.vqty), 0) AS received 
        FROM t1  
        LEFT JOIN t2 AS send on t1.id = send.from_br  
        LEFT JOIN t2 AS receive on t1.id = receive.to_br 
        GROUP BY t1.branch_name, send.from_br, receive.from_br) as t 
        GROUP BY t.branch_name

检查http://sqlfiddle.com/#!9/af0973/7号

相关问题