Content Entry

2.6 根据数据项的键排序

Published: 2008-11-18 Categories: Cookbook Tags: SQL DB2 MySQL Oracle PostgreSQL Database MSSQL case

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--

comments loading