大家好：

我想通过SQL做出相同的金额两两自动匹配的语句来，
比如有4个10，那么备注里边就自动更新为4个1，表示匹配成功
比如有3个10，备注里边就自动更新为2个1 另外一个做为NULL值显示，表示匹配了2个 还有一个没有匹配到
但是想不出个头绪来，还请大家帮忙了(SQL2000或者SQL2005都可以)

要显示的结果就如下所示（已经排序好）:

金额  备注
10    1
10    1
10    NULL
20    1
20    1
30    1
30    1
30    1
30    1
40    NULL

--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
create table [test]([金额] int,[备注] int)
insert [test]
select 10,null union all
select 10,null union all
select 10,null union all
select 20,null union all
select 20,null union all
select 30,null union all
select 30,null union all
select 30,null union all
select 30,null union all
select 40,null


go
alter table test add px int identity
go
alter table test add row int
go
update test
set row=t.tt from(
select *,tt=(select COUNT(1) from test b 
where a.px>=b.px and a.金额=b.金额) from test a)t
where test.px=t.px
go
alter table test add [count] int
go
update test
set [count]=t.[count] from(
select 金额,COUNT(1)over(partition by 金额) [count] from test
)t where t.金额=test.金额


update test
set [备注]=0 where  (row%2=1 and row=[count])

update test
set [备注]=1 where row<[count] or (row=[count] and row%2=0)
alter table test drop column px,row,[count]

select * from test


/*
金额    备注
10    1
10    1
10    0
20    1
20    1
30    1
30    1
30    1
30    1
40    0
*/

------解决方案--------------------

SQL code

--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
create table [test]([金额] int,[备注] int)
insert [test]
select 10,null union all
select 10,null union all
select 10,null union all
select 20,null union all
select 20,null union all
select 30,null union all
select 30,null union all
select 30,null union all
select 30,null union all
select 40,null


go
alter table test add px int identity
go
alter table test add row int
go
update test
set row=t.tt from(
select *,tt=(select COUNT(1) from test b 
where a.px>=b.px and a.金额=b.金额) from test a)t
where test.px=t.px
go
alter table test add [count] int
go
update test
set [count]=t.[count] from(
select 金额,COUNT(1)over(partition by 金额) [count] from test
)t where t.金额=test.金额


update test
set [备注]=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    1
10    1
10    0
20    1
20    1
30    1
30    1
30    1
30    1
40    0
*/

这么做就行了

------解决方案--------------------

SQL code

--> 测试数据：[test]
if object_id('[test]') is not null 
drop table [test]
create table [test]([金额] int,[备注] int)
insert [test]
select 10,null union all
select 10,null union all
select 10,null union all
select 20,null union all
select 20,null union all
select 30,null union all
select 30,null union all
select 30,null union all
select 30,null union all
select 40,null


go
--增加一个排序列
alter table test add px int identity
go
--增加一个按照金额分组的排序列
alter table test add row int
go
--更新row的数据
update test
set row=t.tt from(
select *,tt=(select COUNT(1) from test b 
where a.px>=b.px and a.金额=b.金额) from test a)t
where test.px=t.px
go
--增加一个字来记录每个金额出现的次数
alter table test add [count] int
go
--更新这个次数
--2005以上语法
update test
set [count]=t.[count] from(
select 金额,COUNT(1)over(partition by 金额) [count] from test
)t where t.金额=test.金额
--2000语法
update test
set 金额=t.金额 from(
select 金额,COUNT(1) as [count] from test group by 金额)t
where t.金额=test.金额

--通过row 与[count]的关系来更新备注
update test
set [备注]=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    1
10    1
10    0
20    1
20    1
30    1
30    1
30    1
30    1
40    0
*/