原表如下
id ziduan1 ziduan2
... ...
1 a1 a2
2 b1 b2
... ...
数据交换结果
id ziduan1 ziduan2
... ...
1 a1 b2
2 b1 a2
... ...
已知id，只交换ziduan2的值，最好只用一条语句，求快速的办法
------解决方案--------------------
- SQL code
declare @T table (id int,ziduan1 varchar(2),ziduan2 varchar(2))insert into @Tselect 1,'a1','a2' union allselect 2,'b1','b2'update @Tset ziduan2=case when ziduan1='a1' then (select ziduan2 from @T where ziduan1='b1')when ziduan1='b1' then (select ziduan2 from @T where ziduan1='a1')endselect * from @T/*id ziduan1 ziduan2----------- ------- -------1 a1 b22 b1 a2*/
------解决方案--------------------
- SQL code
declare @T table (id int,ziduan1 varchar(2),ziduan2 varchar(2))insert into @Tselect 1,'a1','a2' union allselect 2,'b1','b2'union allselect 3,'b1','b2'select * from @Tupdate @T set ziduan2=--selectcase id when 1 then (select ziduan2 from @T where id =2)when 2 then (select ziduan2 from @T where id =1) else ziduan2 endfrom @T where id in(1,2)select * from @T
------解决方案--------------------
想不出更好的办法了
- SQL code
declare @T table ( id int,ziduan1 varchar(2),ziduan2 varchar(2))insert into @Tselect 1,'a1','a2' union allselect 2,'b1','b2'SELECT * FROM @TUpdate @T SET ziduan2= CASE ziduan2 WHEN 'a2' THEN 'b2 ' WHEN 'b2 ' THEN 'a2 ' ELSE ziduan2 END WHERE ziduan2 IN ( 'a2 ', 'b2 ') SELECT * FROM @T/*id ziduan1 ziduan2 ----------- ------- ------- 1 a1 a22 b1 b2（所影响的行数为 2 行）（所影响的行数为 2 行）id ziduan1 ziduan2 ----------- ------- ------- 1 a1 b22 b1 a2（所影响的行数为 2 行）*/