求一条换姓名的sql

将计就计123 2013-02-05 12:41:21

表有个字段 sname 是姓名字段。我现在想把姓氏互相换一下
比如

李维一
张嘉凯
王小刚
采薇

换成

张维一
李嘉凯
采小刚
王薇

...全文

454 15 打赏收藏转发到动态举报

写回复

用AI写文章

15 条回复

切换为时间正序

请发表友善的回复…

发表回复

一十七 2013-02-18

打赏
举报

引用 13 楼 bbbbbben 的回复:

SQL code?1234567891011121314151617181920212223242526272829303132333435USE testGO -->生成表tb if object_id('tb') is not null drop table tbGoCreate table tb([col1] nvarchar(3))Insert into……

+++1

伤痕累累 2013-02-05

打赏
举报

with tb(id,txt) as( select 1,'李维一' union all select 2,'张嘉凯' union all select 3,'王小刚' union all select 4,'采薇' ) select id=a.id,txt=left(b.txt,1)+right(a.txt,len(a.txt)-1) from tb a join tb b on a.id+1=b.id and a.id%2=1 union all select id=b.id,txt=left(a.txt,1)+right(b.txt,len(b.txt)-1) from tb a join tb b on a.id+1=b.id and a.id%2=1 order by id

-Tracy-McGrady- 2013-02-05

打赏
举报


with cte as(select row_number() over(order by getdate()) as rowid,sname from 表)
update a set a.sname=case when a.rowid % 2=0 then (select left(sname,1) from cte where rowid=a.rowid-1)
                     else (select left(sname,1) from t where rowid=a.rowid+1) end+substring(a.sname,2,10)
from cte a

我腫了 2013-02-05

打赏
举报

USE test
GO


-->生成表tb

if object_id('tb') is not null 
	drop table tb
Go
Create table tb([col1] nvarchar(3))
Insert into tb
Select N'李维一'
Union all Select N'张嘉凯'
Union all Select N'王小刚'
Union all Select N'采薇'


;WITH t AS (
	SELECT *,ROW_NUMBER()OVER(ORDER BY GETDATE()) AS row FROM tb
)
SELECT
		LEFT(ISNULL(b.col1,a.col1),1)+RIGHT(a.col1,LEN(a.col1)-1) AS col1
	FROM t AS a
		LEFT JOIN t AS b ON a.row=CASE WHEN a.row%2=1 THEN b.row-1 ELSE b.row+1 END
GO


/*
col1
----
张维一
李嘉凯
采小刚
王薇
*/

红木偶 2013-02-05

打赏
举报


Create table #s (sname varchar(20))
insert into  #s select '李维一'
union all      select '张嘉凯'
union all      select '王小刚'
union all      select '采薇'
Create table #t (sname varchar(20))
insert into  #t select '李维一'
union all      select '张嘉凯'
union all      select '王小刚'
union all      select '采薇'


with t1 as (select sname,px=row_number()over(order by getdate()) from #s ),
     t2 as (select sname,px=row_number()over(order by getdate()) from #t )

select *  into #a from (
select name1,name2 
 from 
   (select sname as name1,px from t1 where px%2=1)a 
  join 
   (select sname as name2,px from t2 where px%2=0)b on a.px=b.px-1
union all
select name1,name2
from
   (select sname as name1,px from t1 where px%2=0)a 
  join 
   (select sname as name2,px from t2 where px%2=1)b on a.px=b.px+1
)ab
--select * from #a
update #a set name1=left(ltrim(name1),1)+substring(ltrim(name2),2,len(ltrim(name2)))
select name1 from #a

避免逻辑复杂，我使用了临时表，把数据在临时表做好。再修改到实际表内

唐诗三百首 2013-02-05

打赏
举报


create table dbo.ded(sname varchar(20))

insert into dbo.ded(sname)
select '李维一' union all
select '张嘉凯' union all
select '王小刚' union all
select '采薇'


with t as
( select row_number() over(order by getdate()) 'rn',
         sname 
  from dbo.ded
)
update a 
 set a.sname=case when a.rn%2=0 then (select left(sname,1) from t where rn=a.rn-1)
                  else (select left(sname,1) from t where rn=a.rn+1) end
             +substring(a.sname,2,10)
 from t a                 
             

select * from dbo.ded
/*
sname
--------------------
张维一
李嘉凯
采小刚
王薇

(4 row(s) affected)
*/

将计就计123 2013-02-05