sql select查询下面的表 [英] sql select query for below tables
问题描述
表1
T1_id ---数量
1 ---------- 5
2 ---------- 3
表2
T2_id --- T1_id ---数量
1 -------- 1 -------- 2
2 -------- 1 ------ - 1
3 -------- 2 -------- 1
条件T1_id主键表1中的表1和表2中的外键
结果
T2_id --- T1_id ---数量
1 -------- 1 -------- 2
2 -------- 1 -------- 1
0 -------- 1 -------- 2 //Table1.Quantity(Sum(Table2.Quantity)where Table2.T1_id = Table1.T1_id)
3 -------- 2 -------- 1
0 -------- 2 -------- 2 //表1 .Quantity(Sum(Table2.Quantity)其中Table2.T1_id = Table1.T1_id)
Table1
T1_id---Quantity
1----------5
2----------3
Table2
T2_id---T1_id---Quantity
1--------1--------2
2--------1--------1
3--------2--------1
Condition T1_id primary key in Table1 and Foreign key in Table2
Result
T2_id---T1_id---Quantity
1--------1--------2
2--------1--------1
0--------1--------2 //Table1.Quantity(Sum(Table2.Quantity)where Table2.T1_id=Table1.T1_id)
3--------2--------1
0--------2--------2 //Table1.Quantity(Sum(Table2.Quantity)where Table2.T1_id=Table1.T1_id)
推荐答案
SELECT T2_id , T1_id , Quantity
FROM (
select 1 AS ORD, T2_id , T1_id , Quantity from Table2
UNION ALL
select 2 AS ORD,0 AS T2_id , T1_id , SUM(Quantity ) AS Quantity
from Table2 GROUP BY T1_id
) AS P
ORDER BY T1_id, ORD
这篇关于sql select查询下面的表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!