mysql未参与_MySQL 返回未包含在group by中的列

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)