给查询结果排序
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;