sqlserver去重记录_SQLServer 去除重复记录经典实例

T a where ID=(select top 1 ID from #T where Name=a.name order by ID desc)

--方法8:

select * from #T a where ID!&ltall(select ID from #T where Name=a.Name)

--方法9(注:ID为唯一时可用):

select * from #T a where ID in(select max(ID) from #T group by Name)

--SQL2005:

--方法10:

select ID,Name,Memo from (select *,max(ID)over(partition by Name) as MinID from #T a)T where ID=MinID

--方法11:

select ID,Name,Memo from (select *,row_number()over(partition by Name order by ID desc) as MinID from #T a)T where MinID=1

--生成结果2:

/*

ID Name Memo

----------- ---- ----

3 A A3

5 B B2

(2 行受影响)

*/

--2、删除重复记录有大小关系时,保留大或小其中一个记录

--&gt --&gt (Roy)生成測試數據

if not object_id('Tempdb..#T') is null

drop table #T

Go

Create table #T([ID] int,[Name] nvarchar(1)