排序、union、内联、半联的改写笔记

最新推荐文章于 2020-12-05 19:45:08 发布

原创最新推荐文章于 2020-12-05 19:45:08 发布 · 295 阅读

1 ·

CC 4.0 BY-SA版权

oracle 专栏收录该内容

12 篇文章

订阅专栏

本文介绍了 Oracle 数据库中处理空值、排序、多表查询及连接等高级 SQL 技巧，包括使用 NULLSFIRST 和 NULLSLAST 控制空值排序、通过 UNION 和 UNION ALL 处理数据合并、以及半连接与内连接的转换方法。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

2.5 处理排序空值
comm

ORACLE默认排序，空值应该是在后面的

要想把空值排在前面可以在前面加 NULLS FIRST ，要放在后面就加 NULLS LAST

如：

SELECT ename, sal, comm FROM emp ORDER BY 3 NULLS FIRST;

如果是多列可以采用下面的方法：

如：

SELECT ename, sal, comm,mgr FROM emp ORDER BY 3 NULLS FIRST,4 nulls last;

ORDER BY 后面可以放表达式，列名，别名，列位置，函数

如这种：SELECT * FROM emp ORDER BY to_char(hiredate, 'yyyy-mm-dd')

需求： EMP表，查询员工的收入，但JOB为销售的就返回提成COMM，不是销售的就返回工资SAL

SELECT ename AS 姓名, sal AS 工资, job AS 工作, comm AS 提成,

CASE WHEN job = 'SALESMAN' THEN Comm ELSE Sal END AS 主要收入

FROM emp

ORDER BY 5;

通过上面这些，当JOB=‘SALESMAN’时返回提成，非销售的就返回工资。

需求：

有一个字段全是金额 1--10000都有如何把100左右的放在最前面然后再按顺序排列剩下的

然后先把100元左右或者等于100的放在前面然后别的金额再按金额大小排序

可以这样写：

SELECT empno,

ename,

CASE

WHEN sal> 1000 AND sal < 10000 THEN

ELSE

END AS 级别,

sal

FROM emp

ORDER BY CASE

WHEN sal> 1000 AND sal < 10000 THEN

ELSE

END,

sal;

合集：

SELECT str,v,NULL FROM l
UNION
SELECT str,v,status FROM r

union、intersect、minus都会对这两张表的结果集进行去除，而不是单独对哪个表进行去重

OR改成union ,union all （ or不能走索引，改成union ,union all 就能走索引了）、

什么时候用UNION,什么时候有UNION ALL要看业务情况，有没有交叉重复，如果有就用UNION，没有就可以用UNION ALL。如果改了以后还是不走索引，那就要判断一下有没必要改，或者说跑下SQL，看改之前速度快，还是改了速度快。

SELECT empno, ename FROM emp WHERE empno = 7788 OR ename = 'SCOTT';

可以这样改：

SELECT empno, ename FROM emp WHERE empno = 7788

UNION

SELECT empno, ename FROM emp WHERE ename = 'SCOTT';

还可以这样改：

select distinct empno, ename from

(

SELECT empno, ename FROM emp WHERE empno = 7788

UNION all

SELECT empno, ename FROM emp WHERE ename = 'SCOTT'

);

第3章：多表查询：

3.1 合并显示记录集

如图：

SELECT empno AS 编码, ename AS 名称, nvl(mgr, deptno) AS 上级编码 FROM emp

UNION ALL

SELECT deptno AS 编码, dname AS 名称, '' AS 上级编码 FROM dept;

使用会报错

因为空字串和空值并不是等价的。

要这样写：

SELECT empno AS 编码, ename AS 名称, nvl(mgr, deptno) AS 上级编码 FROM emp

UNION ALL

SELECT deptno AS 编码, dname AS 名称, to_number('') AS 上级编码 FROM dept;

