请教一个重复项的sql
就是把重复的内容 都重设成第一次出现的那个id
假如我有一个表
id name newid
1 ab 1
2 cd 2
3 ab 1
4 cd 2
5 ab 1
newid 是空的
功能就是把 newid 设成表里的那样的值
谢谢先
[解决办法]
CREATE TABLE #tmp (id INT, Cname VARCHAR(20), Cnewid INT)
INSERT #tmp ( id, Cname, Cnewid )
SELECT 1, 'ab', NULL UNION ALL
SELECT 2, 'cd', NULL UNION ALL
SELECT 3, 'ab', NULL UNION ALL
SELECT 4, 'cd', NULL UNION ALL
SELECT 5, 'ab', NULL
;WITH cte AS
(
SELECT Cnewid,ROW_NUMBER() OVER(ORDER BY id) AS RN
FROM #tmp
)
UPDATE cte SET Cnewid=CASE RN%2 WHEN 1 THEN 1 ELSE 2 end
SELECT * FROM #tmp
if object_id('[tableA]') is not null drop table [tableA]
go
create table tableA(ID int,NAME char(20),newid int)
insert into tableA
select 1,'ab',NULl union all
select 2,'cd',NULl union all
select 3,'ab',NULl union all
select 4,'cd',NULl union all
select 5,'ab',NULl
update tableA set newid=nn from tableA A left join
(select NAME,row_number() over(order by NAME) as nn from tableA group by NAME) B
on A.NAME=B.NAME
---------------
ID NAME newid
----------- -------------------- -----------
1 ab 1
2 cd 2
3 ab 1
4 cd 2
5 ab 1
(5 行受影响)
update [table] set [newid]=t1.id
from (select [name],MIN(id) as id from [table] group by [name]) t1
where [table].[name]=t1.[name]
if object_id('test') is not null drop table test
go
create table test(id int identity,name nvarchar(10),newid int)
insert test(name)
select 'ab'
union all
select 'cd'
union all
select 'ab'
union all
select 'cd'
union all
select 'ab'
update a set a.newid=b.id from test a join (select name,min(id) id from test group by name) b on a.name=b.name
CREATE TABLE #tmp (id INT, Cname VARCHAR(20), Cnewid INT) INSERT INTO #tmp
( id, Cname, Cnewid ) VALUES ( 1, 'ab', NULL), ( 2, 'cd', NULL),
( 3, 'ab', NULL), ( 4, 'cd', NULL), ( 5, 'ab', NULL)
UPDATE t1 SET t1.Cnewid=t2.RN FROM #tmp t1 INNER JOIN
(SELECT Cname,ROW_NUMBER() OVER(ORDER BY Cname) AS RN FROM #tmp GROUP BY Cname)t2
on t1.Cname = t2.Cname
select * from #tmp
drop TABLE #tmp