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屋！