SQL去掉重复的代码

[x403096100]

表名:kehu,
字段:ID
字段: name
字段:tel
字段:address
字段:age

1. name很多重复的,去掉重复的,(保留初始的数据)

2.去重之后,再次查询name的所有信息

求这两个需求的SQL语句代码

[RINK_1]

select *
from (select *,ROW_NUMBER() over (PARTITION by name order by id) as seq from kehu) as A
where seq=1

[zheninchangjiang]

这种需求极不合理，因为名称重复，而原始数据倒底是哪条不清楚，稍有不慎就是正确资料的丢失

[x403096100]

(保留初始的数据).......这个需求可以去掉

[日月路明]

declare Cur cursor
for
select Name,Num=count(*)-1 from TableA group by Name having count(*)>1

declare @Name varchar(100),@Num int

open Cur
fetch next from Cur into @Name,@Num
while @@fetch_status=0
BEGIN
  set rowcount @Num
  delete TableA where Name=@Name
  fetch next from Cur into @Name,@Num  	
END

close Cur
deallocate cur

[砸死牛顿的苹果]

居然用游标~

[qq_41988350]

去重请采用 row_number方法哦 sqlserver row_number关键字的用法

[吉普赛的歌]

--1. 将原表备份，防止意外
SELECT * 
INTO kehu_bak_20180412
FROM kehu

--2. 删除重复数据
;WITH cte as (
	SELECT ROW_NUMBER() OVER(PARTITION BY [name] ORDER BY id) AS rid,* FROM kehu
)
DELETE FROM cte WHERE rid>1

--3. 查询name 的所有信息
SELECT [name] FROM kehu