使用分组依据的同一列的两个条件 [英] 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屋!
查看全文