SQL中的Distinct和Group By

本文通过一个在线图书馆系统的还书操作实例,详细介绍了如何使用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子句唯一的使用场合;

<think>我们正在讨论MySQLDISTINCTGROUP BY的区别。根据用户提供的引用,我们可以总结出以下几点: 1. **基本概念**: - `DISTINCT`:用于去除查询结果中的重复行,只返回唯的记录[^1]。 - `GROUP BY`:用于将结果集按个或多个列分组,通常与聚合函数(如COUNT, SUM, AVG等)起使用,返回每个组的汇总信息[^1][^4]。 2. **功能差异**: - `DISTINCT` 仅进行去重操作,不进行任何聚合计算[^4]。 - `GROUP BY` 除了分组外,还可以对每个组进行聚合计算,并且可以使用HAVING子句对分组后的结果进行过滤[^3][^4]。 3. **返回结果**: - `DISTINCT` 返回去重后的所有列的值(即每条唯记录)[^1]。 - `GROUP BY` 返回每个分组的汇总结果,通常包含分组列聚合函数的结果[^4]。 4. **性能比较**: - 在语义相同(即仅用于去重)且没有索引的情况下,`DISTINCT` 的效率通常高于 `GROUP BY`。因为 `GROUP BY` 可能会进行排序(触发filesort),导致效率降低[^2]。 - 但是,当有索引时,两者的性能可能接近,因为MySQL可以利用索引来避免排序[^2]。 5. **使用场景**: - 如果只需要去除重复记录而不需要聚合计算,使用 `DISTINCT` 更合适,因为它语义明确且效率较高[^3]。 - 如果需要对分组后的数据进行聚合计算或过滤(使用HAVING),则必须使用 `GROUP BY`[^3][^4]。 6. **灵活性**: - `GROUP BY` 比 `DISTINCT` 更灵活,因为它允许进行更复杂的操作,如聚合计算过滤[^3]。 - `DISTINCT` 作用于所有选择的列,而 `GROUP BY` 可以指定分组的列,并且可以选择不显示所有分组列(通过聚合函数处理其他列)[^4]。 **示例对比**: 假设有个表 `orders`: | order_id | customer_id | amount | |----------|-------------|--------| | 1 | 1 | 100 | | 2 | 1 | 100 | | 3 | 2 | 200 | 1. 使用 `DISTINCT` 获取唯的 `customer_id` `amount` 组合: ```sql SELECT DISTINCT customer_id, amount FROM orders; ``` 结果: | customer_id | amount | |-------------|--------| | 1 | 100 | | 2 | 200 | 2. 使用 `GROUP BY` 实现相同效果(仅去重): ```sql SELECT customer_id, amount FROM orders GROUP BY customer_id, amount; ``` 结果同上。 3. 使用 `GROUP BY` 进行聚合计算(例如求每个顾客的总金额): ```sql SELECT customer_id, SUM(amount) AS total_amount FROM orders GROUP BY customer_id; ``` 结果: | customer_id | total_amount | |-------------|--------------| | 1 | 200 | | 2 | 200 | **总结区别**: | 特性 | DISTINCT | GROUP BY | |--------------|------------------------------|------------------------------| | 主要目的 | 去重 | 分组并进行聚合计算 | | 聚合函数 | 不能使用 | 必须使用(除非只显示分组列) | | 过滤分组 | 不能使用HAVING | 可以使用HAVING | | 返回结果 | 唯记录 | 每个分组的汇总结果 | | 性能(无索引)| 通常较高(避免排序) | 可能较低(可能触发filesort) | | 灵活性 | 较低 | 较高(可进行复杂操作) | 因此,选择使用 `DISTINCT` 还是 `GROUP BY` 取决于具体需求:如果只需要去重,用 `DISTINCT`;如果还需要聚合或过滤分组,则必须用 `GROUP BY`。 --- §§相关问题§§ 1. 在什么情况下 `DISTINCT` `GROUP BY` 可以互换使用? 2. 如何优化包含 `DISTINCT` 或 `GROUP BY` 的查询性能? 3. `DISTINCT` 能否与聚合函数起使用?例如 `COUNT(DISTINCT column)` 是什么意思? 4. 为什么在需要对数据进行复杂处理时推荐使用 `GROUP BY` 而不是 `DISTINCT`?</think>### MySQL 中 `DISTINCT` `GROUP BY` 的区别详解 #### 1. **核心功能差异** | 特性 | `DISTINCT` | `GROUP BY` | |--------------|----------------------------|----------------------------| | **主要目的** | 单纯去重 | 分组 + 聚合计算 | | **聚合函数** | 不可直接使用 | 必须配合聚合函数(如 `COUNT`, `SUM`) | | **结果类型** | 返回原始数据的唯记录 | 返回分组的聚合结果 | | **过滤能力** | 无分组过滤功能 | 支持 `HAVING` 子句过滤分组 | **示例对比**: ```sql -- DISTINCT:仅返回唯的name SELECT DISTINCT name FROM users; -- GROUP BY:可配合聚合函数 SELECT name, COUNT(*) AS user_count FROM users GROUP BY name HAVING user_count > 1; -- 过滤分组 ``` #### 2. **性能差异(无索引时)** - **`DISTINCT` 效率更高** 两者都会分组操作,但 `GROUP BY` 可能触发排序(`filesort`)导致效率下降[^2]。 - **优化建议**: 对分组/去重字段添加索引可显著提升两者性能,例如: ```sql CREATE INDEX idx_name ON users(name); -- 提升DISTINCT/GROUP BY效率 ``` #### 3. **使用场景差异** | 场景 | 推荐方式 | 原因 | |-----------------------|----------------|----------------------------------------------------------------------| | 单纯去重 | `DISTINCT` | 语义明确,效率更高[^3] | | 分组+聚合计算 | `GROUP BY` | 必需(如统计每个名字的出现次数) | | 复杂业务处理 | `GROUP BY` | 支持 `HAVING` 过滤、多级分组、聚合计算等[^3] | | 多列去重 | 两者均可 | `SELECT DISTINCT col1, col2` ≈ `SELECT col1, col2 GROUP BY col1, col2` | #### 4. **语法差异示例 ```sql -- ✅ DISTINCT正确用法(去重) SELECT DISTINCT department FROM employees; -- ✅ GROUP BY正确用法(分组聚合) SELECT department, AVG(salary) FROM employees GROUP BY department HAVING AVG(salary) > 10000; -- ❌ DISTINCT错误尝试聚合 SELECT DISTINCT department, AVG(salary) -- 报错!不能混合使用 FROM employees; ``` #### 5. **总结对比表** | 维度 | `DISTINCT` | `GROUP BY` | |--------------|-------------------------------------|-------------------------------------| | **核心功能** | 去重 | 分组 + 聚合 | | **性能** | 更高(无索引时)[^2] | 可能因排序变慢 | | **灵活性** | 低(仅基础去重) | 高(支持HAVING、复杂聚合)[^3] | | **返回结果** | 原始数据行 | 分组摘要结果 | | **适用场景** | 快速获取唯值 | 数据统计与分析 | > **经验建议**: > - 仅需去重时优先用 `DISTINCT`(更简洁高效) > - 涉及统计计算时必用 `GROUP BY` > - 高频查询字段务必建索引[^1][^2] ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值