sql server 如何删除重复数据只留一条

[LiuLiPan]

sql server 如何删除重复数据只留一条

[二月十六]

--测试数据
if not object_id(N'Tempdb..#T') is null
	drop table #T
Go
Create table #T([id] int,[name] nvarchar(22))
Insert #T
select 1,N'小明' union all
select 1,N'小明' union all
select 1,N'小明' union all
select 2,N'小张' union all
select 2,N'小张' union all
select 3,N'小李'
Go
--测试数据结束
;WITH cte AS (
SELECT *,ROW_NUMBER()OVER(PARTITION BY name ORDER BY id) AS rn FROM #T
)
DELETE FROM cte WHERE rn>1

SELECT * FROM #T

[netcup]

数据库设计最好要设置主键，对于多方面都有利。如果有主键，则删除语句很简单。

[iamggggs]

按lz这种不加说明一行问句，估计一个distinct就满足了：

select distinct * from table

[qq_25073223]

sql server下使用row_number删除重复数据，只保留一条，请参阅下面的文章 http://www.maomao365.com/?p=4942