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

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

--查询销量、库存的查询汇总语句不用group by
--> 测试数据：@Product
declare @Product table([PID] int,[Name] varchar(7))
insert @Product
select 1,'Nugget' union all
select 2,'Seafood'

--> 测试数据：@Buy
declare @Buy table([Date] varchar(3),[PID] int,[Qty] int)
insert @Buy
select '1/7',1,10 union all
select '2/7',2,20 union all
select '3/7',1,10

--> 测试数据：@Sale
declare @Sale table([Date] varchar(3),[PID] int,[Qty] int)
insert @Sale
select '5/7',1,2 union all
select '5/7',2,3 union all
select '6/7',1,4 union all
select '6/7',2,5

select [Name],
     (select sum(Qty) from @Buy where PID=t.PID) as  BuyQty,
     (select sum(Qty) from @Sale where PID=t.PID) as  SaleQty,
     (select sum(Qty) from @Buy where PID=t.PID)-
     (select sum(Qty) from @Sale where PID=t.PID) as Balance
from @Product t