使用分组依据的同一列的两个条件 [英] Two where condition for same column using group by

问题描述

我有两个表t1，t2.我的表格和预期结果如下. 我的表架构位于 sqlfiddle

I am having two tables, t1, t2. My tables and expected result are given below. My table schema is in sqlfiddle

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;

但是我得到零行.

推荐答案

我认为这是您要查找的查询:

I think this is the query you are looking for:

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

> 此处演示

这篇关于使用分组依据的同一列的两个条件的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！