Oracle 查询优化改写

第一章 单表查询

1、将空值转换为实际值

    SELECT coalesce(comm,0) FROM emp;

说明coalesce与nvl、decode的区别
①coalesce(comm,0)，若comm为空，则取0，否则返回comm；
适合多个参数，coalesce(EXPR1,EXPR2,EXPR3,…,EXPRn)，所有参数类型必须保持一致；
coalesce可返回第一个不为空的值。
②nvl(comm,0)只适合2个参数；将空值转换为0；
③decode(comm,’apple’,0),若comm=apple，返回0，否则返回NULL

2、拼接列
用字符串“||”把各列连在一起

    SELECT ename ||'的工作是'||job AS msg FROM emp WHERE deptno = 10;    ——————————————————    CLARK的工作是MANAGER

3、限制返回的行数

SELECT *   FROM (SELECT rownum AS an,emp.* FROM emp WHERE rownum <=2)WHERE sn = 2;

本语句返回的是第二行的数据，但是为什么先要写子查询呢？
因为rownum是依次对数据做标识的，需要先把所有的数据取出来，才能确认第二行。

4、随机返回n条记录

SELECT empno,ename  FROM (SELECT empno,ename FROM emp ORDER BY dbmsandom.value())WHERE rownum <= 3;

为什么要写一层嵌套？
对于随机返回n行数据，正确的写法应该是 先随机排序，再取数据 。否则每次随机返回的值会是一样的。

第二章 给查询结果排序

1、将返回结果排序

ORDER BY 1,2;

表示按照SELECT后的字段进行排序，1表示第一栏，2表示第二栏；
此处的意思是，先按照第一栏升序排列，在第一栏相同的情况下，按第二栏升序排列。

2、TRANSLATE
#语法格式
TRANSLATE(expr,from_string,to_string)
from_string 与 to_string 一一对应，如果在from_string中没有的，就不变。
如果to_string 为空，则返回空值；
如果to_string 对应的没有字符，删除from_string中列出的字符将会被消掉。

SELECT TRANSLATE('ab你好bcadefg','1abcdefg','1')AS NEW_STR FROM DUAL;NEW_STR------你好

“你好”不进行替换，其他字符对应位置均为空，故替换为空

3、处理排序空值
用 关键字NULLS FIRST 和NULLS LAST
空值在前 NULLS FIRST
空值在后 NULLS LAST

第三章 使用数字

1、正确的平均值
avg(coalesce(comm,0)) /需要把空值转换为0/
聚合函数会忽略空值，对avg,count会产生影响，根据需求决定把空值转换为零。

2、group by
如果select 后面有聚合函数，通常需要加group by；
当表中没有数据时， 不加group by会返回一行数据，但加了group by 会没有数据返回。

3、计算累计差
可将需要减的数，先变成负数，用CASE WHEN

4、dense_rank()
返回排序值，若有相同值，可都返回

dense_rank() over(PARTITION BY deptno ORDER BY sal DESC) 

5、计算出现次数最多的值
比如查看部门中哪个工资等级的员工最多，需要分4步进行

SELECT deptno,sal  FROM(select deptno,sal       dense_rank() over(PARTITION BY deptno ORDER BY 出现次数 DESC) 次数排序       FROM (SELECT sal,deptno,count(*) 出现次数             FROM emp             GROUP BY deptno,sal)x)y  where 次数排序 = 1；

第一步：计算不同工资出现的次数
第二步：按次数排序生成序号
第三步：根据序号过滤得到需要的结果
第四步：利用partition by 子句分别查询各部门哪个工资等级的员工最多

6、返回最值所在行数据
①标量–先取出最大值，再与此最大值关联，略麻烦

SELECT deptno,       empno,       (SELECT MAX(b.ename) FROM emp b WHERE b.sal = a.max_sal)工资最高的人,       ename,       salFROM (SELECT deptno,             empno,             MAX(sal) over (PARTITION BY deptno) max_sal,             ename,             sal       FROM emp a       WHERE deptno = 10)aORDER BY 1,5 DESC;

②分析函数

SELECT deptno,       empno,       MAX(ename)keep(dense_rank FIRST ORDER BY sal)over (PARTITION BY deptno) 工资最低的人,       MAX(ename)keep(dense_rank LAST ORDER BY sal)over (PARTITION BY deptno) 工资最高的人,       ename,       salFROM  empWHERE deptno = 10ORDER BY 1,6 DESC;

