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

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

一.需求

正在执行一个group by查询，并希望返回哪些属于选择列表而不包含于group by子句的列。
通常，这是不可能的，因为对于这样的非组列，并不时每行都包含唯一值。

假设要找到每个部门中工资最高和最低的员工，以及每种职位中工资最高和最低的员工，要查看这些人的姓名、所在部门、职位名称以及工资。

希望返回的结果集如下:

+--------+--------+-----------+---------+-----------------+----------------+
| deptno | ename | job | sal | dept_status | job_status |
+--------+--------+-----------+---------+-----------------+----------------+
| 20 | SCOTT | ANALYST | 3000.00 | TOP SAL IN DEPT | TOP SAL IN JOB |
| 20 | FORD | ANALYST | 3000.00 | TOP SAL IN DEPT | TOP SAL IN JOB |
| 10 | MILLER | CLERK | 1300.00 | LOW SAL IN DEPT | TOP SAL IN JOB |
| 20 | SMITH | CLERK | 800.00 | LOW SAL IN DEPT | LOW SAL IN JOB |
| 30 | JAMES | CLERK | 950.00 | LOW SAL IN DEPT | NULL |
| 10 | CLARK | MANAGER | 2450.00 | NULL | LOW SAL IN JOB |
| 20 | JONES | MANAGER | 2975.00 | NULL | TOP SAL IN JOB |
| 30 | BLAKE | MANAGER | 2850.00 | TOP SAL IN DEPT | NULL |
| 10 | KING | PRESIDENT | 5000.00 | TOP SAL IN DEPT | TOP SAL IN JOB |
| 30 | ALLEN | SALESMAN | 1600.00 | NULL | TOP SAL IN JOB |
| 30 | WARD | SALESMAN | 1250.00 | NULL | LOW SAL IN JOB |
| 30 | MARTIN | SALESMAN | 1250.00 | NULL | LOW SAL IN JOB |
+--------+--------+-----------+---------+-----------------+----------------+

二.解决方案

使用内敛视图，按deptno和job找到最高工资和最低工资。然后，只保留工资最高或工资最低的员工。

2.1 子查询方法

select  deptno,ename,job,sal,
        case when sal = max_by_dept
             then 'TOP SAL IN DEPT'
             when  sal = min_by_dept
             then 'LOW SAL IN DEPT'
         end as dept_status,
        case when sal = max_by_job
             then 'TOP SAL IN JOB'
             when sal = min_by_job
             then 'LOW SAL IN JOB'
        end as job_status
  from  (
select  e.deptno,e.ename,e.job,e.sal,
        (select max(sal) from emp d
          where d.deptno = e.deptno) as max_by_dept,
        (select max(sal) from emp d
          where d.job = e.job) as max_by_job,
        (select min(sal) from emp d
          where d.deptno = e.deptno) as min_by_dept,
         (select min(sal) from emp d
           where d.job = e.job) as min_by_job
  from  emp e
        ) x
where  sal in (max_by_dept,max_by_job,
               min_by_dept,min_by_job);    

测试记录:

mysql> select  deptno,ename,job,sal,
    ->         case when sal = max_by_dept
    ->              then 'TOP SAL IN DEPT'
    ->              when  sal = min_by_dept
    ->              then 'LOW SAL IN DEPT'
    ->          end as dept_status,
    ->         case when sal = max_by_job
    ->              then 'TOP SAL IN JOB'
    ->              when sal = min_by_job
    ->              then 'LOW SAL IN JOB'
    ->         end as job_status
    ->   from  (
    -> select  e.deptno,e.ename,e.job,e.sal,
    ->         (select max(sal) from emp d
    ->           where d.deptno = e.deptno) as max_by_dept,
    ->         (select max(sal) from emp d
    ->           where d.job = e.job) as max_by_job,
    ->         (select min(sal) from emp d
    ->           where d.deptno = e.deptno) as min_by_dept,
    ->          (select min(sal) from emp d
    ->            where d.job = e.job) as min_by_job
    ->   from  emp e
    ->         ) x
    -> where  sal in (max_by_dept,max_by_job,
    ->                min_by_dept,min_by_job);
