同一列的两个where条件使用groupby

xvw2m8pv 于 2021-06-21 发布在 Mysql

关注(0)|答案(2)|浏览(391)

我有两张table，t1，t2。我的表格和预期结果如下。我的表架构在SQLFIDLE中
t1级：

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

t2段：

id    VBRNCH    VTOBRN    vqty
1     1         0         10
2     2         0         20
3     3         0         30
4     0         4         40
5     0         5         50

预期结果是：

branch_name    send    received
1               10     0
2               20     0
3               30     0
4               0      40
5               0      50

我试过的是：

SELECT
  b1.branch_name,
  i1.vqty AS send,
  i2.vqty AS received
FROM t2 i1
INNER
JOIN t1 b1
  ON b1.id = i1.VBRNCH
INNER JOIN t2 i2
  ON b1.id = i2.VTOBRN
GROUP
BY i1.VTOBRN,
   i2.VBRNCH;

但我现在没有排了。

mysql

来源：https://stackoverflow.com/questions/48110199/two-where-condition-for-same-column-using-group-by

2条答案

按热度按时间

k10s72fa1#

例如

SELECT x.*
     , COALESCE(sent,0) sent
     , COALESCE(received,0) received
  FROM t1 x
  LEFT 
  JOIN 
     ( SELECT from_br
            , SUM(vqty) sent
         FROM t2
        GROUP
           BY from_br
     ) a
    ON a.from_br = x.id
  LEFT
  JOIN
     ( SELECT to_br
            , SUM(vqty) received
         FROM t2
        GROUP
           BY to_br
     ) b
    ON b.to_br = x.id
 ORDER
    BY id;

http://sqlfiddle.com/#!9/af0973/21号

赞(0）回复(0）举报 2021-06-21

kgqe7b3p2#

我想这就是你要找的问题：

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.VBRNCH  
LEFT JOIN t2 AS receive on t1.id = receive.VTOBRN 
GROUP BY t1.branch_name

此处演示

赞(0）回复(0）举报 2021-06-21

我来回答

同一列的两个where条件使用groupby

2条答案

相关问题

热门标签

最新问答