前往小程序,Get更优阅读体验!
立即前往
首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >SQL 找出分组中具有极值的行

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

作者头像
白日梦想家
发布2020-07-20 11:00:57
1.7K0
发布2020-07-20 11:00:57
举报
文章被收录于专栏:SQL实现

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

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

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

最终的查询结果如下图。

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

窗口函数

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

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

代码语言:javascript
复制
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 分组,取出每个部门中的最高薪资,再和原表做一次关联就能获取到正确的结果。

代码语言:javascript
复制
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 条件中使用子查询。

代码语言:javascript
复制
SELECT 
  a.* 
FROM
  emp a 
WHERE a.sal = 
  (SELECT 
    MAX(sal) 
  FROM
    emp 
  WHERE deptno = a.deptno) 
ORDER BY deptno

外连接

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

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

代码语言:javascript
复制
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 的数据。

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

本文参与 腾讯云自媒体同步曝光计划,分享自微信公众号。
原始发表:2020-05-26,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 SQL实现 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体同步曝光计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
目录
  • 窗口函数
  • 子查询
  • 外连接
  • 总结
相关产品与服务
云数据库 MySQL
腾讯云数据库 MySQL(TencentDB for MySQL)为用户提供安全可靠,性能卓越、易于维护的企业级云数据库服务。其具备6大企业级特性,包括企业级定制内核、企业级高可用、企业级高可靠、企业级安全、企业级扩展以及企业级智能运维。通过使用腾讯云数据库 MySQL,可实现分钟级别的数据库部署、弹性扩展以及全自动化的运维管理,不仅经济实惠,而且稳定可靠,易于运维。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档