专栏首页SQL实现SQL 找出分组中具有极值的行

SQL 找出分组中具有极值的行

你可能也遇到过这种需求:找出每个部门入职最早的员工的信息;获取每个科目最高分的学生信息;获取用户最近一次的完整登录信息。

这些需求有两个共同点:一是需要做分组,有按部门分组、有按科目、也有按用户分组;二是在分组里面找到存在极值的行,是整行数据,而不只是极值

就拿 emp 举例,要从 emp 表中获取每个部门薪资最高的员工的信息。emp 表的数据如下:

最终的查询结果如下图。

要实现这个查询功能,有多少种实现方法呢?

窗口函数

如果你在用 MySQL 5.8+,窗口函数可能是你最先想到的办法,因为它足够简洁、简单。

先按部门分组,再对组内按照薪资降序排序,取排序序号为 1 的行即为部门最高薪资的员工的信息。

SELECT 
  empno,
  ename,
  job,
  mgr,
  hiredate,
  sal,
  comm,
  deptno 
FROM
  (SELECT 
    *,
    rank() over (
      PARTITION BY deptno 
  ORDER BY sal DESC
  ) AS rk 
  FROM
    emp) t 
WHERE rk = 1 
ORDER BY deptno

这里需要注意,用来排序的窗口函数使用 rank() 或者 dense_rank(),而不能使用 row_number() ,因为有可能存在一个部门里两名或者和更多员工的薪资都是最高的,row_number() 不会给相同的排序条件分配同一个序号。

子查询

如果你的数据库还不支持窗口函数,那可以先对 emp 分组,取出每个部门中的最高薪资,再和原表做一次关联就能获取到正确的结果。

SELECT 
  a.* 
FROM
  emp a 
  INNER JOIN 
    (SELECT 
      deptno,
      MAX(sal) AS sal 
    FROM
      emp 
    GROUP BY deptno) b 
    ON b.deptno = a.deptno 
    AND b.sal = a.sal 
ORDER BY deptno

上面是自然连接的写法,你也可以在WHERE 条件中使用子查询。

SELECT 
  a.* 
FROM
  emp a 
WHERE a.sal = 
  (SELECT 
    MAX(sal) 
  FROM
    emp 
  WHERE deptno = a.deptno) 
ORDER BY deptno

外连接

外连接总能给我们带来惊喜,这次也不例外。

在此之前,你可能很难想到可以使用 Left Join 达到分组求极值的效果。现在就来揭开 Left Join 的神秘面纱。

SELECT 
  a.* 
FROM
  emp a 
  LEFT JOIN emp b 
    ON b.deptno = a.deptno 
    AND a.sal < b.sal 
WHERE b.sal IS NULL 
ORDER BY a.deptno

我们知道,在SELECT * FROM a left join b on 关联条件 语句中 ,不论在 b 表中是否有数据行可以和 a 表匹配,a 表的数据都会查询出来。不过,我们可以通过 WHERE 子句过滤 a 表返回的数据。

在关联条件 b.deptno = a.deptno AND a.sal < b.sal 中,只要 a.sal 不是分组内的最大值,总能在 b 表中找到比它大的数据。当 a.sal 是分组的内的最大值时,a.sal < b.sal 的条件不成立,关联出来的结果中 b 表的数据为 NULL。因此,通过 WHERE b.sal IS NULL 可以找到每个分组里面 a.sal 最大的记录。

总结

前两种方法我们最可能想到,它们的写法也很容易理解,而使用外连接就需要我们多一点反向思考,需要知道使用外连接可以关联出为 NULL 的数据。

上面这几种方法都能满足前文提出的需求,至于它们之间哪个执行更快,就留给读者你去思考了。

本文分享自微信公众号 - SQL实现(gh_684ee9235a26),作者:zero

原文出处及转载信息见文内详细说明,如有侵权,请联系 yunjia_community@tencent.com 删除。

原始发表时间:2020-05-26

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

我来说两句

0 条评论
登录 后参与评论

相关文章

  • SQL 计算小计和总计

    有一个完美的解决方案是使用 GROUP BY 子句的 ROLLUP 扩展。ROLLUP 在分组统计的基础上,再对结果进行相同操作(SUM、AVG、COUNT)的...

    白日梦想家
  • 模拟 GROUP_CONCAT() 函数

    MySQL 提供了 GROUP_CONCAT() 函数,可以很方便地针对某字段下的值聚合成一个字符串,字符串内部默认使用“,”分割。

    白日梦想家
  • 编写 SQL 的排除联接

    有两个表,就叫源表和目标表吧。它们有一个相同的字段,通过该字段可以把源表和目标表关联在一起,我们希望从源表中检索到的记录里的关联字段的值没有存在目标表中。举个例...

    白日梦想家
  • Java使用增强for循环和迭代器遍历Map集合

    版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/sinat_35512245/articl...

    大黄大黄大黄
  • CDH5之启用邮箱警报 原

    在使用CDH5的时候,各种警报信息,需要及时知道,CDH5平台自带了邮箱预警功能,此邮箱预警功能,可以使用CDH5平台自带的邮箱,也可配置自定义的邮箱,下面一一...

    云飞扬
  • Java之使用增强for循环和迭代器遍历

    版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/sinat_35512245/articl...

    大黄大黄大黄
  • 前端Tips#5 - 将异步函数 promise 化

    可以直接将代码粘贴到控制台中去查看效果:(以下代码片段将延迟两秒打印出 “Hi”)

    JSCON简时空
  • 从[低水平勤奋]到[爆发式成长]的高效策略

    上学时不清楚为何学习,随着大流,摸索着方法,完成一次次考试,磕磕绊绊,完成了研究生学业,但是依然不知道学习到底有什么用。

    用户2559057
  • 工厂方法模式

    概述        工厂方法模式,英文Factory method pattern,工厂方法模式是简单工厂模式的进化版, 看本文之间最好先看一下简单工厂模式。工...

    高爽
  • MySQL高可用新方案|MySQL InnoDB ReplicaSet介绍

    在新的版本中,MySQL官方引入了新的高可用解决方案MySQL InnoDB ReplicaSet。

    [3306 Pai ] 社区

扫码关注云+社区

领取腾讯云代金券