<SQL.COOKER>第二章复盘打卡加个人心得
目录如下
2.1,已指定的次序返回查询结果
解决方案:
select ename,job,sal from emp
order by sal asc
-----------------------
一般默认升序,若需要降序排列可改为如下
-----------------------------------
select ename,job,sal from emp
order by sal desc
----------------------------------
有时候在排序时可以用列的位置代替列名
select ename,job,sal from emp
order by 3 desc
2.2,按多个字段排序
解决方案:
select empno,deptno,sal,ename,job
from emp
order by deptno ,sal desc
------------------------------------
若使用group by 或者 distinct ,则不能使用select 列表中没有的列进行排序
2.3,按字串排序
解决方案:
db2,mysql,oracle,postgresql使用如下
----------------------------------
select ename,job from emp
order by substr(job,length(job)-2)
------------------------------------
sql sever 使用如下
select ename,job from emp
order by substring(job,len(job)-2,2)
2.4,对字母,数字混合的数据进行排序
考虑这个视图
create view v as
select ename || ' ' || deptno as data
from emp
然后
select * from emp
得到如下结果
要通过的deptno排序或者 ename排序得到如下结果
解决方案:
-----order by deptno-------
select data from v
order by replace (data, replace( translate(data,'0123456789','##########'),'#',' '),'')
-----order by ename-------
select data from v
order by replace( translate(data,'0123456789','##########'),'#',' ')
2.5,处理排序空值
解决方案:
select ename,sal,comm
from emp
order by comm asc
---------------------------------
select ename,sal,comm
from emp
order by comm desc
------------------------------------
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中写法如下
-----------------------------------
select ename,comm from emp
order by comm nulls first
-------------------------------
select ename,comm from emp
order by comm nulls last
2.6 按数据项的键排序
解决方案:
select ename,sal,job,comm
from emp
order by case when job ='salesman' then comm else sal end
---------------------------------------------------------------
select ename,sal,job,comm
from emp
order by case when job ='salesman' then comm else sal end