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

2012-02-20 10:15:17 335 阅读 AI 写同款 · GEO 优化AI 选题数据监测一键分发 ›

flyfly2008 墨衍会员 · AI 创作全网分发关注 + 加入社区

Mysql SQL优化（一）高效率的有条件的去重和联合查询的优化 =================从5.5秒查询到0.19秒，提升26倍速度=======================新接手的一个项目，很多地方速度超慢需要改这里有一个问题就是，从一张记录表中选择会员号不同，且同一会员号中id最大的全部数据然后和其他2张表联合查询出来标准版的数据库去重问题原来的SQL是这样的SELECT * FROM member M LEFT JOIN ... 阅读详情

有两张表：
预测单表：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表的数据根据产品去进行匹配：要先满足第一张预测单数据，分配完后余下数分配给第二张，张三张、、、、

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

...全文

JSSIP实战：5分钟搞定WebRTC电话功能（附完整代码示例）本文通过JSSIP库实战演示如何在Web应用中快速集成WebRTC电话功能。文章提供了从环境准备、SIP服务器连接到完整代码示例的详细步骤，涵盖用户注册、拨号接听、媒体流处理等核心环节，并针对常见问题给出排查方案，帮助开发者高效实现网页端实时语音通信。阅读详情

flyfly2008

MS-SQL Server 34,876 成员 254,635 内容

+ 关注 + 加入社区

切换为时间正序

一十七 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

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

相关推荐