求一SQL,如何去匹配数据，高手请进！来者有份

Question

有两张表：
预测单表：YCD
cno       ddate           dept  cp qty
YCD001   2012-01-01       01     A 200
YCD001   2012-01-02       01     A 800
YCD001   2012-01-05       01     B 1000
YCD002   2012-01-03       01     A 2000
YCD003   2012-01-04       02     A 3000
发货单表：FHD
dept   cp    qty
 01     A    3500
 02     B    500要得到如下结果：
cno       ddate           dept  cp qty   fhqty
YCD001   2012-01-01       01     A 200    200
YCD001   2012-01-02       01     A 800    800
YCD001   2012-01-05       01     B 1000   0       ----- 按部门来的，因为01部门没有发B产品
YCD002   2012-01-03       01     A 2000   2000
null      null            01     A 0      500       -----多余的（3500-200-800-200=500）500单独显示
YCD003   2012-01-04       02     A 3000   0         
null      null            02     B 0      500       
说明：YCD表的数据与FHD表的数据根据产品去进行匹配：要先满足第一张预测单数据，分配完后余下数分配给第二张，张三张、、、、
是用游标，还是有更好办法！

Answer 1

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',3000declare @FHD table (dept varchar(2),cp varchar(1),qty int)
insert into @FHD
select '01','A',3500 union all
select '02','B',500select * 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  然后联查做差即可。

Answer 2

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',3000declare @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',500select 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 行受影响)

Answer 3

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',3000declare @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  

Answer 4

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',3000declare @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',500select 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 行受影响)

Answer 5

AcHerat
为什么要加一个ID上去，ID主要起什么作用

Answer 6

因为是类似先进先出的，所以加个ID作为先进先出的排序，如果表没有，可以利用临时表或with cte来做一个标识列。