关于两个相同金额自动匹配的问题
大家好：我想通过SQL做出相同的金额两两自动匹配的语句来，比如有4个10，那么备注里边就自动更新为4个1，表示匹配成功比如有3个10，备注里边就自动更新为2个1 另外一个做为NULL值显示，表示匹配了2个 还有一个没有匹配到但是想不出个头绪来，还请大家帮忙了(SQL2000或者SQL2005都可以)要显示的结果就如下所示（已经排序好）:金额 备注10 110 110 NULL20 120 130 130 130 130 140 NULL
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([金额] int,[备注] int)insert [test]select 10,null union allselect 10,null union allselect 10,null union allselect 20,null union allselect 20,null union allselect 30,null union allselect 30,null union allselect 30,null union allselect 30,null union allselect 40,nullgoalter table test add px int identitygoalter table test add row intgoupdate testset row=t.tt from(select *,tt=(select COUNT(1) from test b where a.px>=b.px and a.金额=b.金额) from test a)twhere test.px=t.pxgoalter table test add [count] intgoupdate testset [count]=t.[count] from(select 金额,COUNT(1)over(partition by 金额) [count] from test)t where t.金额=test.金额update testset [备注]=0 where (row%2=1 and row=[count])update testset [备注]=1 where row<[count] or (row=[count] and row%2=0)alter table test drop column px,row,[count]select * from test/*金额 备注10 110 110 020 120 130 130 130 130 140 0*/
[解决办法]
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([金额] int,[备注] int)insert [test]select 10,null union allselect 10,null union allselect 10,null union allselect 20,null union allselect 20,null union allselect 30,null union allselect 30,null union allselect 30,null union allselect 30,null union allselect 40,nullgoalter table test add px int identitygoalter table test add row intgoupdate testset row=t.tt from(select *,tt=(select COUNT(1) from test b where a.px>=b.px and a.金额=b.金额) from test a)twhere test.px=t.pxgoalter table test add [count] intgoupdate testset [count]=t.[count] from(select 金额,COUNT(1)over(partition by 金额) [count] from test)t where t.金额=test.金额update testset [备注]=case when (row%2=1 and row=[count]) then 0 else 1 endalter table test drop column px,row,[count]select * from test/*金额 备注10 110 110 020 120 130 130 130 130 140 0*/这么做就行了
[解决办法]
--> 测试数据：[test]if object_id('[test]') is not null drop table [test]create table [test]([金额] int,[备注] int)insert [test]select 10,null union allselect 10,null union allselect 10,null union allselect 20,null union allselect 20,null union allselect 30,null union allselect 30,null union allselect 30,null union allselect 30,null union allselect 40,nullgo--增加一个排序列alter table test add px int identitygo--增加一个按照金额分组的排序列alter table test add row intgo--更新row的数据update testset row=t.tt from(select *,tt=(select COUNT(1) from test b where a.px>=b.px and a.金额=b.金额) from test a)twhere test.px=t.pxgo--增加一个字来记录每个金额出现的次数alter table test add [count] intgo--更新这个次数--2005以上语法update testset [count]=t.[count] from(select 金额,COUNT(1)over(partition by 金额) [count] from test)t where t.金额=test.金额--2000语法update testset 金额=t.金额 from(select 金额,COUNT(1) as [count] from test group by 金额)twhere t.金额=test.金额--通过row 与[count]的关系来更新备注update testset [备注]=case when (row%2=1 and row=[count]) then 0 else 1 end--删除所有的新增字段alter table test drop column px,row,[count]--验证数据select * from test/*金额 备注10 110 110 020 120 130 130 130 130 140 0*/