+--------+--------+-----------+---------+-----------------+----------------+
| deptno | ename  | job       | sal     | dept_status     | job_status     |
+--------+--------+-----------+---------+-----------------+----------------+
|     20 | SMITH  | CLERK     |  800.00 | LOW SAL IN DEPT | LOW SAL IN JOB |
|     30 | ALLEN  | SALESMAN  | 1600.00 | NULL            | TOP SAL IN JOB |
|     30 | WARD   | SALESMAN  | 1250.00 | NULL            | LOW SAL IN JOB |
|     20 | JONES  | MANAGER   | 2975.00 | NULL            | TOP SAL IN JOB |
|     30 | MARTIN | SALESMAN  | 1250.00 | NULL            | LOW SAL IN JOB |
|     30 | BLAKE  | MANAGER   | 2850.00 | TOP SAL IN DEPT | NULL           |
|     10 | CLARK  | MANAGER   | 2450.00 | NULL            | LOW SAL IN JOB |
|     20 | SCOTT  | ANALYST   | 3000.00 | TOP SAL IN DEPT | TOP SAL IN JOB |
|     10 | KING   | PRESIDENT | 5000.00 | TOP SAL IN DEPT | TOP SAL IN JOB |
|     30 | JAMES  | CLERK     |  950.00 | LOW SAL IN DEPT | NULL           |
|     20 | FORD   | ANALYST   | 3000.00 | TOP SAL IN DEPT | TOP SAL IN JOB |
|     10 | MILLER | CLERK     | 1300.00 | LOW SAL IN DEPT | TOP SAL IN JOB |
+--------+--------+-----------+---------+-----------------+----------------+
12 rows in set (0.00 sec)

2.2 MySQL 8.0 窗口函数方法

select  deptno,ename,job,sal,
        case when sal = max_by_dept
             then 'TOP SAL IN DEPT'
             when  sal = min_by_dept
             then 'LOW SAL IN DEPT'
         end as dept_status,
        case when sal = max_by_job
             then 'TOP SAL IN JOB'
             when sal = min_by_job
             then 'LOW SAL IN JOB'
        end as job_status
  from  (
select  deptno,ename,job,sal,
        max(sal) over w1 as 'max_by_dept',
        max(sal) over w2 as 'max_by_job',
        min(sal) over w1 as 'min_by_dept',
        min(sal) over w2 as 'min_by_job'
  from  emp
 window w1 as (partition by deptno),
        w2 as (partition by job)
        ) x
where  sal in (max_by_dept,max_by_job,
               min_by_dept,min_by_job);  

测试记录:

mysql> select  deptno,ename,job,sal,
    ->         case when sal = max_by_dept
    ->              then 'TOP SAL IN DEPT'
    ->              when  sal = min_by_dept
    ->              then 'LOW SAL IN DEPT'
    ->          end as dept_status,
    ->         case when sal = max_by_job
    ->              then 'TOP SAL IN JOB'
    ->              when sal = min_by_job
    ->              then 'LOW SAL IN JOB'
    ->         end as job_status
    ->   from  (
    -> select  deptno,ename,job,sal,
    ->         max(sal) over w1 as 'max_by_dept',
    ->         max(sal) over w2 as 'max_by_job',
    ->         min(sal) over w1 as 'min_by_dept',
    ->         min(sal) over w2 as 'min_by_job'
    ->   from  emp
    ->  window w1 as (partition by deptno),
    ->         w2 as (partition by job)
    ->         ) x
    -> where  sal in (max_by_dept,max_by_job,
    ->                min_by_dept,min_by_job);
+--------+--------+-----------+---------+-----------------+----------------+
| deptno | ename  | job       | sal     | dept_status     | job_status     |
+--------+--------+-----------+---------+-----------------+----------------+
|     20 | SCOTT  | ANALYST   | 3000.00 | TOP SAL IN DEPT | TOP SAL IN JOB |
|     20 | FORD   | ANALYST   | 3000.00 | TOP SAL IN DEPT | TOP SAL IN JOB |
|     10 | MILLER | CLERK     | 1300.00 | LOW SAL IN DEPT | TOP SAL IN JOB |
|     20 | SMITH  | CLERK     |  800.00 | LOW SAL IN DEPT | LOW SAL IN JOB |
|     30 | JAMES  | CLERK     |  950.00 | LOW SAL IN DEPT | NULL           |
|     10 | CLARK  | MANAGER   | 2450.00 | NULL            | LOW SAL IN JOB |
|     20 | JONES  | MANAGER   | 2975.00 | NULL            | TOP SAL IN JOB |
|     30 | BLAKE  | MANAGER   | 2850.00 | TOP SAL IN DEPT | NULL           |
|     10 | KING   | PRESIDENT | 5000.00 | TOP SAL IN DEPT | TOP SAL IN JOB |
|     30 | ALLEN  | SALESMAN  | 1600.00 | NULL            | TOP SAL IN JOB |
|     30 | WARD   | SALESMAN  | 1250.00 | NULL            | LOW SAL IN JOB |
|     30 | MARTIN | SALESMAN  | 1250.00 | NULL            | LOW SAL IN JOB |
+--------+--------+-----------+---------+-----------------+----------------+
12 rows in set (0.00 sec)