给查询结果排序
SELECT empno,ename,hredate FROM emp WHERE deptno=10 ORDER BY HIREDATE ASC;
也可以这样写
SELECT empno,ename,hredate FROM emp WHERE deptno=10 ORDER BY 3 ASC;
按第三列排序的意思
多字段排序(部门编号升序,工资降序)
SELECT empno, deptno,sal,ename,job FROM emp ORDER BY 2 ASC,3 DESC;
TRANSLATE
语法格式:TRANSLATE( expr, from_string , to_string )
SELECT TRANSLATE ('ab 你好 bcdefg','abcdefg','1234567')AS NEW_STR FROM DUAL;
输出结果为: 12你好234567
处理排序空值 用关键字 NULLS FIRST 和 NULLS LAST。
SELECT empno, deptno,sal,ename,job FROM emp ORDER BY 3 NULLS FIRST;
根据条件取不同的列中的值来排序
比如:领导对工资在5000到8000之间的员工更感兴趣,要求把这个范围的员工排在前面优先查看
SELECT
empno AS 编号,
ename AS 姓名,
sal AS 工资
FROM emp WHERE deptno=10
ORDER BY
CASE
WHEN sal>=5000 AND sal<=8000 THEN 1
ELSE 2
END,3;