求一SQL,怎么去匹配数据，来者有份

求一SQL,如何去匹配数据，高手请进！来者有份
有两张表：
预测单表：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表的数据根据产品去进行匹配：要先满足第一张预测单数据，分配完后余下数分配给第二张，张三张、、、、


是用游标，还是有更好办法！

[解决办法]
这个多余的（3500-200-800-200=500）500单独显示
？？？等于500吗？
[解决办法]
3500-200-800-200=500 ??? 啥情况
[解决办法]
3500-200-800-200=500? 这个也不相等呀？
[解决办法]

SQL code

declare @YCD table (cno varchar(6),ddate datetime,dept varchar(2),cp varchar(1),qty int)insert into @YCDselect 'YCD001','2012-01-01','01','A',200 union allselect 'YCD001','2012-01-02','01','A',800 union allselect 'YCD001','2012-01-05','01','B',1000 union allselect 'YCD002','2012-01-03','01','A',2000 union allselect 'YCD003','2012-01-04','02','A',3000declare @FHD table (dept varchar(2),cp varchar(1),qty int)insert into @FHDselect '01','A',3500 union allselect '02','B',500select * from @YCD UNION allselect NULL,NULL,* from @FHD ORDER BY cp/*cno    ddate                   dept cp   qty------ ----------------------- ---- ---- -----------YCD002 2012-01-03 00:00:00.000 01   A    2000YCD003 2012-01-04 00:00:00.000 02   A    3000NULL   NULL                    01   A    3500YCD001 2012-01-01 00:00:00.000 01   A    200YCD001 2012-01-02 00:00:00.000 01   A    800YCD001 2012-01-05 00:00:00.000 01   B    1000NULL   NULL                    02   B    500*/--貌似group +sum  然后联查做差即可。
[解决办法]
SQL code
declare @YCD table (id int identity(1,1),cno varchar(6),ddate datetime,dept varchar(2),cp varchar(1),qty int)insert into @YCDselect 'YCD001','2012-01-01','01','A',200 union allselect 'YCD001','2012-01-02','01','A',800 union allselect 'YCD001','2012-01-05','01','B',1000 union allselect 'YCD002','2012-01-03','01','A',2000 union allselect 'YCD003','2012-01-04','02','A',3000declare @FHD table (id int identity(1,1),dept varchar(2),cp varchar(1),qty int)insert into @FHDselect '01','A',3500 union allselect '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) sqtyfrom @YCD a left join @FHD b on a.cp = b.cp and a.dept = b.deptunion allselect 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.deptorder by dept,px/************************id          cno    ddate                   dept cp   qty         px          sqty----------- ------ ----------------------- ---- ---- ----------- ----------- -----------1           YCD001 2012-01-01 00:00:00.000 01   A    200         0           2002           YCD001 2012-01-02 00:00:00.000 01   A    800         0           8003           YCD001 2012-01-05 00:00:00.000 01   B    1000        0           04           YCD002 2012-01-03 00:00:00.000 01   A    2000        0           2000NULL        NULL   NULL                    01   A    3500        1           5005           YCD003 2012-01-04 00:00:00.000 02   A    3000        0           0NULL        NULL   NULL                    02   B    500         1           500(7 行受影响) 
 
[解决办法]
等高手帮LZ解答。。
[解决办法]
探讨

等高手帮LZ解答。。

[解决办法]
因为是类似先进先出的，所以加个ID作为先进先出的排序，如果表没有，可以利用临时表或with cte来做一个标识列。
[解决办法]
丫的，不懂！