前端之家收集整理的这篇文章主要介绍了
ORACLE 优化查询改写（二），
前端之家小编觉得挺不错的，现在分享给大家，也给大家做个参考。
@H_
403_0@--2.1 以指定的次序返回
查询结果
--查看所雇员工的信息
SELECT empno,ename,hiredate FROM emp WHERE deptno=10 ORDER BY hiredate ASC;
SELECT empno,hiredate FROM emp WHERE deptno=10 ORDER BY 3 ASC;
SELECT empno,sal FROM emp WHERE deptno=10 ORDER BY 3 ASC;
--2.2按多个字段排序
--多列排序时，若前面的列有重复值，后面的排序才有用。
SELECT empno,deptno,sal,job FROM emp ORDER BY 2 ASC,3 DESC;
--2.3按子串排序
SELECT last_name AS
名称,phone_number AS 号码,salary AS 工资,substr(phone_number,-4) AS 尾号
FROM hr.employees where rownum<=5 ORDER BY 4;
--2.4 TRANSLATE(expr,from_string,to_string)
SELECT TRANSLATE('ab 你好 bcadefg','abcdefg','1234567') AS NEW_STR FROM DUAL;
--如果to_string为空，则返回空值
SELECT TRANSLATE('ab 你好 bcadefg','') AS NEW_STR FROM DUAL;
--如果to_string对应的位置没有字符,
删除from_string中列出的字符将会被取消
SELECT TRANSLATE('ab 你好 bcadefg','1') AS NEW_STR FROM DUAL;--1 你好 1
--2.5按数字和字母混合字符串中的字母排序
CREATE OR REPLACE VIEW V as SELECT empno || ' '|| ename AS data FROM emp;
SELECT * FROM V;
SELECT data,TRANSLATE(data,' 0123456789','-0123456789') AS ename FROM V ORDER BY 2;
--2.6处理排序的空值
--将空值排在最前面
SELECT ename,comm FROM emp ORDER BY 3 NULLS FIRST;
--2.7根据条件取不同列中的值比较
SELECT empno AS 编码,ename AS 姓名,CASE
WHEN sal>=1000 AND sal<=2000 THEN 1
ELSE 2
END AS 级别,sal AS 工资 from emp where deptno=30 ORDER BY 3,4;
