SQL查询语句(查询结果排序)
1.以指定次序对查询结果排序
//显示部门10中的员工名字、职位和工资,并按照工资升序排序
select enamel, job, sal
from emp
where deptno = 10
order by sal asc
//显示部门10中的员工名字、职位和工资,并按照工资降序排序
select enamel, job, sal
from emp
where deptno = 10
order by sal desc
2.按多个字段排序
//在emp表中首先按照deptno升序排序在按照sal降序排序
select empno, deptno, ename, job, sal
from emp
order by deptno, sal desc
3.按子串排序
//按字符串的某一部分对查询结果排序
//按照职位的最后两个字符串排序
//DB2、MysSQL、Oracle和PostgerSQL
select ename, job
from emp
order by substr(job, length(job)-2)
//SQLServer
select ename, job
from emp
order by substring(job, len(job)-2, 2)
4.处理排序空值
//在一个表中的某一列可以存在空值,需要指定空值排在最后或者排在最前
//可以按照升序或者降序排序使得空值排在最前或者最后
select ename, sal, comm
from emp
order by comm asc
select ename, sal, comm
from emp
order by comm desc
//加入非空值的排序方式,与空值排序方式发生冲突
//DB2、mysql、postgersql和sqlserver可以使用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
//Oracle在order by子句中使用nulls first或nulls last来确定null是首先排序还是最后排序
select ename, sal, comm
from emp
order by comm desc, nulls first
5.根据数据项键排序
//要根据某些逻辑条件排序。例如如果job是“salesman”就按照comm排序,否则就按sal排序
select ename, sal, job, comm
from emp
order by case when job = 'salesman' then comm else sal end
select ename, sal, job, comm,
case when job = 'salesman' then comm else sal end as ordered
from emp
order by ordered