--测试数据 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>1SELECT * FROM #T
按lz这种不加说明一行问句,估计一个distinct就满足了:select distinct * from table
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>1SELECT * FROM #T
http://www.maomao365.com/?p=4942