備注:測驗資料庫版本為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)