前面一贴:http://topic.csdn.net/u/20120516/21/aa23e8d3-9255-48ec-ae18-3dcaa8931e4e.html
已经解决了目前的问题,在实际应用过程中又出现了一个新问题
就是前面再加上“类别”一列,类别a与b 如果两个类别金额相同自动匹配为1 不匹配为0
如果有多个a与b a与b的金额也要一一对应才能自动顺延匹配实际要显示的效果如下:类别 金额 备注
a 10 1
b 10 1
a 20 0
a 20 0
a 20 0
a 30 0
a 40 1
b 40 1
a 40 0
a 50 1
b 50 1
a 50 1
b 50 1
--> 测试数据:[test]
if object_id('[test]') is not null
drop table [test]
create table [test](
[类别] varchar(1),
[金额] int,
[备注] int
)
insert [test]
select 'a',10,null union all
select 'b',10,null union all
select 'a',20,null union all
select 'a',20,null union all
select 'a',20,null union all
select 'a',30,null union all
select 'a',40,null union all
select 'b',40,null union all
select 'a',40,null union all
select 'a',50,null union all
select 'b',50,null union all
select 'a',50,null union all
select 'b',50,nullalter table test add id int identity
go
update test
set [备注]=case when id in (select * from(
select a.id
from test a full join test b
on a.id=b.id+1
where a.类别<>b.类别 and a.金额=b.金额
union
select b.id
from test a full join test b
on a.id=b.id+1
where a.类别<>b.类别 and a.金额=b.金额)m) then 1 else 0 endalter table test drop column id
go
select * from test
/*
类别 金额 备注
----- -------- -------------
a 10 1
b 10 1
a 20 0
a 20 0
a 20 0
a 30 0
a 40 1
b 40 1
a 40 1--这里我不明白你的为什么是0??
a 50 1
b 50 1
a 50 1
b 50 1
*/解释一下嘛
a30只有一个 也是找不到对应的b30 所以也为0
goupdate tb set 备注=1
where 类别='a' and exists(select 1 from tb t where t.类别='b' and t.px=tb.px+1)
or
类别='b' and exists(select 1 from tb t where t.类别='a' and t.px=tb.px-1)
goalter table tb drop column px
goselect * from tb
/**
类别 金额 备注
---- ----------- -----------
a 10 1
b 10 1
a 20 0
a 20 0
a 20 0
a 30 0
a 40 1
b 40 1
a 40 0
a 50 1
b 50 1
a 50 1
b 50 1(13 行受影响)
**/