Content Entry

1.8 在 SELECT 子句中使用条件逻辑

Q: 在 SELECT 语句中,对数值执行 IF-ELSE 操作
A: 使用 CASE

Oracle,MySQL,PostgreSQL,MSSQL,DB2 (使用 CASE )
SELECT ename, sal,
CASE WHEN sal <= 2000 THEN 'UNDERPAID'
WHEN sal >= 4000 THEN 'OVERPAID'
ELSE 'OK'
END AS status
FROM emp

+--------+------+-----------+
| ename | sal | status |
+--------+------+-----------+
| SMITH | 800 | UNDERPAID |
| ALLEN | 1600 | UNDERPAID |
| WARD | 1250 | UNDERPAID |
| JONES | 2975 | OK |
| MARTIN | 1250 | UNDERPAID |
| BLAKE | 2850 | OK |
| CLARK | 2450 | OK |
| SCOTT | 3000 | OK |
| KING | 5000 | OVERPAID |
| TURNER | 1500 | UNDERPAID |
| ADAMS | 1100 | UNDERPAID |
| JAMES | 950 | UNDERPAID |
| FORD | 3000 | OK |
| MILLER | 1300 | UNDERPAID |
+--------+------+-----------+
14 rows in set (0.05 sec)

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