declare @ta table(供应商id int,进货数量 int,进货日期 datetime,商品id int)
insert into @ta select 100,300,'01/02/2004',45
insert into @ta select 213,154,'02/04/2005',47
insert into @ta select 417,78 ,'04/02/2004',45
insert into @ta select 789,3 ,'06/07/2004',678
insert into @ta select 425,41 ,'02/07/2004',14
insert into @ta select 213,85 ,'08/07/2005',14
declare @tb table(客户id int,销售数量 int,销售日期 datetime,商品id int)
insert into @tb select 457,41 ,'05/14/2004',45
insert into @tb select 695,52 ,'06/24/2005',47
insert into @tb select 247,265,'04/05/2005',45select
a.供应商id,
销售数量=case when (a.sum_num+a.进货数量)>b.sum_num then b.sum_num-a.sum_num else a.进货数量 end,
a.商品id
from
(select c.*,isnull((select sum(进货数量) from @ta where 商品id=c.商品id and 进货日期<c.进货日期),0) sum_num from @ta c) a,
(select 商品id,sum(销售数量) as sum_num from @tb group by 商品id) b
where
a.商品id=b.商品id
and
a.sum_num<b.sum_num
order by
a.进货日期/*
供应商id 销售数量 商品id
----------- ----------- -----------
100 300 45
417 6 45
213 52 47
*/
insert into @ta select 100,300,'01/02/2004',45
insert into @ta select 213,154,'02/04/2005',47
insert into @ta select 417,78 ,'04/02/2004',45
insert into @ta select 789,3 ,'06/07/2004',678
insert into @ta select 425,41 ,'02/07/2004',14
insert into @ta select 213,85 ,'08/07/2005',14
declare @tb table(客户id int,销售数量 int,销售日期 datetime,商品id int)
insert into @tb select 457,41 ,'05/14/2004',45
insert into @tb select 695,52 ,'06/24/2005',47
insert into @tb select 247,265,'04/05/2005',45select
a.供应商id,
销售数量=case when (a.sum_num+a.进货数量)>b.sum_num then b.sum_num-a.sum_num else a.进货数量 end,
a.商品id
from
(select c.*,isnull((select sum(进货数量) from @ta where 商品id=c.商品id and 进货日期<c.进货日期),0) sum_num from @ta c) a,
(select 商品id,sum(销售数量) as sum_num from @tb group by 商品id) b
where
a.商品id=b.商品id
and
a.sum_num<b.sum_num
order by
a.进货日期/*
供应商id 销售数量 商品id
----------- ----------- -----------
100 300 45
417 6 45
213 52 47
*/
进货表:
供应商id 进货数量 进货日期 商品id
100 300 1/2/2004 45
213 154 2/4/2005 47
417 78 4/2/2004 45销售表:
客户id 销售数量 销售日期 商品id
457 41 5/14/2004 45
695 52 6/24/2005 47
247 265 4/5/2005 45
现在是要统计出从每个供应商进货来的书的销售情况,因为一种商品可能由2个或2个以上的
供应商供应的,所以造成这样一个难点,如上面商品id是45的那种商品,销售过两次,进货过两次,
销售有306本,由于进货先是id为100的供应商进300本,所以41+265=306-300=6,这6本就属于供应商为417的销售供应商id 销售数量 商品id
100 300 45
417 6 45
213 52 47
(
供应商Id int,
进货数量 int,
进货日期 datetime,
商品Id int
)create table C
(
客户Id int,
销售数量 int,
销售日期 datetime,
商品Id int
)insert R select 100,300,'2004-1-2',45
insert R select 213,154,'2005-2-4',47
insert R select 417,78,'2004-4-2',45
insert R select 3,3,'2004-6-7',678
insert R select 41,41,'2004-2-7',14
insert R select 85,85,'2004-8-7',14insert C select 457,41,'2004-5-14',45
insert C select 659,52,'2005-6-24',47
insert C select 247,265,'2005-4-5',45
select R.商品Id,
case when R.进货数量<((select 销售数量 from (select sum(销售数量) as 销售数量, 商品Id from C group by 商品Id) C where 商品Id=R.商品Id)-isnull((select T.进货数量 from R T where T.商品Id=R.商品Id and T.进货日期<R.进货日期),0)) then R.进货数量
else ((select 销售数量 from (select sum(销售数量) as 销售数量, 商品Id from C group by 商品Id) C where 商品Id=R.商品Id)-isnull((select T.进货数量 from R T where T.商品Id=R.商品Id and T.进货日期<R.进货日期),0))
end
from
(select * from R where 商品Id in( select 商品Id from C)) R
case when R.进货数量<((select 销售数量 from (select sum(销售数量) as 销售数量, 商品Id from C group by 商品Id) C where 商品Id=R.商品Id)-isnull((select T.进货数量 from R T where T.商品Id=R.商品Id and T.进货日期<R.进货日期),0)) then R.进货数量
else ((select 销售数量 from (select sum(销售数量) as 销售数量, 商品Id from C group by 商品Id) C where 商品Id=R.商品Id)-isnull((select T.进货数量 from R T where T.商品Id=R.商品Id and T.进货日期<R.进货日期),0))
end as 销售数量,
R.商品Id
from
(select * from R where 商品Id in( select 商品Id from C)) R