备注:测试数据库版本为MySQL 8.0
如需要scott用户下建表及录入数据语句,可参考:
scott建表及录入数据sql脚本
问题:
要根据某些条件逻辑来排序。
例如,如果JOB是“SALESMAN”,要根据COMM来排序。
否则,根据SAL排序。
解决方案:
在ORDER BY 子句中使用CASE表达式:
select ename,sal,job,comm
from emp
order by case when job = 'SALESMAN' then comm else sal end;
mysql> select ename,sal,job,comm
-> from emp
-> order by case when job = 'SALESMAN' then comm else sal end;
+--------+---------+-----------+---------+
| ename | sal | job | comm |
+--------+---------+-----------+---------+
| TURNER | 1500.00 | SALESMAN | 0.00 |
| ALLEN | 1600.00 | SALESMAN | 300.00 |
| WARD | 1250.00 | SALESMAN | 500.00 |
| SMITH | 800.00 | CLERK | NULL |
| JAMES | 950.00 | CLERK | NULL |
| ADAMS | 1100.00 | CLERK | NULL |
| MILLER | 1300.00 | CLERK | NULL |
| MARTIN | 1250.00 | SALESMAN | 1400.00 |
| CLARK | 2450.00 | MANAGER | NULL |
| BLAKE | 2850.00 | MANAGER | NULL |
| JONES | 2975.00 | MANAGER | NULL |
| SCOTT | 3000.00 | ANALYST | NULL |
| FORD | 3000.00 | ANALYST | NULL |
| KING | 5000.00 | PRESIDENT | NULL |
+--------+---------+-----------+---------+
14 rows in set (0.01 sec)