转贴自 http://www.hooto.com/home/rui/doc/node/list/treeid/1056/page/1

1.6 在 WHERE 子句中引用取别名的列

Q: 在 where 子句中引用别名
A: 将查询作为内联视图就可以引用其中的别名

Oracle,MysqL,Postgresql,MSsql,DB2 (使用内联视图)
SELECT *
FROM (
SELECT sal AS salary,comm AS commission
FROM emp
) x
WHERE salary < 5000 AND commission IS NOT NULL

+--------+------------+
| salary | commission |
+--------+------------+
| 1600 | 300 |
| 1250 | 500 |
| 1250 | 1400 |
| 1500 | 0 |
+--------+------------+
4 rows in set (0.00 sec)

PS: 子句优先级 FROM -> WHERE -> SELECT

1.7 连接列值

Q: 将多列值连接作为一列返回
A: 使用内置函数 “concat”

DB2,Oracle,Postgresql ( concat函数，以及简写 “||” 双竖线连接符 )
SELECT ename || ‘ WORKS AS A ‘ || job AS msg
FROM emp
WHERE deptno = 10

MysqL ( concat函数 )
SELECT concat(ename,‘ WORKS AS A ‘,job) AS msg
FROM emp
WHERE deptno = 10

sql Server ( “+” 运算符连接)
SELECT ename + ‘ WORKS AS A ‘ + job AS msg
FROM emp
WHERE deptno = 10

+—————————+
| msg |
+—————————+
| CLARK WORKS AS A MANAGER |
| KING WORKS AS A PRESIDENT |
| MILLER WORKS AS A CLERK |
+—————————+
3 rows in set (0.04 sec)

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

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

Oracle,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)

1.9 限制返回的行数

Q: 如何限制查询中返回的行数
A: 使用数据库内置函数

DB2 ( 使用 FETCH FIRST 子句 )
SELECT *
FROM emp FETCH FIRST 5 ROWS ONLY

MysqL,Postgresql ( 使用 LIMIT )
SELECT *
FROM emp LIMIT 5

Oracle ( 使用 ROWNUM )
SELECT *
FROM emp
WHERE ROWNUM<= 5 ( ROWNUM = 5 错误的语法 )

sql Server ( 使用 TOP )
SELECT TOP 5
FROM emp

1.10 随机返回 n 条记录

Q: 从表中随机返回 n 条记录，每次执行返回不同的结果集
A: 使用DBMS内置函数生成随机数值，在 ORDER BY 中使用该函数随机排序

DB2 ( 使用 RAND 内置函数 )
SELECT ename,job
FROM emp
ORDER BY rand() FETCH FIRST 5 ROWS ONLY

MysqL ( 使用 RAND 内置函数 )
SELECT ename,job
FROM emp
ORDER BY rand() LIMIT 5

Postgresql ( random() )
SELECT ename,job
FROM emp
ORDER BY random() LIMIT 5

Oracle ( 使用 DBMS_RANDOM 包中的内置函数 VALUE )
SELECT *
FROM (
SELECT ename,job
FROM emp
ORDER BY dbms_random.value()
)
WHERE ROWNUM <= 5

sql Server ( newid() )
SELECT top 5 ename,job
FROM emp
ORDER BY newid()

PS: 在 ORDER BY 子句中指定数字常量时，是要求根据 SELECT 列表中相应位置的列来排序，在 ORDER BY 子句中使用函数时，则按函数在每一行计算结果排序

1.11 查找空值

Q: 查找某列值为空的行
A: 使用 IS NULL，或者 IS NOT NULL

Oracle,DB2 (使用IS NULL,IS NOT NULL)
SELECT *
FROM emp
WHERE comm IS NULL

1.12 将空值替换为实际值

Q: 用非空值替换空值
A: 使用 COALESCE

Oracle,DB2 (使用COALESCE())
SELECT coalesce(comm,0)
FROM emp

+——————-+
| coalesce(comm,0) |
+——————-+
| 0 |
| 300 |
| 500 |
| 0 |
| 1400 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
| 0 |
+——————-+
14 rows in set (0.00 sec)

1.13 按模式搜索

Q: 需要返回匹配特定子串或模式的行，比如在部门 10 和 20 中，返回名字有一个 “I”,或者职务中带有 “ER” 的员工行
A: 使用 LIKE 运算符，通配符 “%”

