寻求好的解决办法
大家说说有什么好的办法来实现两条指定记录指定字段的值的相互交换
如:
      1           10
      2           11
即将10与11相互交换.
前段时间在java版看来只利用运算的优先级就能实现互换,而且只是一条语句,没有用到第三变量.
数据库里有什么好的办法嘛?

------解决方案--------------------
create table tb
(
id int,
col1 int
)
go
insert into tb select 1,10 union select 2,11
go

update tb
set tb.col1=b.col1
from tb,tb b where tb.id <> b.id
select * from tb
id col1
----------- -----------
1 11
2 10

(2 行受影响)
------解决方案--------------------
create table tb
(
id int,
col1 int
)
go
insert into tb select 1,10 union select 2,11
insert into tb select 3,20 union select 4,21
go

select * from tb


--drop table tb

select a.id,b.col1
from tb a inner join tb b
on a.id=b.id-1
where a.id%2=1
union
select a.id,b.col1
from tb a inner join tb b
on a.id=b.id+1
where a.id%2=0
------解决方案--------------------
declare @t table(id int,value int)
insert @t
select 1,10 union all
select 2,11 union all
select 3,12 union all
select 4,13 union all
select 5,14 union all
select 6,15

SELECT t1.id,t1.value as orivalue,t2.value as exchange from
(select GroupID = ((select count(*) from @t where id <= a.id) + 1)/2,*
from @t as a) as t1
,
(select GroupID = ((select count(*) from @t where id <= a.id) + 1)/2,*
from @t as a) as t2
WHERE t1.GroupID = t2.GroupID AND (t1.id < t2.id or t1.id > t2.id)
ORDER BY t1.id

/*结果
id orivalue exchange(交换后的值)
----------- ----------- -----------
1 10 11
2 11 10
3 12 13
4 13 12
5 14 15
6 15 14
*/

------解决方案--------------------
如果id是不连续的话就得
select *,identity(int,1,1) as aa into #aa from table

要Update 得话
update tb
set col1=c.col1
from tb inner join (
select a.id,b.col1
from tb a inner join tb b
on a.id=b.id-1
where a.id%2=1
union
select a.id,b.col1
from tb a inner join tb b
on a.id=b.id+1
where a.id%2=0)c
on tb.id=c.id

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

create table #(a int,b int)
insert into # select 1, 20
union all select 3, 10
union all select 4, 13
union all select 6, 18


update aa set b=bb.b
from
# aa
join
# bb
on bb.a <> aa.a
where aa.a in(1,4) and bb.a in(1,4)

select * from #


a b
----------- -----------
1 13
3 10
4 20
6 18