sql查询去重代码实例

sql查询去重

    if not object_id('tempdb..#t') is null
        drop table #t
    go
    create table #t([id] int,[name] nvarchar(1),[memo] nvarchar(2))
    insert #t
    select 1,n'a',n'a1' union all
    select 2,n'a',n'a2' union all
    select 3,n'a',n'a3' union all
    select 4,n'b',n'b1' union all
    select 5,n'b',n'b2'
    go


    --i、name相同id最小的记录(推荐用1,2,3),方法3在sql05时，效率高于1、2
    方法1:
    select * from #t a where not exists(select 1 from #t where name=a.name and id<a.id)

    方法2:
    select a.* from #t a join (select min(id)id,name from #t group by name) b on a.name=b.name and a.id=b.id

    方法3:
    select * from #t a where id=(select min(id) from #t where name=a.name)

    方法4:
    select a.* from #t a join #t b on a.name=b.name and a.id>=b.id group by a.id,a.name,a.memo having count(1)=1 

    方法5:
    select * from #t a group by id,name,memo having id=(select min(id)from #t where name=a.name)

    方法6:
    select * from #t a where (select count(1) from #t where name=a.name and id<a.id)=0

    方法7:
    select * from #t a where id=(select top 1 id from #t where name=a.name order by id)

    方法8:
    select * from #t a where id!>all(select id from #t where name=a.name)

    方法9(注:id为唯一时可用):
    select * from #t a where id in(select min(id) from #t group by name)

    --sql2005:

    方法10:
    select id,name,memo from (select *,min(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) as minid from #t a)t where minid=1

    生成结果:
    /*
    id          name memo
    ----------- ---- ----
    1           a    a1
    4           b    b1

    (2 行受影响)
    */


    --ii、name相同id最大的记录,与min相反:
    方法1:
    select * from #t a where not exists(select 1 from #t where name=a.name and id>a.id)

    方法2:
    select a.* from #t a join (select max(id)id,name from #t group by name) b on a.name=b.name and a.id=b.id order by id

    方法3:
    select * from #t a where id=(select max(id) from #t where name=a.name) order by id

    方法4:
    select a.* from #t a join #t b on a.name=b.name and a.id<=b.id group by a.id,a.name,a.memo having count(1)=1 

    方法5:
    select * from #t a group by id,name,memo having id=(select max(id)from #t where name=a.name)

    方法6:
    select * from #t a where (select count(1) from #t where name=a.name and id>a.id)=0

    方法7:
    select * from #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!<all(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 行受影响)

    */