SQL经典实例（二）查询结果排序

1.以指定顺序返回查询结果

#deptno为10，根据工资从低到高排序
select ename,job,sal
	from emp
where deptno = 10
order by sal asc

--降序
-- order by sal desc 

--数值代替列
--order by 3 desc

结果：

2.多字段排序

--emp表里的数据，先按照deptno升序，再按照sal降序排列
select empno,deptno,sal,ename,job
	from emp
	order by deptno,sal desc

结果：

3.依据字串排序

select ename,job
from emp
order by substring(job,len(job)-2,2)
--(列名，起始位置，字符串长度)

结果：

4.排序时对Null值的处理

--加一个辅助列

--非NULL值comm升序排列，全部NULL值放到后面
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

--非NULL值comm降序排列，全部NULL值放到后面
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

--非NULL值comm升序排列，全部NULL值放到前面
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 

--非NULL值comm降序排列，全部NULL值放到前面
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

结果：

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

--如果job等于SALESMAN,按照comm来排序；否则按照sal排序
select ename,sal,job,comm
	from emp
	order by case when job ='SALESMAN'then comm else sal end
--or
select ename,sal,job,comm,
	case when job = 'SALESMAN' then comm else sal end as ordered
from emp
order by 5

结果：