删除重复记录并保留最大小最小主键行

一、需求，删除dname重复的记录，并且保留，id最小的，或最大的一行
id    dname
1    事业部
2    销售部
3    技术部
4    测试部
5    技术部
6    事业部
7    事业部

1.按dname分组，统计条数

select

    count(id) count,dname

from dept

group by dname

count dname

3   事业部

2   技术部

1   测试部

1   销售部

2.按dname分组，统计条数，筛选出count大于1的dname

select

    dname

from dept

group by dname

having count(id)>1

dname

事业部

技术部

3.查询出所有有重复的记录

select * from dept

where dname in

(

        select dname from dept

        group by dname

        having count(id)>1

)

4.按dname分组,取每组中id最小的记录

select min(id),dname from dept

group by dname

having count(id)>1

id

1

3

5.根据3的查询结果，除于4中的记录，就是要删除的记录

select * from dept

where dname in

(

        select dname from dept

        group by dname

        having count(id)>1

)

and id not in

(

    select min(id) id from dept

    group by dname

    having count(id)>1

)

id dname

5   技术部

6   事业部

7   事业部

6.删除指定的记录

delete from dept where id in(

    select id from (

        select * from dept

            where dname in

            (

                    select dname from dept

                    group by dname

                    having count(id)>1

            )

            and id not in

            (

                select min(id) id from dept

                group by dname

                having count(id)>1

            )

    )temptable

)

转载于:https://www.cnblogs.com/chaeyeon/p/7068421.html