MySQL 找到包含最大值和最小值的记录

最新推荐文章于 2024-07-03 03:24:07 发布

只是甲

最新推荐文章于 2024-07-03 03:24:07 发布

阅读量2k

点赞数 3

分类专栏： # MySQL CookBook MySQL从小工到专家之路文章标签：数据库 mysql sql

本文链接：https://blog.csdn.net/u010520724/article/details/109613638

版权

MySQL从小工到专家之路同时被 2 个专栏收录

181 篇文章 24 订阅

订阅专栏

MySQL CookBook

73 篇文章 3 订阅

订阅专栏

备注:测试数据库版本为MySQL 8.0

如需要scott用户下建表及录入数据语句，可参考:
scott建表及录入数据sql脚本

一.需求

查找表中的"两级"值。例如，找出表EMP中具有最高工资和最低工资的员工。

二.解决方案

2.1 子查询方法

select ename
 from  emp
where  sal in ( (select min(sal) from emp),
                (select max(sal) from emp) );

测试记录

mysql> select ename
    ->  from  emp
    -> where  sal in ( (select min(sal) from emp),
    ->                 (select max(sal) from emp) );
+-------+
| ename |
+-------+
| SMITH |
| KING  |
+-------+
2 rows in set (0.00 sec)

2.2 MySQL 8.0 窗口函数方法

select ename
  from (
select ename,sal,
       min(sal) over w as 'min_sal',
       max(sal) over w as 'max_sal'
  from emp
  window w as ()
       ) x
where sal in (min_sal,max_sal);

测试记录

mysql> select ename
    ->   from (
    -> select ename,sal,
    ->        min(sal) over w as 'min_sal',
    ->        max(sal) over w as 'max_sal'
    ->   from emp
    ->   window w as ()
    ->        ) x
    -> where sal in (min_sal,max_sal);
+-------+
| ename |
+-------+
| SMITH |
| KING  |
+-------+
2 rows in set (0.00 sec)