34,575
社区成员
发帖
与我相关
我的任务
分享
declare @YCD table (id int identity(1,1),cno varchar(6),ddate datetime,dept varchar(2),cp varchar(1),qty int)
insert into @YCD
select 'YCD001','2012-01-01','01','A',200 union all
select 'YCD001','2012-01-02','01','A',800 union all
select 'YCD001','2012-01-05','01','B',1000 union all
select 'YCD002','2012-01-03','01','A',2000 union all
select 'YCD003','2012-01-04','02','A',3000
declare @FHD table (id int identity(1,1),dept varchar(2),cp varchar(1),qty int)
insert into @FHD
select '01','A',3500 union all
select '02','B',500
select a.*,0 as px,
(case when b.qty-(select isnull(sum(qty),0) from @YCD where dept=a.dept and cp=a.cp and id<=a.id)>=0
then a.qty
else case when b.qty-(select isnull(sum(qty),0) from @YCD where dept=a.dept and cp=a.cp and id<a.id)>=0
then b.qty-(select isnull(sum(qty),0) from @YCD where dept=a.dept and cp=a.cp and id<a.id) else 0 end end) sqty
from @YCD a left join @FHD b on a.cp = b.cp and a.dept = b.dept
union all
select null,null,null,a.dept,a.cp,a.qty,1,a.qty-isnull(b.qty,0)
from @FHD a left join (select dept,cp,sum(qty) qty from @YCD group by dept,cp) b
on a.cp = b.cp and a.dept = b.dept
order by dept,px
/************************
id cno ddate dept cp qty px sqty
----------- ------ ----------------------- ---- ---- ----------- ----------- -----------
1 YCD001 2012-01-01 00:00:00.000 01 A 200 0 200
2 YCD001 2012-01-02 00:00:00.000 01 A 800 0 800
3 YCD001 2012-01-05 00:00:00.000 01 B 1000 0 0
4 YCD002 2012-01-03 00:00:00.000 01 A 2000 0 2000
NULL NULL NULL 01 A 3500 1 500
5 YCD003 2012-01-04 00:00:00.000 02 A 3000 0 0
NULL NULL NULL 02 B 500 1 500
(7 行受影响)
declare @YCD table (cno varchar(6),ddate datetime,dept varchar(2),cp varchar(1),qty int)
insert into @YCD
select 'YCD001','2012-01-01','01','A',200 union all
select 'YCD001','2012-01-02','01','A',800 union all
select 'YCD001','2012-01-05','01','B',1000 union all
select 'YCD002','2012-01-03','01','A',2000 union all
select 'YCD003','2012-01-04','02','A',3000
declare @FHD table (dept varchar(2),cp varchar(1),qty int)
insert into @FHD
select '01','A',3500 union all
select '02','B',500
-----要得到如下结果:
cno ddate dept cp qty fhqty
------ ----------------------- ---- ---- -----------
YCD002 2012-01-03 00:00:00.000 01 A 200 200
YCD003 2012-01-04 00:00:00.000 02 A 800 800
YCD001 2012-01-05 00:00:00.000 01 B 1000 0
YCD002 2012-01-03 00:00:00.000 01 A 2000 2000
null null 01 A 0 500
-多余的(3500-200-800-2000=500)500单独显示
YCD003 2012-01-04 00:00:00.000 02 A 3000 0
null null 02 B 0 500
declare @YCD table (id int identity(1,1),cno varchar(6),ddate datetime,dept varchar(2),cp varchar(1),qty int)
insert into @YCD
select 'YCD001','2012-01-01','01','A',200 union all
select 'YCD001','2012-01-02','01','A',800 union all
select 'YCD001','2012-01-05','01','B',1000 union all
select 'YCD002','2012-01-03','01','A',2000 union all
select 'YCD003','2012-01-04','02','A',3000
declare @FHD table (id int identity(1,1),dept varchar(2),cp varchar(1),qty int)
insert into @FHD
select '01','A',3500 union all
select '02','B',500
select a.*,(case when b.qty-(select isnull(sum(qty),0) from @YCD where dept=a.dept and cp=a.cp and id<=a.id)>=0
then a.qty
else case when b.qty-(select isnull(sum(qty),0) from @YCD where dept=a.dept and cp=a.cp and id<a.id)>=0
then b.qty-(select isnull(sum(qty),0) from @YCD where dept=a.dept and cp=a.cp and id<a.id) else 0 end end) sqty
from @YCD a left join @FHD b on a.cp = b.cp and a.dept = b.dept
/************************
id cno ddate dept cp qty sqty
----------- ------ ----------------------- ---- ---- ----------- -----------
1 YCD001 2012-01-01 00:00:00.000 01 A 200 200
2 YCD001 2012-01-02 00:00:00.000 01 A 800 800
3 YCD001 2012-01-05 00:00:00.000 01 B 1000 0
4 YCD002 2012-01-03 00:00:00.000 01 A 2000 2000
5 YCD003 2012-01-04 00:00:00.000 02 A 3000 0
(5 行受影响)
declare @YCD table (cno varchar(6),ddate datetime,dept varchar(2),cp varchar(1),qty int)
insert into @YCD
select 'YCD001','2012-01-01','01','A',200 union all
select 'YCD001','2012-01-02','01','A',800 union all
select 'YCD001','2012-01-05','01','B',1000 union all
select 'YCD002','2012-01-03','01','A',2000 union all
select 'YCD003','2012-01-04','02','A',3000
declare @FHD table (dept varchar(2),cp varchar(1),qty int)
insert into @FHD
select '01','A',3500 union all
select '02','B',500
select * from @YCD UNION all
select NULL,NULL,* from @FHD ORDER BY cp
/*
cno ddate dept cp qty
------ ----------------------- ---- ---- -----------
YCD002 2012-01-03 00:00:00.000 01 A 2000
YCD003 2012-01-04 00:00:00.000 02 A 3000
NULL NULL 01 A 3500
YCD001 2012-01-01 00:00:00.000 01 A 200
YCD001 2012-01-02 00:00:00.000 01 A 800
YCD001 2012-01-05 00:00:00.000 01 B 1000
NULL NULL 02 B 500
*/
--貌似group +sum 然后联查做差即可。