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

flyfly2008 2012-02-20 10:15:17
有两张表:
预测单表: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表的数据根据产品去进行匹配:要先满足第一张预测单数据,分配完后余下数分配给第二张,张三张、、、、


是用游标,还是有更好办法!
...全文
184 15 打赏 收藏 转发到动态 举报
写回复
用AI写文章
15 条回复
切换为时间正序
请发表友善的回复…
发表回复
一十七 2012-02-20
  • 打赏
  • 举报
回复
等高手帮LZ解答。。
AcHerat 元老 2012-02-20
  • 打赏
  • 举报
回复

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 行受影响)
flyfly2008 2012-02-20
  • 打赏
  • 举报
回复

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



梦里独赏花 2012-02-20
  • 打赏
  • 举报
回复
都是数据库高手啊
AcHerat 元老 2012-02-20
  • 打赏
  • 举报
回复

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 行受影响)
flyfly2008 2012-02-20
  • 打赏
  • 举报
回复
3500-200-800-2000=500?
flyfly2008 2012-02-20
  • 打赏
  • 举报
回复
楼上高手,不是这样的
小孩快跑 2012-02-20
  • 打赏
  • 举报
回复
丫的,不懂!
叶子 2012-02-20
  • 打赏
  • 举报
回复

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 然后联查做差即可。
叶子 2012-02-20
  • 打赏
  • 举报
回复
3500-200-800-200=500? 这个也不相等呀?
jmx123456789 2012-02-20
  • 打赏
  • 举报
回复
3500-200-800-200=500 ??? 啥情况
老猫五号 2012-02-20
  • 打赏
  • 举报
回复
这个多余的(3500-200-800-200=500)500单独显示
???等于500吗?
AcHerat 元老 2012-02-20
  • 打赏
  • 举报
回复
因为是类似先进先出的,所以加个ID作为先进先出的排序,如果表没有,可以利用临时表或with cte来做一个标识列。
flyfly2008 2012-02-20
  • 打赏
  • 举报
回复

AcHerat
为什么要加一个ID上去,ID主要起什么作用
zhangweitc123 2012-02-20
  • 打赏
  • 举报
回复
[Quote=引用 11 楼 t3121522520 的回复:]
等高手帮LZ解答。。
[/Quote]+1

34,575

社区成员

发帖
与我相关
我的任务
社区描述
MS-SQL Server相关内容讨论专区
社区管理员
  • 基础类社区
  • 二月十六
  • 卖水果的net
加入社区
  • 近7日
  • 近30日
  • 至今
社区公告
暂无公告

试试用AI创作助手写篇文章吧