Oracle,DB2 (使用 LIKE)
SELECT ename,job
FROM emp
WHERE deptno IN (10,20)
AND (ename LIKE ‘%I%’ OR job LIKE ‘%ER’)

2.3 按子串排序

2.3 按子串排序
Q: 按照字符串某一部分排序,如按照某字段值的最后两个字符排序
A: 使用 DBMS 内置函数

DB2,Postgresql ( substr() )
SELECT ename,job
FROM emp
ORDER BY substr(job,length(job) - 2)

sql Server ( substring() )
SELECT ename,job
FROM emp
ORDER BY substring(job,len(job) - 2,2)

2.4 对字母数字混合的数据排序

2.4 对字母数字混合的数据排序
Q: 现有字母和数字混合的数据，按照数字或字母部分来排序，建立如下视图:

create view V
as
select ename ||’ ‘|| deptno as data
from emp ;

select * from V ;

data
———–
SMITH 20
ALLEN 30
WARD 30
JONES 20
MARTIN 30
BLAKE 30
CLARK 10
SCOTT 20
KING 10
TURNER 30
ADAMS 20
JAMES 30
FORD 20
MILLER 10
(14 rows)

分别实现对 data 的字母和数字排序

A: 使用函数 replace(),translate()

Oracle,Postgresql
/* order by deptno */
select data
from V
order by replace(data,
replace(
translate(data,‘0123456789′,‘##########’),‘#’,”),”)
data
———–
MILLER 10
CLARK 10
KING 10
SCOTT 20
JONES 20
SMITH 20
ADAMS 20
FORD 20
WARD 30
TURNER 30
ALLEN 30
BLAKE 30
MARTIN 30
JAMES 30
(14 rows)

/* order by ename */
select data
from V
order by replace(
translate(data,”)

data
———–
ADAMS 20
ALLEN 30
BLAKE 30
CLARK 10
FORD 20
JAMES 30
JONES 20
KING 10
MARTIN 30
MILLER 10
SCOTT 20
SMITH 20
TURNER 30
WARD 30
(14 rows)

DB2 (隐式类型转换严格，为使视图有效，需要将 deptno 转换成 char 类型)
/* order by deptno */
select *
from (
select ename ||’ ‘|| cast(deptno as char(2)) as data
from emp
) V
order by replace(data,‘##########’,‘0123456789′),”)

/* order by ename */
select *
from (
select ename ||’ ‘|| cast(deptno as char(2)) as data
from emp
) V
order by replace(
translate(data,”)

MysqL,sql Server (不支持 translate(),无解决方案)

2.5 处理排序空值

2.5 处理排序空值
Q: 指定是否将空值字段行排在最后
A: 使用 case 表达式在 order by 子句中增加标记列；或 RDBMS 特殊方案

DB2,sql Server,Oracle
/* all nulls last */
select ename,comm,is_null
from (
select ename,
case when comm is null then 0 else 1 end as is_null
from emp
) x
order by is_null desc,comm

ename | sal | comm | is_null
——–+——+——+———
TURNER | 1500 | 0 | 1
ALLEN | 1600 | 300 | 1
WARD | 1250 | 500 | 1
MARTIN | 1250 | 1400 | 1
SCOTT | 3000 | | 0
KING | 5000 | | 0
ADAMS | 1100 | | 0
JAMES | 950 | | 0
FORD | 3000 | | 0
SMITH | 800 | | 0
MILLER | 1300 | | 0
JONES | 2975 | | 0
BLAKE | 2850 | | 0
CLARK | 2450 | | 0
(14 rows)

/* all nulls first */
select ename,
case when comm is null then 0 else 1 end as is_null
from emp
) x
order by is_null,comm

ename | sal | comm | is_null
——–+——+——+———
MILLER | 1300 | | 0
ADAMS | 1100 | | 0
JAMES | 950 | | 0
FORD | 3000 | | 0
SMITH | 800 | | 0
JONES | 2975 | | 0
BLAKE | 2850 | | 0
CLARK | 2450 | | 0
SCOTT | 3000 | | 0
KING | 5000 | | 0
TURNER | 1500 | 0 | 1
ALLEN | 1600 | 300 | 1
WARD | 1250 | 500 | 1
MARTIN | 1250 | 1400 | 1

Oracle 9i or later (使用 “nulls last”,“nulls first”)
/* all nulls last */
select ename,comm
from emp
order by comm nulls last

/* all nulls first */
select ename,comm
from emp
order by comm nulls first

2.6 根据数据项的键排序

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

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

DB2技巧