2.6 根据数据项的键排序

2.6 根据数据项的键排序
Q: 针对某条件逻辑排序，如： job = 'SALESMAN' 按照 comm 排序，否则根据 sal 排序
A: 使用 case 表达式来动态改变如何对结果排序

select ename, sal, job, comm
from emp
order by case when job = 'SALESMAN' then comm else sal end

ename   | sal   |      job      | comm
--------+------+-----------+------
TURNER | 1500 | SALESMAN   |      0
ALLEN   | 1600 | SALESMAN   |   300
WARD    | 1250 | SALESMAN   |   500
SMITH   |   800 | CLERK       |
JAMES   |   950 | CLERK       |
ADAMS   | 1100 | CLERK       |
MILLER | 1300 | CLERK       |
MARTIN | 1250 | SALESMAN   | 1400
CLARK   | 2450 | MANAGER    |
BLAKE   | 2850 | MANAGER    |
JONES   | 2975 | MANAGER    |
SCOTT   | 3000 | ANALYST    |
FORD    | 3000 | ANALYST    |
KING    | 5000 | PRESIDENT |
(14 rows)

Tables Used:
for MySQL

• http://www.hooto.com/home/rui/doc/archives/5089.html

for PostgreSQL

• http://www.hooto.com/home/rui/doc/archives/5090.html

Learn:
SQL Cookbook, by Anthony Molinaro.
Copyright 2006 O'Reilly Media, Inc.

--EOF--