笔记206 查询销量、库存的查询汇总语句不用group by

笔记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

 

posted @ 2013-08-04 13:27  桦仔  阅读(383)  评论(0)    收藏  举报