oracle 空值替换6,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,

sal

FROM (SELECT deptno,

empno,

MAX(sal) over (PARTITION BY deptno) max_sal,

ename,

sal

FROM emp a

WHERE deptno = 10)a

ORDER 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,

sal

FROM emp

WHERE deptno = 10

ORDER 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)y

ORDER BY 1;

第一步：分组汇总

第二步：通过分析函数获取总合计

第三步：得到总合计周就可以计算比例

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

SELECT deptno,

empno,

ename,

sal,

round(ratio_to_report(sal) over(PARTITION BY deptno)*100,2) 工资比例

FROM emp

ORDER 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_bonus

FROM emp e

LEFT 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.empno

GROUP BY e.deptno

ORDER BY 1;