order by 的列可以不在select 里面
SELECT job from t_aa order by empno
而如果查询中使用group by DISTINCT 则order by 中的列要在select 里面
SELECT distinct job from t_aa order by empno --报错
SELECT job,count(1)from t_aa group by job order by job --不报错
SELECT job,count(1)from t_aa group by job order by empno --报错
SELECT job J from t_aa order by J ---可以使用别名
SELECT job J from t_aa order by "J" ---可以使用别名
SELECT job J from t_aa order by 1 ---可以使用列顺序
--对字母数字混合的数据排序
create view v
as
SELECT ename|| ' '||deptno data from emp
select * from emp for update
----按照数字排序
select data r,-------ALLEN 20
(translate (data,'0123456789','##########'))r2,---ALLEN ##
replace (
(translate (data,'0123456789','##########')),'#','') r3,---ALLEN 截取字符
replace(data,
replace (
(translate (data,'0123456789','##########')),'#',''),'')r4---20 截取数字
from v
order by r4
------处理空值排序
order by nulls last or first
-------根据数据项的键排序
根据某些条件逻辑,例如,在JOB是‘salesman’,要根据comm来排序,否则,根据sal排序。
解决方案 在order by 中使用case表达式
SELECT ename,job,sal,comm
from emp
order by case
when JOB = 'SALESMAN' then
comm
else
sal
end desc nulls last