MySQL根据数据项的键排序

备注:测试数据库版本为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)