KEEP函数可以满足此要求，且可同时返回最大值和最小值；
另外，frist、last语句也可放在group 里与其他聚合函数一样使用，这时要去掉后面的over (partition by **)

7、求总和的百分比
总工资合计需要用到分析函数：sum()和over()
当over()后不加任何内容时，就是对所有的数据进行汇总。

SELECT deptno  部门,       工资合计,       总合计,       round((工资合计/总合计)*100，2) 工资比例FROM (SELECT deptno,工资合计,SUM(工资合计) over() 总合计        FROM(SELECT deptno,SUM(sal) 工资合计 FROM emp GROUP BY deptno)x)yORDER BY 1;

第一步：分组汇总
第二步：通过分析函数获取总合计
第三步：得到总合计周就可以计算比例

另外，可以用专用的比例函数“ratio_to_report”来直接计算

SELECT deptno,       empno,       ename,       sal,       round(ratio_to_report(sal) over(PARTITION BY deptno)*100,2) 工资比例FROM empORDER BY 1,2;

第四章 操作多个表

1、union all与空字符串
union all常用来于合并多个数据集，空字符串本身是varchar2类型，null可以是任何类型，故二者不等价。

2、union 与or
将or语句进行改写，如果改写成union all结果就是错的，因为 or是两个结果的合集而不是并集，故改写时需要改为union来去掉重复的数据。
*但是！*不仅2个 数据集间重复的数据会被去重，而且单个数据集里面重复的数据也会被去重，有重复数据集用UNION后得到的数据与预期会不一致。
所以，需要在去重前加一个可以唯一标识各行的列即可。
加入唯一列，既可保证正确的去重，也可防止不该发生的去重。
除了用唯一列、主键列外，还可以用rowid。
还可以增加rownum来当做唯一列。

3、组合相关的列
当有多个表关联时，join的方式更能清楚的看清各表之间的关系，建议优先使用JOIN的写法。

4、IN、EXISTS和 INNER JOIN
此3种写法并没有固定的哪种写法更高效，在写的时候，需要查看PLAN。

5、INNER JOIN、LEFT JOIN 、RIGHT JOIN、FULL JOIN
INNER JOIN返回2个表匹配的数据；
LEFT JOIN只返回与左表匹配的数据；

  select 1.str left_str,r.str right_str  from 1,r  where 1.v=r.v(+)  order by 1,2;  --以1为基准表，返回2中与1匹配的数据  

RIGHT JOIN返回与右表匹配的数据；

select 1.str left_str,r.str right_str  from 1,r  where 1.v(+)=r.v  order by 1,2;  --以2为基准表，返回1中与r匹配的数据 

FULL JOIN无（+）的写法；该方式均返回所有的数据，但只有相匹配的数据显示在同一行， 非匹配的行只显示一个表的数据。

6、自关联
自关联的话，其实就是2次查询同一个表，但是取不同的别名。用JOIN连接。

7、检查两个表中的数据及对应数据的条数是否相同
比较2个数据集的不同时，通常用类似下面的FULL JOIN语句

   SELECT v.empno,v.ename,b.empno,b.ename   FROM v   FULL JOIN emp b ON (b.empno = v.empno)   WHERE (v.empno IS NULL OR b.empno IS NULL)

有时需要对数据进行处理，再进行比较，比如上述语句再加一列显示相同数据的条数，再进行比较。

SELECT v.empno,v.ename,v.cnt,emp.empno,emp.ename,emp.cnt   FROM (SELECT empno,ename,COUNT(*) cnt FROM v GROUP BY empno,ename)v   FULL JOIN (SELECT empno,ename,COUNT(*) cnt FROM emp GROUP BY empno,ename)emp   ON (emp.empno = v.empno AND emp.cnt = v.cnt)   WHERE (v.empno IS NULL OR emp.empno IS NULL)

8、聚集与内连接
*有点没看懂，改天更

9、聚集与外连接
先做聚集操作，再外连接即可。

SELECT e.deptno,       SUM(e.sal) total_sal,       SUM(e.sal * eb2.rate) total_bonusFROM emp eLEFT JOIN(SELECT eb.empno,                 SUM(CASE                     WHEN eb.type = 1 THEN                     0.1                     WHEN eb.type = 2 THEN                     0.2                     WHEN eb.type = 3 THEN                     0.3                     END) rate           FROM emp_bonus eb           GROUP BY eb.empno)eb2 ON eb2.empno = e.empnoGROUP BY e.deptnoORDER BY 1;