SQL Cookbook（读书笔记）No.2

二、 查询结果排序

2.1 以指定的次序返回查询结果

Q:显示部门10中的员工名字、职位和工资，并按照工资升序排列。

A:使用order by 子句：

select ename,job,sal
from emp 
where deptno = 10
order by sal asc

PS:我们可以用列的编号代表排序所基于的列名，从1开始（这个数值必须不能大于SELECT选择列表中项数）

select ename,job,sal
from emp 
where deptno = 10
order by 3 asc

一般情况下都可以执照SELECT列表中没有的列来排序，但是必须显式地给出排序的列名。

注：sal列在select选择列表中没有出现，且不能用数字替代（你也不知道该字段的顺序，二元表结构是无序的)。

　　返回的结果集数据排列和上面一致，只是没有sal字段的数据。

select ename,job
from emp 
where deptno = 10
order by sal asc

如果在查询中使用GROUP BY或DISTINCT则不能按照SELECT列表中没有的列来排序。

在ORDER BY子句中可以使用SELECT选项中的别名：

select ename,job,sal as salary
from emp 
where deptno = 10
order by salary asc

2.3 按子串排序

Q:按子串的某一部分对查询结果排序。例如，要从EMP表中返回员工名字和职位，并且按照职位字段的最后两个字符排序，结果集应如下所示：

A:在ORDER BY子句中使用和SUBSTRING函数：

select ename,job
from emp
order by substring(job,len(job)-2,2)

PS： 使用DBMS的子串函数，可以很容易地按字符串的一部分来排序。要按照字符串的最后两个字符来排序，首先要找到字符串的末尾，并减2。起始位置就是字符串中的倒数第2个字符。然后，获取从起始位置开始的所有字符。SQL在SUBSTRING中需要第3个参数来指定要获取的字符数。在本例中，只要这个数目大于或等于2就可以了。

 

 2.5 处理排序空值

Q:在EMP中根据COMM排序结果，这个字段有空值(需要指定是否将空值排在最后)

--查询1
select ename,sal,comm
from emp 
order by 3 

--查询2
select ename,sal,comm
from emp 
order by 3 desc

结果集1：　　　　　　　　　　   结果集2：

         

PS: 使用CASE表达式来标记一个值是否为NULL。该标记有两个值分别表示NULL或非NULL，在ORDER BY子句增加该标记列，便可以很容易控制空值是否排在最前还是最后，而不会为空值而干扰。

--S1.COMM列非空数据升序，空值排在最后
select ename,sal,comm
from (
select ename,sal,comm,
case when comm is null then 0 else 1 end as is_null
from emp  
        ) x
order by is_null desc,comm

--S2.COMM列非空数据降序，空值排在最后
select ename,sal,comm
from (
select ename,sal,comm,
case when comm is null then 0 else 1 end as is_null
from emp  
        ) x
order by is_null desc,comm desc

--S3.COMM列非空数据升序，空值排在最前
select ename,sal,comm
from (
select ename,sal,comm,
case when comm is null then 0 else 1 end as is_null
from emp  
        ) x
order by is_null ,comm 

--S4.COMM列非空数据降序，空值排在最前
select ename,sal,comm
from (
select ename,sal,comm,
case when comm is null then 0 else 1 end as is_null
from emp  
        ) x
order by is_null ,comm desc

结果集：

S1　　　　　　　　　　　　S2　　　　　　　　　　　　S3　　　　　　　　　　　S4　

　　　　  　 　

 

2.6 根据数据项的键排序

 

Q:根据某些条件逻辑排序。Ex,若JOB是“SALESMAN”，要根据COMM来排序。否则根据SAL排序，返回如下结果集：

A:在ORDER BY子句中使用CASE语句

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

PS:下面的写法让人容易理解

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

转载于:https://www.cnblogs.com/DandBayer/archive/2012/02/08/2321012.html