看两个数据的差值用minus (比如改写语句以后，可以把两个结果放到两个表中，用来查询查询结果是否一致）

union 、union all与 OR的区别

union all不需要去重，两个数据集直接合并

union 两个数据合并再去除

举例：

SELECT empno, ename FROM emp WHERE empno = 7788 OR ename = 'SCOTT';

这种语句，如果CBO不进行改写的话，就没有变法走索引。

先建两个索引：

create index idx_emp_empno on emp(empno);
create index idx_emp_ename on emp(ename);

改成如下：

SELECT empno, ename FROM emp WHERE empno = 7788
UNION ALL
SELECT empno, ename FROM emp WHERE ename = 'SCOTT';

这时就是错的，因为前面只返回一条数据，而使用union all返回的是两条，所以需要用union 进行去重。

SELECT empno, ename FROM emp WHERE empno = 7788
UNION
SELECT empno, ename FROM emp WHERE ename = 'SCOTT';

其实它也就是这样的SQL：

SELECT DISTINCT empno, ename
FROM (SELECT empno, ename
FROM emp
WHERE empno = 7788
UNION ALL
SELECT empno, ename
FROM emp
WHERE ename = 'SCOTT')
ORDER BY 1;

OR 如果不走索引改成union ,union all就可以走索引

如：

SELECT deptno FROM emp WHERE mgr = 7698 OR job = 'SALESMAN' ORDER BY 1;

改成如下是错误的：

SELECT deptno FROM emp WHERE mgr = 7698

UNION

SELECT deptno FROM emp WHERE job = 'SALESMAN'

ORDER BY 1;

其相当于：

SELECT DISTINCT deptno

FROM (SELECT deptno FROM emp WHERE mgr = 7698

UNION ALL

SELECT deptno FROM emp WHERE job = 'SALESMAN')

ORDER BY 1;

可以这样改：

SELECT empno,deptno FROM emp WHERE mgr = 7788
UNION
SELECT empno,deptno FROM emp WHERE job = 'SALESMAN';

或者构造伪列：

SELECT deptno
FROM
(
SELECT ROWID,deptno FROM emp WHERE mgr = 7698
UNION
SELECT ROWID,deptno FROM emp WHERE job = 'SALESMAN'
)
ORDER BY 1;

要求返回与表emp2(empno,job,sal)中数据相匹配的emp(empno,ename,job,sal,deptno)信息。

这样写：

但是碰到空值就需要用nvl转换

SELECT empno, ename, job, sal, deptno

FROM emp

WHERE (ename, job, sal) IN (SELECT ename, job, sal FROM emp2);

当数据有重复造成修改后有问题时，可以通过

通过增加唯一列，来防止出错

如改成这样：

原语句：

SELECT deptno FROM emp

WHERE mgr = 7698 OR job = 'SALESMAN' ORDER BY

DEPTNO是重复的

直接这样改也是错的：

SELECT deptno FROM emp WHERE mgr = 7698

UNION

SELECT deptno FROM emp WHERE job = 'SALESMAN';

只有增加一个唯一列，从而达到使数据准确的目的

SELECT empno,deptno FROM emp WHERE mgr = 7698

UNION

SELECT empno,deptno FROM emp WHERE job = 'SALESMAN';

还可以这样：

WITH e AS (SELECT ROWNUM AS sn,deptno,mgr,job FROM v)
SELECT deptno
FROM
(
SELECT sn,deptno FROM e WHERE mgr = 7698
UNION
SELECT sn,deptno FROM e WHERE job = 'SALESMAN'
)
ORDER BY 1;

3.3 IN、EXISTS、INNER JOIN

需求：

要求返回与表emp2(empno,job,sal)中数据相匹配的emp(empno,ename,job,sal,deptno)信息。

ORACLE可以使用多列进行in,其他数据库是不行的。SQL SERVIE 就是不行。但有个前提就是这几个列不能有空值，如果有空值也就需要转换一下。

如：

SELECT empno, ename, job, sal, deptno

FROM emp

WHERE (ename, job, sal) IN (SELECT ename, job, sal FROM emp2);

如果遇到空值可以使用nvl(comm,-1)进行转换

如：

SELECT empno, ename, job, sal, deptno
FROM emp
WHERE (ename, job, sal) IN (SELECT ename, job, sal FROM emp2);

还可以这样改:

WHERE b.ename = a.ename

AND b.job = a.job

AND b.sal = a.sal -- 一一对应

如：

SELECT empno, ename, job, sal, deptno
FROM emp a
WHERE EXISTS (SELECT NULL
FROM emp2 b
WHERE b.ename = a.ename
AND b.job = a.job
AND b.sal = a.sal);

如果要求改成内联，怎么改？如下： （如果我们看到

我们这个例子比较特殊，都是唯一的，没有重复行。因为可以直接关联，如：

SELECT a.empno, a.ename, a.job, a.sal, a.deptno
FROM emp a
INNER JOIN emp2 b ON (b.ename = a.ename AND b.job = a.job AND b.sal = a.sal);

当半连接的关联列不唯一的时候需要对关联列进行去重。或改成唯一列。才能改内联，否者两者数据就不会等价。

如：SELECT * FROM dept WHERE dept.deptno IN (SELECT emp.deptno FROM emp);

必须改成：

SELECT dept.* FROM dept INNER JOIN (SELECT deptno FROM emp GROUP BY deptno) emp
on emp.deptno=dept.deptno;

改成这种就是错误的：

SELECT dept.* FROM dept INNER JOIN emp ON emp.deptno = dept.deptno;

这个半连接怎么改?

SELECT empno, ename, job, sal, deptno

FROM emp

WHERE (ename, job, sal) IN (SELECT ename, job, sal FROM emp2);

可以这样改写：

SELECT empno, ename, job, sal, deptno
FROM emp a
WHERE EXISTS (SELECT NULL
FROM emp2 b
WHERE b.ename = a.ename
AND b.job = a.job
AND b.sal = a.sal);

改成内联接怎么改？如下：

SELECT a.empno, a.ename, a.job, a.sal, a.deptno
FROM emp a

INNER JOIN emp2 b ON (b.ename = a.ename AND b.job = a.job AND b.sal = a.sal);

1、半连接只能改成内联

2、子查询关联列数据不能有重复(关联列要求为主键，这样是最保险的）

3、子查询数据不能有空值

inner join 内联就是两个表一一对应了才返回。也就是说要两个表都有关联数据。

left join 左联就是左边表的数据都返回，右边数据只返回匹配行

right join 右联就是右边表的数据都返回，左边数据只返回匹配行

full join 全外联接并不等待迪卡尔积，它是返回所有可能的数据。并不是笛卡尔的乘积模式。

LEFT_STR RIGHT_STR
-------- ---------
left_1
left_2
left_3 right_3
left_4 right_4
right_5
right_6

匹配数据，显示在同一行

自关联，同一个表，自己和自己关联。一般可以改成左联

反连接：not in ,not exists,left join

匹配的数据都不要就叫反连接

半连接：in ,exists

反连接改left join需要注意的地方：唯一需要注意的就是反连接子查询是否有固化的条件，如果子查询会固化，那么改写后就不会等价。

SELECT *

FROM dept

WHERE NOT EXISTS (SELECT NULL FROM emp WHERE emp.deptno = dept.deptno);

SELECT dept.*,emp.deptno
FROM dept
LEFT JOIN emp ON emp.deptno = dept.deptno

3.7 检测两个表中的数据是否相同
CREATE OR REPLACE VIEW v AS
SELECT * FROM emp WHERE deptno != 10
UNION ALL
SELECT * FROM emp WHERE ename = 'WARD';

两个表进行full join，并且关联列不能为空

SELECT *

FROM (SELECT empno, ename, COUNT(*) AS cnt FROM v GROUP BY empno, ename) v

FULL JOIN (SELECT empno, ename, COUNT(*) AS 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);

如果表中没有重复值，其实对两个表作个全外连接就行了

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);

但如果表中有重复值，就要先求出数量再FULL JOIN

SELECT empno, ename, COUNT(*) AS cnt FROM v GROUP BY empno, ename

举例：

CREATE TABLE emp_bonus (empno INT , received DATE , TYPE INT);
INSERT INTO emp_bonus VALUES( 7934, DATE '2005-5-17', 1 );
INSERT INTO emp_bonus VALUES( 7934, DATE '2005-2-15', 2 );
INSERT INTO emp_bonus VALUES( 7839, DATE '2005-2-15', 3 );
INSERT INTO emp_bonus VALUES( 7782, DATE '2005-2-15', 1 );

数据如下：

计算员工的奖金，

如果类型为1的时候，工资X10%作为奖金

如果类型为2的时候，工资X20%作为奖金

如果类型为3的时候，工资X30%作为奖金

求部门10里，sum(sal),sum(bonus)

首先求所有人的工资：

SELECT e.deptno,
e.empno,
e.ename,
e.sal,
(e.sal * CASE
WHEN eb.type = 1 THEN
0.1
WHEN eb.type = 2 THEN
0.2
WHEN eb.type = 3 THEN
0.3
END) AS bonus
FROM emp e
INNER JOIN emp_bonus eb ON (e.empno = eb.empno)
WHERE e.deptno = 10
ORDER BY 1, 2;

注意：7934是重复的，所以不能直接加sum求和

作业：

谜题51 预算与实际支出

create or replace view 预算 (项目 ,种类 ,预算费用)
as
select 1 , 9100 , 100.00 from dual union all
select 2 , 9100 , 15.00 from dual union all
select 3 , 9100 , 6.00 from dual union all
select 4 , 9200 , 8.00 from dual union all
select 5 , 9200 , 11.00 from dual;

create or replace view 支出(收据 ,项目, 支出费用)
as
select 1 , 1 , 10.00 from dual union all
select 2 , 1 , 20.00 from dual union all
select 3 , 1 , 15.00 from dual union all
select 4 , 2 , 32.00 from dual union all
select 5 , 4 , 8.00 from dual union all
select 6 , 5 , 3.00 from dual union all
select 7 , 5 , 4.00 from dual;

根据种类分组计算预算和支出，结果如下图：

谜题41

create or replace view 项目(项目编码 , 项目描述 )
as
SELECT 10 , 'Item 10' from dual union all
SELECT 20 , 'Item 20' from dual union all
SELECT 30 , 'Item 30' from dual union all
SELECT 40 , 'Item 40' from dual union all
SELECT 50 , 'item 50' from dual;
create or replace view 实际(项目编码,  实际费用 ,  支票号 )
as
SELECT 10 , 300.00 , '1111' from dual union all
SELECT 20 , 325.00 , '2222' from dual union all
SELECT 20 , 100.00 , '3333' from dual union all
SELECT 30 , 525.00 , '1111' from dual;

create or replace view 预算(项目编码  ,贷款 )
as
SELECT 10 , 300.00 from dual union all
SELECT 10 , 50.00 from dual union all
SELECT 20 , 325.00 from dual union all
SELECT 20 , 110.00 from dual union all
SELECT 40 , 25.00 from dual;