用表中的UserID为1记录集更新其他USerID的记录,
假设userid=1的为标准集,那userid=2的就应该增加一条记录Col_Name的值为C且seq=3,
同时要更新Col_Name为A和B记录的Seq的值分别为1和2
有一表t1:
UserID Col_Name Seq
1 A 1
1 B 2
1 C 3
2 A
2 B
3 A
更新后要成为:
UserID Col_Name Seq
1 A 1
1 B 2
1 C 3
2 A 1
2 B 2
2 C 3
3 A 1
3 B 2
3 C 3 请问要怎么才能实现呢?
假设userid=1的为标准集,那userid=2的就应该增加一条记录Col_Name的值为C且seq=3,
同时要更新Col_Name为A和B记录的Seq的值分别为1和2
有一表t1:
UserID Col_Name Seq
1 A 1
1 B 2
1 C 3
2 A
2 B
3 A
更新后要成为:
UserID Col_Name Seq
1 A 1
1 B 2
1 C 3
2 A 1
2 B 2
2 C 3
3 A 1
3 B 2
3 C 3 请问要怎么才能实现呢?
create table #T (UserID int,[Col_Name] varchar(2),Seq int)
insert into #T select 1,'A',1
union all select 1,'B',2
union all select 1,'C',3
union all select 2,'A',0
union all select 2,'B',0
union all select 3,'A',0
goupdate #T
set #T.seq=b.seq
from #T as b inner join #T on #T.[col_name]=b.[col_name]
select * from #T
/*
UserID Col_Name Seq
----------- -------- -----------
1 A 1
1 B 2
1 C 3
2 A 1
2 B 2
3 A 1(6 行受影响)
*/
create table #T (UserID int,[Col_Name] varchar(2),Seq int)
insert into #T select 1,'A',1
union all select 1,'B',2
union all select 1,'C',3
union all select 2,'A',0
union all select 2,'B',0
union all select 3,'A',0
goupdate #T
set #T.seq=b.seq
from #T as b inner join #T on #T.[col_name]=b.[col_name]
select * from #T
/*
UserID Col_Name Seq
----------- -------- -----------
1 A 1
1 B 2
1 C 3
2 A 1
2 B 2
3 A 1(6 行受影响)
*/
2 C 3
3 B 2
3 C 3
所以应该先向表中插入这几行,然后再更新
--向表中插入新的数据
insert into tb(userid,col_name)
select a.userid,b.col_name
from (select distinct userid from tb) a cross join
(select distinct col_name from tb) b left join tb
on a.userid=tb.userid and b.col_name=tb.col_name
where tb.userid is null--更新
update tb
set seq= (case col_name when 'A' then 1
when 'B' then 2
when 'C' then 3
end)
select * from tb order by userid,col_name
结果:
1 A 1
1 B 2
1 C 3
2 A 1
2 B 2
2 C 3
3 A 1
3 B 2
3 C 3