原创

SQL去重的七种方式的用法以及优缺点

温馨提示:
本文最后更新于 2022年11月11日,已超过 582 天没有更新。若文章内的图片失效(无法正常加载),请留言反馈或直接联系我

SQL语句去重,常见的方法。

举例student表

create table student(
    id int,
    name varchar(20),
    age int,
    address varchar(200)
);

表数据

方法一:使用DISTINCT关键字进行去重。

DISTINCT关键字,在使用时,后面会跟上去重的字段。可以保证这些去重字段的数据不重复。

比如,取出student表中,不重复的address有哪些,可以使用如下SQL语句:

select distinct address from student;

    返回结果如下:

    这种方法,最大的优点是使用起来比较简单。

    但也有一个比较大的缺点,就是去重的字段与最终返回的结果集中的字段,是一致的。也就是说,在上面的SQL语句中,使用address字段进行去重,最终的结果,也只返回address一个字段。

    如果想以address字段去重,并且同时返回其他字段,DISTINCT是做不到的。distinct同时作用了两个字段或者全部字段,即必须全部字段都相同的才会被排除。

    方法二:使用GROUP BY关键字进行去重

    与DISTINCT关键字一样,GROUP BY关键字,也是标准SQL支持的常用的去重方法。它可以在去重的同时,同步返回其他字段的信息。

    还是以对address字段进行去重为例,其他字段可以使用聚合函数根据需要进行获取:

    select min(id),max(name),max(age),address from student group by address;

      返回结果如下:


      在上面的语句中,不仅对address字段进行了去重,也同时返回了id、name、age字段的信息。

      在这一点上,比DISTINCT要好用很多。

      不过,仔细一看,好像总是觉得哪里不对劲。

      id=1的学生,应该叫周俊廷,而在上面的返回结果中却是杨萧语,返回的age字段,也有同样的问题。

      也就是说,在返回的结果中,同一行的id、name、age,可能并不是同一个学生的,这就导致看起来数据有些混乱。

      如果对数据的一致性有要求,可以使用下面的第三种方法。

      方法三:使用窗口函数进行去重。

      窗口函数有好几种,使用起来大同小异,这里只介绍ROW_NUMBER() over(partition by ... order by ...)。

      SQL Server 通过Row_Number 函数给数据库表的记录进行标号,在使用的时候后面会跟over 子句,而over 子句主要用来对表中的记录进行分组和排序的。

      语法如下:

      ROW_NUMBER() OVER(PARTITION BY COLUMN1 ORDER BY COLUMN2)

      1.Partition BY 用来分组

      2.Order by 用来排序

      接下来用 row_number() over 进行去重。首先用address进行分组,id进行排序。

      别名rn是将重复的address用id排序,条件rn=1,是只取重复address的第一条。

      select id,name,age,address from (select id,name,age,address,row_number() over(partition by address order by id asc) as rn from student)a where a.rn = 1;

      ROW_NUMBER()窗口函数的含义是,先对数据按照partition by的字段进行分组,然后以order by的字段进行排序,序号从1开始递增。

      上面的SQL返回的结果为:

      这个返回结果,就完美多了。

      但是,需要注意的是,有些数据库是不支持窗口函数的。像MySQL数据库中就无法使用。

      方法四:使用IN去重

      这种方法的关键在于,找到一组不重复的数据的特征,然后以这个特征来取数据。

      比如:按address来去重,如果数据有重复,取id最大的那条。

      select * from student where id in (select max(id) from student group by address);

        SQL返回结果如下:

        当然,也可以取id最小的那条,将上面语句中的max改成min就可以了。

        这种方法适合表里有一个数据不重复的字段(上面SQL中的id字段)的情况。

        如果表中不存在这样一个字段,这种方法就不再适用了。但有些数据库,天生自带了类似的字段可以使用。

        比如,在ORACLE数据库中,可以使用ROWID替代上面SQL中的id字段。当然仅限于ORACLE数据库:

        select * from student where rowid in (select max(rowid) from student group by address);

          方法五:使用NOT EXISTS去重

          与方法四的思路类似,使用NOT EXISTS也可以实现同样的效果。

          select * from student a where not exists(select 1 from student b where a.address = b.address  and a.id > b.id);

            SQL返回结果如下:

            方法六:使用ALL关键字

            在MySQL数据库中,有一个特殊的操作符ALL,这是一个集合操作符。

            select * from student a where a.id <= ALL(select b.id from student b where a.address = b.address);

              SQL返回结果如下:

              在上面的SQL中,ALL操作符的意思是说,a.id字段要<=ALL操作符括号里查询出来的所有值。

              所以,这种方法的核心思路与方法四是类似的。

              方法七:使用INNER JOIN + GROUP BY关键字

              这种方法的核心思路,也与方法四是类似的。

              select a.* from student a inner join student b on a.address = b.address and a.id >= b.id group by a.id,a.name,a.age,a.address having count(*)=1;

                SQL返回结果如下:

                熟练使用上面七种数据去重的方法,基本上可以解决所有的数据去重问题。如果你有更好的方法,可以留言交流一下。

                思考

                distinct 和group by 的区别:

                (1)distinct常用来查询不重复记录的条数:count(distinct name),group by 常用它来返回不重记录的所有值。

                (2)在使用group by 分组后,在select中可以选择分组字段,和非分组字段的函数值,如 max()、min()、sum、count()等

                distinct 和row_number over()区别:

                (1)distinct 和 row_number over 都可以实现去重功能,而distinct 作用于当行的时候,其"去重" 是去掉表中字段所有重复的数据,作用于多行的时候是,其"去重"所有字段都相同的数据。

                (2)在使用row_number over 子句时候是先分组,然后进行排序,再取出每组的第一条记录"去重"

                正文到此结束
                该篇文章的评论功能已被站长关闭
                本文目录