SQL中的Distinct和Group By

最新推荐文章于 2025-04-02 20:35:19 发布

原创最新推荐文章于 2025-04-02 20:35:19 发布 · 1.8k 阅读

1 ·

CC 4.0 BY-SA版权

文章标签：

#sql #date #2010 #null #数据库 #oracle

数据库专栏收录该内容

1 篇文章

订阅专栏

本文通过一个在线图书馆系统的还书操作实例，详细介绍了如何使用SQL语句处理去除重复数据的问题，特别是使用distinct和group by的区别及应用场景。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

在项目中遇到写数据库层要处理去除重复数据的问题，使用distinct的过程中也遇到了一些新手容易遇到的简单问题。

自己查找和参考了别人的博文，自己建表试验了一些写法，对distinct和group by的使用有了一些了解。

不拿公司的项目说事，以在线图书馆系统的还书操作作为例子：
数据表 repay_record 表示还书明细信息表

id                              number not null,             //主键
repay_code              varchar2(18) not null,     //还书流水号
book_id                  number not null,             //书的id号
customer_id             number not null,             //顾客id
book_repay_date    date not null,                  //还书提交时间
book_repay_status varchar2(32) not null,   //还书状态，'0','1','2'
repay_handle_date date                           //还书处理完毕时间

解释一下应用场景：
    *顾客一次可以选择多本书进行还书操作，一次操作会产生一个还书流水号(REPAY_CODE)，
    *书如果没有被成功还掉是可以下次再还的。

截取部分数据：

      REPAY_CODE          BOOK_ID     BOOK_REPAY_DATE                REPAY_HANDLE_DATE            book_repay_status
-------------------------------------------------------------------------------------------------------------------------------------------
I2010062400000097    10007157    2010-6-24 上午 11:45:00        2010-6-24 上午 11:50:00        0
I2010062400000097    10007151    2010-6-24 上午 11:45:01        2010-6-24 上午 11:50:00        0
I2010062400000100    10007157    2010-6-24 上午 11:51:00        2010-6-24 上午 11:56:00        0
-------------------------------------------------------------------------------------------------------------------------------------------

查询需求：
    查找所有状态(book_repay_status)为0的还书流水号，要求去除重复，并且按照还书提交时间(book_repay_date)排序

第一个sql：
    select distinct t.repay_code
    from repay_record t
    where t.book_repay_status = '0'
    order by t.book_repay_date

    很直观，但是执行的时候报错： ORA-01791:not a SELECTed expression
    官方文档的大意是：select distinct 和order by一起使用的时候，order by中必须是常量或者select列表中出现的表达式

    关于这个错误，可以参考 http://blog.youkuaiyun.com/lovingprince/archive/2009/03/06/3963625.aspx，

个人觉得这篇文章讲得很不错，

    大概的意思就是，distinct会使得多条记录变成一条，存在取舍，因此排序就会存在拿哪一条记录去排序的问题，
    不管是单表还是多表，只要oracle自己区分不出根据哪个来排序就会出现这个异常。

第二个sql：
    select distinct t.repay_code, t.book_repay_date
    from repay_record t
    where t.book_repay_status = '0'
    order by t.book_repay_date

    按照Oracle的01791错误文档修改，sql是可以执行，但是结果不是想要的结果，这条sql检测出来的结果：
    REPAY_CODE          BOOK_REPAY_DATE
    --------------------------------------------
    I2010062400000097    2010-6-24 上午 11:45:00
    I2010062400000097    2010-6-24 上午 11:45:01
    I2010062400000100    2010-6-24 上午 11:51:00

    并没有出现将repay_code去重复的现象,
    原因很简单，因为distinct是对两个列都起作用了，只有repay_code和book_repay_date都相同的情况下，才会被当做重复数据并被处理掉。

    那如果这样写：
    select distinct (t.repay_code), t.book_repay_date
    from repay_record t
    where t.book_repay_status = '0'
    order by t.book_repay_date

    效果是一样的，这种写法distinct依然会对两个列都起作用。

    回头看我们的需求：查找所有状态(book_repay_status)为0的还书流水号，要求去除重复，并且按照还书提交时间(book_repay_date)排序
    需求中有一个问题是没有明确的，即对于相同的还书流水号，如果有重复记录，排序的时间以哪一条记录的还书提交时间(book_repay_date)为准？

    假设：添加限制，以重复记录中的最新(即最晚)时间为准

