原始数据查询:
对comm字段排序。
先用case语句来区分null和非空
因此,对于空和非空的排序就可以直接用is_null字段来排序了。
1)
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
2)
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 asc,comm desc
3)
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 asc,comm asc
4)
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 asc
这样看的更清楚:
select ename,sal,comm,is_null 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 asc