《SQL CookBook 》笔记-第二章-查询结果排序

目录

• • 2.3 排序时对 null 值的处理

第二章

shanzm

---

第二章 查询结果排序

2.1 以指定顺序返回查询结果

问题：
你想显示部门编号为 10 的员工的名字、职位和工资，并根据工资从低到高排序。

解决方案：
使用order by column_name

代码如下：

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

当然你可以使用多字段排序
比如先按照sal升序排序，然自后按照入职日期降序排列

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

【注意】

1. order by 默认是升序，即asc
2. 降序则使用desc
3. 在同时使用 order by 和 where 子句时，应该让 order by 位于
   where 之后，否则将会产生错误

---

2.2 依据子串排序

问题：
按照一个字符串的特定部分排列查询结果。

例如：
希望从 emp 表检索10号部门的员工的名字和职位，并且按照职位字段的最后两个字符对检索结果进行排序。

解决方案：
在 order by 子句里使用 substring() 函数。

代码如下：

select ename,job
from emp
where deptno=10
order by substring(job,len(job)-2,2)

结果：

ename	job
king	president
miller	clerk
clark	manager

【分析】
substring() 函数

第一个参数：字符串所在列的列名
第二个参数：取子串的开始位置
第三个参数：所取的子串的长度

---

2.3 排序时对 null 值的处理

问题：
排序依据的列中有null,把该列的值为null的行放在最后

例如：你想按照 emp 表的 comm 列对查询结果进行排序，但该字段可能为 null 。非 null 值以升序排列或降序排列，把全部 null 值都放到最后面

解决方案：
使用 case 表达式来动态调整排序项。

代码下：

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

结果如下：

ename	sal	comm
turner	1500	0
allen	1600	300
ward	1250	500
martin	1250	1400
blake	2850	null
clark	2450	null
scott	3000	null
king	5000	null
adams	1100	null
james	950	null
ford	3000	null
miller	1300	null
jones	2975	null
smith	800	null

此处你得想明白，这里面的子查询表x，都是临时的，其中is_null就是一个辅助列，我们按照这个列来排序。

其中子查询：

select ename,sal,comm,
    case when comm is null then 0 
    else 1 
    end as is_null
from emp

结果如下：

ename	sal	comm	is_null
smith	800	null	0
allen	1600	300	1
ward	1250	500	1
jones	2975	null	0
martin	1250	1400	1
blake	2850	null	0
clark	2450	null	0
scott	3000	null	0
king	5000	null	0
turner	1500	0	1
adams	1100	null	0
james	950	null	0
ford	3000	null	0
miller	1300	null	0

【注意】
此例在 from 子句里用到了内嵌视图（即子查询）。

ansi sql 标准规定要给它们取别名。
（只有 oracle 不要求指定这一类别名。）因此，我在解决方案里经常用类似 x 和 y 这样的别
名来标识内嵌视图。
注意最后紧挨着圆括号的字母 x 。
在这里，字母 x 变成了 from 子句里那个子查询返回的表的名字。
列别名是一个有用的工具，能帮我们写出自注释的代码；
相对而言，（本书中出现过的多数）内嵌视图的别名只是一种形式化的东西。
通常我会为它们取一个简单的名字，诸如 x 、 y 、 z 、 tmp1 和 tmp2 。

---

2.4 依据条件逻辑动态调整排序项

问题：
你希望按照某个条件逻辑来排序。

例如：
如果 job 等于 salesman ，就要按照 comm 来排序；否则，按照 sal 排序

解决方案1：
在 order by 子句里使用 case 表达式。

代码下：

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

【注意】case子句还可以在select句中使用。

你可记否？在select子句中时，case子句的end子句的最后要使用as定义一个列名
记住了，不论在哪，case子句的基础格式都是：

 case when condition1 then return_value1
 case when condition2 then return_value2
 else return_default_value
 end 

解决方案2：构造辅助列

注意case语句的返回值。

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

---