注:产品型号A的产品编码分为A1,A2其实就是一个产品,
每个产品型号都可能会有多个产品编码但是库存和订购数都是按产品型号来的所以产品型号A的库存为30产品表
字段(产品型号,产品编码,库存,单价)
A A1 10 100
A A2 20 100
B B1 20 100
C C1 20 100订单表
字段(产品型号,订购数量)
A 3
A 2
B 5
查询出结果为
产品型号,库存,订购数量,可订购数量,单价
A 30 5 25 100
B 20 5 15 100
C 20 0 20 100
每个产品型号都可能会有多个产品编码但是库存和订购数都是按产品型号来的所以产品型号A的库存为30产品表
字段(产品型号,产品编码,库存,单价)
A A1 10 100
A A2 20 100
B B1 20 100
C C1 20 100订单表
字段(产品型号,订购数量)
A 3
A 2
B 5
查询出结果为
产品型号,库存,订购数量,可订购数量,单价
A 30 5 25 100
B 20 5 15 100
C 20 0 20 100
insert [产品表]
select 'A','A1',10,100 union all
select 'A','A2',20,100 union all
select 'B','B1',20,100 union all
select 'C','C1',20,100
create table [订单表]([产品型号] varchar(1),[订购数量] int)
insert [订单表]
select 'A',3 union all
select 'A',2 union all
select 'B',5
go
--SQL:
SELECT a.产品型号, a.库存, b.订购数量, b.可订购数量, a.单价 FROM
(SELECT 产品型号, 库存=SUM(库存),单价 FROM [产品表] GROUP BY 产品型号, 单价) a --改一下即可: 库存=SUM(库存)
CROSS APPLY
(SELECT 订购数量=ISNULL(SUM(订购数量), 0),可订购数量=a.库存-ISNULL(SUM(订购数量),0) FROM [订单表] WHERE 产品型号 = a.产品型号) b
/*
产品型号 库存 订购数量 可订购数量 单价
---- ----------- ----------- ----------- -----------
A 30 5 25 100
B 20 5 15 100
C 20 0 20 100
*/
insert [产品表]
select 'A','A1',10,100 union all
select 'A','A2',20,100 union all
select 'B','B1',20,100 union all
select 'C','C1',20,100
create table [订单表]([产品型号] varchar(1),[订购数量] int)
insert [订单表]
select 'A',3 union all
select 'A',2 union all
select 'B',5
go
select a.产品型号,isnull(b.库存,0) as 库存,isnull(c.订购数量,0) as 订购数量,
isnull(b.库存,0)-isnull(c.订购数量,0) as 可订购数量,b.单价 from (select 产品型号 from 产品表
union
select 产品型号 from 订单表) a
left join (select 产品型号,sum(库存) as 库存,单价 from 产品表 group by 产品型号,单价) b on a.产品型号=b.产品型号
left join (select 产品型号,sum(订购数量) as 订购数量 from 订单表 group by 产品型号) c on a.产品型号=c.产品型号/*
产品型号 库存 订购数量 可订购数量 单价
---- ----------- ----------- ----------- -----------
A 30 5 25 100
B 20 5 15 100
C 20 0 20 100
select a.产品型号,a.库存,isnull(b.订购数量,0),(a.库存-isnull(b.订购数量,0)) as 可订购数量, a.单价
from
(select 产品型号,sum(库存) as 库存,单价 from 产品表 group by 产品型号,单价) a
left join
(select 产品型号,sum(订购数量) as 订购数量 from 订单表 group by 产品型号) b
on a.产品型号=b.产品型号
可订购数=a.库存-isnull(b.订购数量,0),a.单价 FROM
(SELECT 产品型号,库存=sum(库存),单价=max(单价) FROM #p group by 产品型号)a
left JOIN
(SELECT 产品型号,订购数量=sum(订购数量) FROM #o group by 产品型号)b
ON a.产品型号=b.产品型号
isnull(a.产品型号,b.产品型号),isnull(a.库存,0),b.订购数量,isnull(a.库存,0)-isnull(b.订购数量,0) as 可订购数量,a.单价
from
(select 产品型号,sum(库存) as 库存,单价 from 产品表 group by 产品型号,单价)a
full join
(select 产品型号,sum(订购数量) as 订购数量 from 订单表 group by 产品型号)b
on
a.产品型号=b.产品型号