笔记206 查询销量、库存的查询汇总语句不用group by
1 --查询销量、库存的查询汇总语句不用group by
2 --> 测试数据：@Product
3 declare @Product table([PID] int,[Name] varchar(7))
4 insert @Product
5 select 1,'Nugget' union all
6 select 2,'Seafood'
7
8 --> 测试数据：@Buy
9 declare @Buy table([Date] varchar(3),[PID] int,[Qty] int)
10 insert @Buy
11 select '1/7',1,10 union all
12 select '2/7',2,20 union all
13 select '3/7',1,10
14
15 --> 测试数据：@Sale
16 declare @Sale table([Date] varchar(3),[PID] int,[Qty] int)
17 insert @Sale
18 select '5/7',1,2 union all
19 select '5/7',2,3 union all
20 select '6/7',1,4 union all
21 select '6/7',2,5
22
23 select [Name],
24 (select sum(Qty) from @Buy where PID=t.PID) as BuyQty,
25 (select sum(Qty) from @Sale where PID=t.PID) as SaleQty,
26 (select sum(Qty) from @Buy where PID=t.PID)-
27 (select sum(Qty) from @Sale where PID=t.PID) as Balance
28 from @Product t
