27,579
社区成员
发帖
与我相关
我的任务
分享
create table a (name nvarchar(120),phone varchar(120))
go
create table b (name nvarchar(120),phone varchar(120))
go
insert a
select '张三','5566742' union
select '李四','13088889999'union
select '赵五','13877776666'union
select '鬼子六','3322152'
insert b
select '石头','5566742'union
select '麻子','13088889999'union
select '赵五','13877776666'union
select '泡泡','13900002222'
go
update a set name=name+isnull((select ','+name from B where phone=a.phone for xml path('')),'')
from A a
select * from a
/*
鬼子六 3322152
李四,麻子 13088889999
张三,石头 5566742
赵五,赵五 13877776666
*/
insert into A
select * from B where not exists(select 1 from A where phone=b.phone)
select * from a
/*
鬼子六 3322152
李四,麻子 13088889999
张三,石头 5566742
赵五,赵五 13877776666
泡泡 13900002222
*/
delete from a where exists (select 1 from b where phone=a.phone and name=a.name )
select * from a
/*
鬼子六 3322152
李四,麻子 13088889999
张三,石头 5566742
赵五,赵五 13877776666
*/
update a set name=case when charindex(',',name)>=1 then LEFT(name,charindex(',',name)-1) else name end
select * from a
/*
鬼子六 3322152
李四 13088889999
张三 5566742
赵五 13877776666
*/
drop table a
drop table b
delete from A表 a where exists (select 1 from B表 where 电话=a.电话 and 姓名=a.姓名 )
update A表 set 姓名=case when charindex(',',姓名)>=1 then LEFT(姓名,charindex(',',姓名)-1) else 姓名 end