第三个sql：
    select t.repay_code, max(t.book_repay_date) as book_repay_date
    from repay_record t
    where t.book_repay_status = '0'
    group by t.repay_code
    order by book_repay_date

    执行结果：
    REPAY_CODE          BOOK_REPAY_DATE
    --------------------------------------------
    I2010062400000097    2010-6-24 上午 11:45:01
    I2010062400000100    2010-6-24 上午 11:51:00


    这是符合需求的sql语句。

==================================================================================

万恶的需求方开始需求变更：
    上层DAO只能接受一个String类型的反馈值，不是一个map或者一个object。

    修改第三个sql为：
    select t.repay_code
    from repay_record t
    where t.book_repay_status = '0'
    group by t.repay_code
    order by max(book_repay_date)

    轻松搞定需求方。

    但是，如果写成：
    select distinct t.repay_code
    from repay_record t
    where t.book_repay_status = '0'
    order by max(t.book_repay_date)
    就会报错（ora-00937），max()聚合函数必须搭配group by使用。

==================================================================================

万恶的需求方提出新的需求：
    业务需求：每一个还书流水号对应的一条或者多条记录中，还书提交时间应该是相同的；
    因此要提供一个查询接口供监控程序使用，获取所有不符合上述规则的还书流水号。

    select t.repay_code
    from repay_record t
    where t.book_repay_status = '0'
    group by t.repay_code
    having count(distinct t.book_handle_date)>1

==================================================================================
需求方受到启发，提出新的需求：
    在数据能满足上述要求（相同流水号的记录具备相同的还书提交时间）的情况下，按照旧的需求进行查询；如果数据不满足上述要求，则输出空的查询结果


    即：数据库中的数据如果为
    REPAY_CODE          BOOK_ID     BOOK_REPAY_DATE                REPAY_HANDLE_DATE            book_repay_status
    ---------------------------------------------------------------------------------------------------------
    I2010062400000097    10007157    2010-6-24 上午 11:45:00        2010-6-24 上午 11:50:00        0
    I2010062400000097    10007151    2010-6-24 上午 11:45:00        2010-6-24 上午 11:50:00        0
    I2010062400000100    10007157    2010-6-24 上午 11:51:00        2010-6-24 上午 11:56:00        0
    ---------------------------------------------------------------------------------------------------------

    则输出：
    REPAY_CODE          BOOK_REPAY_DATE
    --------------------------------------------
    I2010062400000097    2010-6-24 上午 11:45:00
    I2010062400000100    2010-6-24 上午 11:51:00


    如果数据库中的数据为：
    REPAY_CODE          BOOK_ID     BOOK_REPAY_DATE                REPAY_HANDLE_DATE            book_repay_status
    ---------------------------------------------------------------------------------------------------------
    I2010062400000097    10007157    2010-6-24 上午 11:45:00        2010-6-24 上午 11:50:00        0
    I2010062400000097    10007151    2010-6-24 上午 11:45:01        2010-6-24 上午 11:50:00        0
    I2010062400000100    10007157    2010-6-24 上午 11:51:00        2010-6-24 上午 11:56:00        0
    ---------------------------------------------------------------------------------------------------------
    则输出空的查询结果

    SQL:

    select t.repay_code
    from repay_record t
    where t.book_repay_status = '0' and
        not exists
            (select t1.repay_code as repay_code
            from repay_record t1
            where t1.book_repay_status = '0'
            group by t1.repay_code
            having count(distinct t1.book_repay_date)>1
            )
    group by t.repay_code
    order by max(t.book_repay_date)

===========================================================

总结一下：

1.    distinct的应用场合是去除重复的数据，distinct会作用于后面的所有数据列，不可以对指定的列使用聚合函数。
    group by是对记录根据指定的列进行分组，可以各个分组中指定的列应用聚合函数。

    可以说，distinct能实现的功能，group by都可以实现，且group by 更为功能强大。

2.    group by 配合having子句，可以对group by的分组结果进行以组为单位的筛选；这是having子句唯一的使用场合；