表1:Table_IN
字段:id,goods_ID(商品ID),Pack_Spec_ID(规格ID),Pack_Spec_Number(该规格的数量)
1,1,1,5
2,1,2,10
3,1,3,40
4,1,3,40
表2:Table_OUT
字段:id,goods_ID(商品ID),Pack_Spec_ID(规格ID),Pack_Spec_Number(该规格的数量)
1,1,1,4
2,1,3,60
结果为:
1,1,1,1
2,1,2,10
3,1,3,20
该如何实现?
------解决方案--------------------
Create View V_TEST
As
Select goods_ID, Pack_Spec_ID, Pack_Spec_Number_IN - IsNull(Pack_Spec_Number_OUT, 0) As Pack_Spec_Number
From
(Select goods_ID, Pack_Spec_ID, SUM(Pack_Spec_Number) As Pack_Spec_Number_IN From Table_IN Group By goods_ID, Pack_Spec_ID)
A
Left Join
(Select goods_ID, Pack_Spec_ID, SUM(Pack_Spec_Number) As Pack_Spec_Number_OUT From Table_OUT Group By goods_ID, Pack_Spec_ID)
B
On A.goods_ID = B.goods_ID And A.Pack_Spec_ID = B.Pack_Spec_ID
GO
------解决方案--------------------
select
a.goods_ID,a.Pack_Spec_ID,a.num-isnull(b.num,0) as Pack_Spec_Number
from
(select goods_ID,Pack_Spec_ID,sum(Pack_Spec_Number) as num from Table_IN group by goods_ID,Pack_Spec_ID) a
left join
(select goods_ID,Pack_Spec_ID,sum(Pack_Spec_Number) as num from Table_OUT group by goods_ID,Pack_Spec_ID) a
on
a.goods_ID=b.goods_ID and a.Pack_Spec_ID=b.Pack_Spec_ID
------解决方案--------------------
--创建建测试环境
create table Table_IN(id int,goods_ID int,Pack_Spec_ID int,Pack_Spec_Number int)
create table Table_OUT(id int,goods_ID int,Pack_Spec_ID int,Pack_Spec_Number int)
--插入测试数据
insert Table_IN(id,goods_ID,Pack_Spec_ID,Pack_Spec_Number)
select '1 ', '1 ', '1 ', '5 ' union all
select '2 ', '1 ', '2 ', '10 ' union all
select '3 ', '1 ', '3 ', '40 ' union all
select '4 ', '1 ', '3 ', '40 '
insert Table_OUT(id,goods_ID,Pack_Spec_ID,Pack_Spec_Number)
select '1 ', '1 ', '1 ', '4 ' union all
select '2 ', '1 ', '3 ', '60 '
go
--求解过程
create view T as
select goods_ID,Pack_Spec_ID,sum(Pack_Spec_Number) as Pack_Spec_Number
from (
select goods_ID,Pack_Spec_ID,Pack_Spec_Number from Table_IN
union all
select goods_ID,Pack_Spec_ID,-Pack_Spec_Number from Table_OUT
) _x
group by goods_ID,Pack_Spec_ID
go
select * from T
--删除测试环境
drop table Table_IN,Table_OUT
drop view T
/*--测试结果
goods_ID Pack_Spec_ID Pack_Spec_Number
----------- ------------ ----------------
1 1 1
1 2 10
1 3 20
*/