SQL查询优化改写

曾经终败给现在 2024-02-18 20:41 73阅读 0赞

第1章：单表查询  
  
\--查找空值  
select \* from emp where comm is null;  
  
/\*  
\* replace(1,2,3)  
\* 1:字段,2:旧内容 ，3:新内容  
\*/  
select replace('abcdefg','a',null) from dual;  
  
\---2018-01-16补充  
/\*\*  
但是提供了一个 instr() 方法 具体用法：  
1. '保定市南市区' =>可以是表达式，也可以是具体数据  
2. '市'=>为分离的标志，这里为两组数据中的“市”这个字  
3. 第一个1为从左边开始，如果为-1，则从右边开始。  
4. 第二个1为“市”出现的第几次。  
\*\*/  
select instr('保定市南市区','市',1,1) from dual;的值就是5  
select instr('保定市南市区','市',1,2) from dual;的值就是9  
  
  
\--coalesce  
select coalesce('',null,'0','-1') from dual;  
  
\--随机数  
select dbms\_random.value from dual;  
  
  
\--随即获取前三条记录  
select empno,ename from emp where rownum <= 3 order by dbms\_random.value;  
  
\--模糊查询  
select empno,ename from emp where ename like 'S%' ; -- % 任意个字符  
select empno,ename from emp where ename like '\_D%' ;-- \_一个字符  
  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
  
第2章：给查询结果排序  
  
\--order by 1,2,3  
select empno,ename from emp order by 2;  
  
\--TRANSLATE(expr,from,to) --- expr: 代表一串字符，from 与 to 是从左到右一一对应的关系，如果不能对应，则视为空值  
select translate('ab您还cefda','abcdefg','1234567') from dual;  
select translate('ab您还cefda','abcdefg','') from dual; --如果\_to为空，则返还为空  
select translate('ab您还cefda','abcdefg','1') from dual; --如果\_to长度小于\_from 则补充'';  
select data,translate(data,'- 0123456789','-') from v ;-- 其中-为通配符，任意一字符  
  
\---nulls last  
select empno,ename,comm from emp order by 3 nulls first ;  
  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
  
第3章：操作多个表  
  
\---union与or  
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';  
  
\--创建索引  
create index index\_emp\_empno on emp(empno);  
create index index\_emp\_ename on emp(ename);  
  
\--plan  
alter session set "\_b\_tree\_bitmap\_plans" = false;  
explain plan for select empno,ename from emp where empno=7788 or ename='SCOTT';  
select \* from table(dbms\_xplan.display);  
  
alter session set "\_b\_tree\_bitmap\_plans" = false;  
explain plan for select empno,ename from emp where empno=7788  
UNION  
select empno,ename from emp where ename='SCOTT';  
select \* from table(dbms\_xplan.display);  
  
\--with ::创建临时视图；  
with e as (select empno,ename from emp)  
select \* from e;  
select \* from e;--报错，视图已无效;  
  
\------------IN ,EXISTS,INNER JOIN  
/\*create table emp2 as select ename,job,sal,comm from emp where job='CLERK';\*/  
  
explain plan for  
select empno,ename,job,sal,deptno from emp where (ename,job,sal) in (select ename,job,sal from emp2);  
select \* from table(dbms\_xplan.display);  
  
explain plan for  
select a.empno, a.ename, a.job, a.sal, a.deptno  
from emp a  
where exists (select 1  
from emp2 b  
where a.ename = b.ename  
and a.job = b.job  
and a.sal = b.sal);  
select \* from table(dbms\_xplan.display);  
  
\--inner join ::::hash join  
explain plan for  
select a.empno, a.ename, a.job, a.sal, a.deptno  
from emp a inner join emp2 b on a.ename = b.ename and a.job = b.job and a.sal = b.sal;  
select \* from table(dbms\_xplan.display);  
  
  
\--- date 'yyyy-mm-dd'  
select date '2017-12-01' from dual;  
  
\--- timestamp 'yyyy-mm-dd hh24:mi:ss'  
select timestamp '2017-09-09 12:21:32' from dual;  
  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
38  
39  
40  
41  
42  
43  
44  
45  
46  
47  
48  
49  
50  
51  
52  
53  
54  
55  
56  
  
第4章：插入、更新、删除  
  
\--default  
create table test(  
c1 varchar2(10) default '默认1',  
c2 varchar2(10) default '默认2',  
c3 varchar2(10) default '默认3',  
c4 date default sysdate  
);  
  
insert into test values(default,default,default,default); --c1,c2,c3,c4 设置 default  
insert into test(c1,c2) values(default,default); --c3,c4 依然设置 default  
insert into test(c1,c2,c3) values(default,null,'手输入值');--c2:null ,c1：默认值;  
  
select \* from test;  
  
\--insert into view::: 阻止对某几列的插入  
create or replace view v\_test as select c1,c2,c3 from test;  
insert into v\_test values(default,default,default); --提示，不允许使用default;  
  
insert into v\_test values('手动c1','手动c2','手动c3');--正确执行，为了屏蔽程序手动输入c4  
select \* from test;  
  
\--复制表的定义以及数据  
create table test2 as select \* from test;--复制表的定义&&数据  
select \* from test2;  
  
create table test3 as select \* from test where 1=2; --仅复制表定义  
select \* from test3;  
insert into test3 select \* from test;--仅复制数据1  
insert into test3(c1) (select ename from emp)--仅复制数据2  
\---view;::with check option  
INSERT INTO (  
SELECT C1,C2,C4 FROM TEST WHERE C4 <= SYSDATE WITH CHECK OPTION --c4:必须<= sysdate  
)  
VALUES('C1','C2',SYSDATE+11); --- 提示 违反with check option规约  
  
  
\--inset all  
insert all  
into test2(c1,c2) values(empno,ename)  
into test3(c1,c2) values(empno,ename)  
select \* from emp;  
  
\--inset all when then当第一个，第二个条件同时满足时， 都插入  
insert all  
when job='CLERK' then into test2(c1,c2) values(empno,ename)  
when deptno in ('20','30') then into test3(c1,c2) values(empno,ename)  
select \* from emp;  
  
\--insert first当第一个条件满足时，后续的不再插入  
insert first  
when job='CLERK' then into test2(c1,c2) values(empno,ename)  
when deptno in ('20','30') then into test3(c1,c2) values(empno,ename)  
select \* from emp;  
  
  
\---merge into 更新  
create table bonuses(empolyee\_id number,bonus number default 100);  
insert into bonuses(empolyee\_id) (select empno from emp where emp.job='CLERK');  
\--SELECT \* FROM bonuses  
  
merge into bonuses a --只能更新 bonuses  
using(select empno,sal from emp where empno>=7900 ) b  
on(a.empolyee\_id = b .empno)  
when matched then ---匹配  
update  
set a.bonus = b.sal --不允许更新 on（）里面的字段  
/\* where b.sal < 1000 ---这里的where会使得 后面delete的where无效\*/  
delete  
where a.bonus <1000  
when not matched then  
insert (a.empolyee\_id,a.bonus)  
values (b.empno,b.sal\*0.1)  
where b.sal <8000;  
  
\---创建外键  
alter table emp add constraint fk\_emp\_deptno foreign key(deptno) references dept(deptno);  
  
  
\---删除name重复数据  
create table dupes(id integer,name varchar(10));  
insert into dupes VALUES (1,'AAA');  
insert into dupes VALUES (2,'BBB');  
insert into dupes VALUES (3,'AAA');  
insert into dupes VALUES (4,'CCC');  
insert into dupes VALUES (5,'BBB');  
  
\---select \* from dupes;  
\--1.  
delete from dupes a where exists (select 1 from dupes b where a.name= b.name and a.id > b.id)  
  
\--2  
delete from dupes a where exists (select 1 from dupes b where a.name= b.name and a.rowid > b.rowid)  
  
\--3.  
select rowid as rid ,name ,row\_number() over (partition by name order by id ) as seq from dupes order by 2,3;  
  
delete from dupes a where rowid in (  
select rid from(  
select rowid as rid ,name ,row\_number() over (partition by name order by id ) as seq from dupes order by 2,3  
)  
where seq > 1  
);  
  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
38  
39  
40  
41  
42  
43  
44  
45  
46  
47  
48  
49  
50  
51  
52  
53  
54  
55  
56  
57  
58  
59  
60  
61  
62  
63  
64  
65  
66  
67  
68  
69  
70  
71  
72  
73  
74  
75  
76  
77  
78  
79  
80  
81  
82  
83  
84  
85  
86  
87  
88  
89  
90  
91  
92  
93  
94  
95  
96  
97  
98  
99  
100  
101  
102  
103  
104  
  
第5章：使用字符串  
  
create or replace view v as select '天天向上' as zh,'TTXS' as en from dual;  
  
\--level connect by  
select level from dual connect by level <= 4;  
  
\--拆分字符串  
/\*  
\* substr(src,index,length) ;;  
\* 第二个参数标识开始位置(oracle index从1开始)  
\* 第三个参数表示长度  
\*/  
select zh,en,level,substr(zh,level,1) z ,substr(en,level,1) from v connect by level <= length(zh);  
  
\--2018-01-16补充 --截取最右端n个字符  
select substr( 'abcdefg',-4) from dual; //defg  
  
\--字符串中包含引号：： q-quote：可以用<>,\{\},(),\[\] 必须成对出现  
select 'g''day mater' from dual  
unionr  
  
select q'\[hahah'hahah\]' from dual  
union  
select q'\{xixi'xiixi\}' from dual  
union  
select q'(ooo'oo'')' from dual;  
  
  
create or replace view v as select'clark,king,miller' as str from dual;  
/\*\*  
regexp\_replace(1,2,3,4,5,6)  
语法说明：1：字段 2：替换的字段 3：替换成什么 4：起始位置（默认从1开始） 5：替换的次数（0是无限次） 6：不区分大小写  
\*\*/  
\--计算字符出现的次数;  
select regexp\_count(str,',') from v; --regexp\_count  
select length(regexp\_replace(str,'\[^,\]')) from v; --regexp\_replace  
select length(translate(str,','||str,',')) from v; --translate  
  
create or replace view v as select'clark$\#king$\#miller' as str from dual;  
\--计算字符出现的次数;---多个分隔符  
select regexp\_count(str,'\\$\#') from v;  
select length(regexp\_replace(str,'\[^\\$\#\]'))/length('$\#') from v;  
select length(translate(str,'$\#'||str,'$\#'))/length('$\#') from v;  
  
\---删除不需要的字符  
select ename,regexp\_replace(ename,'\[AEIOU\]')from emp where deptno='10';  
SELECT ENAME,TRANSLATE(ename,'1AEIOU','1') FROM emp where deptno='10';  
  
create or replace view v as select dname || deptno as data from dept;  
\--将数字和字符分离  
select data,regexp\_replace(data,'\[0-9\]') as code ,regexp\_replace(data,'\[^0-9\]') as num from v;  
select data,translate(data,'a0123456789' ,'a') as code ,translate(data,'0123456789'||data ,'0123456789') as num from v;  
  
  
\--查询只包含数字或者只包含字母的记录  
select \* from v where regexp\_like(data,'^\[0-9a-z-A-Z\]+$');  
  
  
  
\---listagg  
select deptno,sum(sal) as total\_sal ,listagg(ename,',') within group (order by ename) as total\_name from emp group by deptno  
  
create or replace view v as select deptno,sum(sal) as total\_sal ,listagg(ename,',') within group (order by ename) as total\_name from emp where deptno in(10,20) group by deptno  
\---regexp\_substr ::提取第n个子串  
/\*\*  
function REGEXP\_SUBSTR(String, pattern, position, occurrence, modifier)  
\_\_srcstr ：需要进行正则处理的字符串  
\_\_pattern ：进行匹配的正则表达式  
\_\_position ：起始位置，从第几个字符开始正则表达式匹配（默认为1）  
\_\_occurrence ：标识第几个匹配组，默认为1  
\_\_modifier ：模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）  
\*\*/  
select \* from v;  
select total\_name,regexp\_substr(total\_name,'\[^,\]+',1,2) from v;  
  
  
\--分割ip （val1,val2,val3...） ---多个字段columns  
create or replace view v as select '192.168.1.75' as ip from dual;  
select ip,  
regexp\_substr(ip,'\[^.\]+',1,1),  
regexp\_substr(ip,'\[^.\]+',1,2),  
regexp\_substr(ip,'\[^.\]+',1,3),  
regexp\_substr(ip,'\[^.\]+',1,4)  
from v;  
  
\--将分割数据转换为多值in列表:: (val1）, (val2）.... ---多条记录rows  
create or replace view v as select 'CLCK,KING,MILLER' as names from dual;  
select names,  
level,  
regexp\_substr(names,'\[^,\]+',1,level)  
from v  
connect by level <= length(translate(names,','||names,','))+1;--也可以使用regexp\_count(names,',') +1代替  
  
\--按照字母顺序排序字符串  
create or replace view v as select 'ADAMS' as words from dual;  
select words,level,substr(words,level,1) ch from v connect by level <= length(words); -- 拆分成字符  
  
select t.words ,listagg(t.ch) within group (order by t.ch) from (  
select words,level,substr(words,level,1) ch from v connect by level <= length(words)  
) t group by t.words ;  
  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
38  
39  
40  
41  
42  
43  
44  
45  
46  
47  
48  
49  
50  
51  
52  
53  
54  
55  
56  
57  
58  
59  
60  
61  
62  
63  
64  
65  
66  
67  
68  
69  
70  
71  
72  
73  
74  
75  
76  
77  
78  
79  
80  
81  
82  
83  
84  
85  
86  
87  
88  
89  
90  
91  
92  
93  
94  
95  
96  
97  
98  
99  
100  
  
第6章：使用数字  
  
\---常用聚集函数  
select deptno ,sum(sal), max(sal),min(sal),count(1),avg(sal),avg(coalesce(sal,0)),listagg(ename,',') within group (order by ename)  
from emp  
group by deptno;  
  
\---生成累计之和 sum(xx) over(order by xxx);  
select empno,ename,sal, sum(sal) over(order by empno)  
from emp  
order by empno;  
  
\---生成累计之和 + 计算公式  
select a.empno,a.ename,a.sal, sum(a.sal) over(order by a.empno),  
(select listagg(b.sal,'+') within group(order by b.empno) from emp b where b.empno <= a.empno)  
from emp a  
order by a.empno;  
  
  
\---计算累计之差: sum() over (\[partition by \] order by )  
drop table detail ;  
create table detail as  
select 1000 as d\_id,'预交费用' as prj ,30000 as amount from dual;  
insert into detail select empno,'支出' || rownum ,sal from emp where deptno=10;  
  
select seq,d\_id,prj,amount,amount\_declare,sum(amount\_declare) over (order by seq) from (  
select seq,d\_id,prj,amount,(case when seq = 1 then amount else -amount end) amount\_declare from (  
select rownum as seq ,t.\* from (  
select a.d\_id,a.prj,a.amount from detail a order by 1  
) t  
)  
);  
  
  
\---更改后累计值(银行交易流水)  
create or replace view V(id,atm,trx)  
as  
select 1,100,'PR'from dual  
union  
select 2,100,'PR'from dual  
union  
select 3,50,'PY'from dual  
union  
select 4,100,'PR'from dual  
union  
select 5,200,'PY'from dual  
union  
select 6,50,'PY'from dual  
;  
  
select v.id,  
v.trx,  
decode(v.trx, 'PR', '存款', 'PY', '取款', '') AS trx\_name,  
v.atm,  
decode(v.trx, 'PR', v.atm, 'PY', -v.atm, -99999) as atm\_real,  
sum( decode(v.trx, 'PR', v.atm, 'PY', -v.atm, -99999)) over (order by v.id) total  
from V v;  
  
  
\---返回各个部门工资前三位的员工 ,row\_number(),rank(),dense\_rank()  
select deptno,empno,sal,  
row\_number() over (partition by deptno order by sal desc) as row\_number, --顺序生成 1,2,3，  
rank() over (partition by deptno order by sal desc) as rank, --当存在相同的sal时， 1,1,3  
dense\_rank() over (partition by deptno order by sal desc) as dense\_rank--当出现相同的sal时， 1,1,2  
from emp  
order by 1,3 desc  
  
  
\----返回最值所在行的数据：：max() ,first,last  
\--------a  
select deptno,sal,max\_sal,rk from (  
select deptno,ename,sal,max(sal) over( partition by deptno) max\_sal,rank() over(partition by deptno order by sal desc) rk from emp  
) where rk =1  
  
\--------b  
select deptno,ename,sal,  
max(ename) keep(dense\_rank first order by sal asc ) over( partition by deptno) min\_name,  
max(ename) keep(dense\_rank last order by sal asc ) over( partition by deptno) max\_name  
from emp  
order by 1,5;  
  
\---------c  
select deptno,max(sal),min(sal),  
max(ename) keep(dense\_rank first order by sal asc ) min\_name,  
max(ename) keep(dense\_rank last order by sal asc ) max\_name  
from emp  
group by deptno;  
  
\----first\_value() ,last\_value()  
select deptno,ename,sal,first\_value(sal) over(partition by deptno order by sal desc)  
from emp;  
  
  
  
\-----百分比: ratio\_to\_report() over (partition by xx)  
select deptno,ename,sal,sum(sal) over (partition by deptno),ratio\_to\_report(sal) over (partition by deptno) from emp;  
  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
38  
39  
40  
41  
42  
43  
44  
45  
46  
47  
48  
49  
50  
51  
52  
53  
54  
55  
56  
57  
58  
59  
60  
61  
62  
63  
64  
65  
66  
67  
68  
69  
70  
71  
72  
73  
74  
75  
76  
77  
78  
79  
80  
81  
82  
83  
84  
85  
86  
87  
88  
89  
90  
91  
92  
93  
94  
  
第7章：日期运算  
  
\----加减年月日  
select  
hiredate,  
hiredate - 5,  
hiredate + 5,  
add\_months(hiredate, 2),  
add\_months(hiredate, -2),  
add\_months(hiredate, -2 \* 12)  
from emp;  
  
  
\-----加减时分秒  
select sysdate ,sysdate - 1/24,sysdate - 1/24/60 from dual;  
  
\----日期间间隔日月年  
select hiredate,sysdate,sysdate-hiredate ,months\_between(sysdate,hiredate) ,months\_between(sysdate,hiredate)/12 from emp;  
  
  
\----记录当前记录和下调记录相差的天数 :: lead(xx) over(order by xxx)  
select deptno,ename,hiredate,lead(hiredate) over(order by hiredate) from emp;  
  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
  
第 8 章：日期操作  
  
\----sysdate能得到的信息  
select sysdate,  
trunc(sysdate,'yyyy'), --年初  
trunc(sysdate,'mm'), --月初  
last\_day(sysdate),--月末  
add\_months(trunc(sysdate,'mm'),1),--下月初  
trunc(sysdate,'day'),--周初（周日开始）  
trunc(sysdate,'dd'),--日初  
to\_char(sysdate,'hh24'),  
to\_char(sysdate,'mi'),  
to\_char(sysdate,'ss')  
from dual;  
  
\----date '2017-12-11'  
select date '2017-11-21' from dual;  
  
\----时间间隔 interval  
select interval '2' year as "year", -- +02-00  
interval '50' month as "month", -- +04-02  
interval '99' day as "day", -- +99 00:00:00 （最大值99）  
interval '80' hour as "hour", -- +03 08:00:00  
interval '80' minute as "minute", -- +00 01:20:00  
interval '3.15' second as "second", -- +00 00:00:03.150000  
interval '2 12:30:29' day to second as "day to second", -- +02 12:30:29.000000  
interval '13-3' year to month as "year to month" -- +13-03  
from dual;  
  
\----提取时间：extract ：： 与to\_char(date,'yyyy-mm-dd hh24:mi:ss')类似， 不同的是返回的是number  
select  
extract(year from systimestamp) as "year",  
extract(month from systimestamp) as "month",  
extract(day from systimestamp) as "day",  
extract(hour from systimestamp) as "hour",  
extract(minute from systimestamp) as "minute",  
extract(second from systimestamp) as "second"  
from dual;  
  
\----ps::  
select extract(hour from sysdate) from dual; --针对date字段， 无法使用extract提取时分秒  
select to\_char(sysdate,'hh24') from dual; ---使用to\_char 可以获取date字段的 时分秒  
  
  
\----判断一年是否为闰年：：： 获取当前年份二月份的最后一天即可;  
select sysdate,  
trunc(sysdate,'yy'), --年初  
add\_months(trunc(sysdate,'yy'),1), --二月初  
last\_day(add\_months(trunc(sysdate,'yy'),1)) -- 二月末  
  
,add\_months(trunc(sysdate,'yy'),2) - 1 ---或者 三月初 - 1天  
from dual;  
  
  
\-----周的计算  
select dd,  
to\_char(dd,'d'),  
to\_char(dd,'day') ,  
next\_day(dd,1), --下一个周日， 1:周日，2:周一  
to\_char(dd,'ww'), --第n周， 每年的1-1为第一周开始，date+6 为一周结束  
to\_char(dd,'iw') --第n周， 每年的第一个周一至周日算一周，  
from (  
select trunc(sysdate,'yy') + level -1 as dd  
from dual  
connect by level <= 10  
);  
  
  
\----创建本月日历;  
select \* from (  
select to\_char(dd,'ww') as ww ,dd,to\_char(dd,'d') day from (  
select trunc(sysdate ,'mm') +level - 1 as dd from dual connect by level <= last\_day(sysdate) - trunc(sysdate ,'mm')  
)  
)  
pivot(  
max(dd) week  
for day in (1,2,3,4,5,6,7)  
)  
order by ww  
  
  
\----全年日历  
select \* from (  
select to\_char(dd,'ww') as week ,to\_char(dd,'mm') as month ,dd,to\_char(dd,'d') day from (  
select trunc(sysdate ,'yy') +level - 1 as dd from dual connect by level <= add\_months(trunc(sysdate ,'yy'),12) - trunc(sysdate ,'yy')  
)  
)  
pivot(  
max(dd)  
for day in (1,2,3,4,5,6,7)  
)  
order by week,month;  
  
  
\-----补充范围内丢失的值：： 统计1980 - 今年， 每年招聘的人数  
select t2.year ,coalesce(employee,0) from  
(select extract(year from hiredate) year,count(1) employee from emp group by extract(year from hiredate) order by 1 ) t1  
right join  
(select extract(year from sysdate) -level+1 as year from dual connect by level <= extract(year from sysdate) -1980 order by 1) t2  
on t1.year = t2.year  
order by 1  
  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
38  
39  
40  
41  
42  
43  
44  
45  
46  
47  
48  
49  
50  
51  
52  
53  
54  
55  
56  
57  
58  
59  
60  
61  
62  
63  
64  
65  
66  
67  
68  
69  
70  
71  
72  
73  
74  
75  
76  
77  
78  
79  
80  
81  
82  
83  
84  
85  
86  
87  
88  
89  
90  
91  
92  
93  
94  
95  
96  
97  
98  
99  
  
第 9 章：范围处理  
  
\----定位连续值的范围  
create or replace view v(prj\_id,start\_date,end\_date) as  
select 1,date '2005-01-01' ,date '2005-01-02' from dual union all  
select 2,date '2005-01-02' ,date '2005-01-03' from dual union all  
select 3,date '2005-01-03' ,date '2005-01-04' from dual union all  
select 4,date '2005-01-04' ,date '2005-01-05' from dual union all  
select 5,date '2005-01-06' ,date '2005-01-07' from dual union all  
select 6,date '2005-01-16' ,date '2005-01-17' from dual union all  
select 7,date '2005-01-17' ,date '2005-01-18' from dual union all  
select 8,date '2005-01-18' ,date '2005-01-19' from dual union all  
select 9,date '2005-01-19' ,date '2005-01-20' from dual union all  
select 10,date '2005-01-21' ,date '2005-01-22' from dual union all  
select 11,date '2005-01-26' ,date '2005-01-27' from dual union all  
select 12,date '2005-01-27' ,date '2005-01-28' from dual union all  
select 13,date '2005-01-28' ,date '2005-01-29' from dual union all  
select 14,date '2005-01-29' ,date '2005-01-30' from dual ;  
  
\--a.提取下一工程开始的时间  
select v.prj\_id,v.start\_date,v.end\_date,lead(v.start\_date) over(order by prj\_id) from v;  
  
\--b.连续的时间  
select \* from (  
select v.prj\_id,v.start\_date,v.end\_date,lead(v.start\_date) over(order by prj\_id) next\_start\_date from v  
) where end\_date = next\_start\_date;  
  
\--tips:::提取上一工程的结束日期  
select v.prj\_id,v.start\_date,v.end\_date,lag(v.end\_date) over(order by prj\_id) pre\_end\_date from v  
  
  
\---------查询同一组或分区中行之间的差 例:统计同一个人的登录间隔(分钟)  
create or replace view login\_log(login\_name,login\_time) as  
select 'hr',timestamp '2017-11-01 8:05:31' from dual union all  
select 'hr',timestamp '2017-11-01 8:21:02' from dual union all  
select 'hr',timestamp '2017-11-01 9:31:50' from dual union all  
select 'hr',timestamp '2017-11-01 16:57:08' from dual union all  
select 'hr',timestamp '2017-11-04 7:31:20' from dual union all  
select 'qa',timestamp '2017-10-28 6:35:20' from dual union all  
select 'qa',timestamp '2017-11-21 8:00:00' from dual union all  
select 'qa',timestamp '2017-11-30 6:51:40' from dual;  
  
\---a.根据登录人分组，获取下一登录时间  
with v2 as (  
select v.login\_name,v.login\_time,lead(v.login\_time) over(partition by v.login\_name order by v.login\_time) next\_login\_time from login\_log v order by 1,2  
)  
select v2.login\_name,v2.login\_time, (v2.next\_login\_time - v2.login\_time) from v2;  
  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
38  
39  
40  
41  
42  
43  
44  
45  
  
第 10 章：高级查找  
  
\---给结果集分页  
\--a. 先排序，外层才能使用正确的序号  
select \* from (  
select rownum as rn ,t.\* from （  
select a.\* from emp a order by a.sal asc nulls first  
) t  
where rownum <= 2\*5  
) where rn > (2-1)\*5 ; --page\_size=5, page\_no=2  
  
\----错误示例1： 排序会影响rownum  
select rownum as rn\_2 ,t.rn\_1,t.\* from （  
select a.\*,rownum as rn\_1 from emp a order by a.sal asc nulls first  
) t  
  
\---错误示例2：  
select rownum as rn ,t.\* from （  
select a.\* from emp a order by a.sal asc nulls first  
) t  
where rownum <= 2\*5 and rownum > 5 ;; --结果为空。 因为rownum是伪劣，只有数据查询出来后，rownum才会有值;  
  
  
\--b.使用row\_number() over 代替排序  
select \* from (  
select row\_number() over (order by a.sal asc nulls first) as rn ,a.\* from emp a  
) t where t.rn between 5 and 10;  
  
  
\----重新生成房间号  
create table hotel(floor\_num,roo\_num) as  
select 1,100 from dual union all  
select 1,100 from dual union all  
select 2,100 from dual union all  
select 2,100 from dual union all  
select 3,100 from dual ;  
  
\--a.分组，查询，排序  
select a.\*,row\_number() over (partition by a.floor\_num order by rowid) rn,rowid as rid from hotel a;  
  
\--b.更新 merge into  
merge into hotel t  
using (  
select a.\*,row\_number() over (partition by a.floor\_num order by rowid) rn,rowid as rid from hotel a  
) b  
on (t.rowid = b.rid)  
when matched then  
update set t.roo\_num = t.floor\_num \* 100 + b.rn;  
  
  
\----跳过表中n行  
\--a.查看偶数行  
select b.\* from(  
select rownum as rn, a.\* from emp a order by a.empno  
) b where mod(b.rn,2) = 0 ;  
  
\----排列组合去重  
drop table test purge;  
create table test (id,num1,num2,num3) as  
select 1 ,'1','2','3' from dual union all  
select 2 ,'1','3','2' from dual union all  
select 3 ,'3','2','1' from dual union all  
select 4 ,'4','2','1' from dual;  
  
\--a.列转行  
select \* from test  
unpivot(num for col in(num1,num2,num3));  
  
\--b.重新排列  
with v as  
(  
select \* from test unpivot(num for col in(num1,num2,num3))  
)  
  
select v.id,listagg(v.num,',') within group (order by v.num) as num from v v  
group by v.id;  
  
\--c.去重复  
with v as  
(  
select \* from test unpivot(num for col in(num1,num2,num3))  
)  
select id , num,max(num) over(partition by id order by id desc) max\_num from (  
select v.id,listagg(v.num,',') within group (order by v.num) as num from v v  
group by v.id  
);  
  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
38  
39  
40  
41  
42  
43  
44  
45  
46  
47  
48  
49  
50  
51  
52  
53  
54  
55  
56  
57  
58  
59  
60  
61  
62  
63  
64  
65  
66  
67  
68  
69  
70  
71  
72  
73  
74  
75  
76  
77  
78  
79  
80  
81  
82  
83  
84  
  
第 11 章：报表和数据仓库查询  
  
\----行专列  
\--a.对emp表按照job汇总,统计各个部门工资  
select job,  
sum(case deptno when 10 then sal else 0 end) as d\_10,  
sum(case deptno when 20 then sal else 0 end) as d\_20,  
sum(case deptno when 30 then sal else 0 end) as d\_30  
from emp  
group by job;  
  
  
\--使用pivot  
select \* from (select job , --该列未在 pivot中显示，所以被当作分组条件  
deptno ,  
sal  
from emp)  
pivot(  
sum(sal) as s -- sum,max等聚合函数+列别名  
for deptno in(10 as d\_10 , --等价于sum(case deptno when 10 then sal else 0 end) as d\_10 再加上上面的s,结果为：d\_10\_s  
20,  
30 as d\_30)  
)  
  
\--多个条件  
select \* from (select job ,  
deptno ,  
sal ,  
comm  
from emp)  
pivot(  
sum(sal) as s ,--s  
sum(comm) as c --c  
for deptno in(10 as d\_10 ,--生成俩列： d\_10\_s,d\_10\_c  
20,  
30 as d\_30)  
);  
  
\----列转行  
\---a.创建视图  
with v\_temp as (  
select \* from (select job , --该列未在pivot中定义，作为分组条件  
sal ,  
deptno from emp where deptno is not null)  
pivot(  
sum(sal) as s --sum ,max等聚合函数  
for deptno in (10 as d10, --相当于 sum(case deptno when 10 then sal end) as d10\_s  
20 as d20,  
30 as d30  
)  
)  
order by 1  
)  
\--select \* from v\_temp;  
  
\--使用 union all  
select job , '10' as deptno ,D10\_S as sal from v\_temp  
union  
select job , '20' as deptno ,D20\_S as sal from v\_temp  
union  
select job , '30' as deptno ,D30\_S as sal from v\_temp  
  
  
\--使用unpivot  
select \* from v\_temp  
unpivot include nulls( --- 包含空值  
sal for xx in (D10\_S,D20\_S,D30\_S) --多出sal ,和xx两列 ：： xx对应的值为D10\_S,D20\_S,D30\_S列的名字 ,sal对应的置为D10\_S列的值。  
)  
  
  
\----将结果集转换为列  
select emps from (select ename,job,to\_char(sal) sal,null as t\_cl from emp)  
unpivot include nulls(  
emps for col in (ename,job,sal,t\_cl)  
)  
  
  
\-----给数据分组(略) :::ceil()  
  
\-----对数据分组(略) :::ntile() over()  
  
\-----计算简单的小计 ::: rollup(xx) -- 统计增加合计  
\--例1.  
select deptno,sum(sal) as s\_sal from emp group by rollup(deptno);  
  
\--例2.  
select deptno ,job,sum(sal) from emp group by deptno,job;  
select deptno ,job,sum(sal) from emp group by rollup((deptno,job)); --((deptno,job)) 将两个子段合并为一个字段统计  
  
  
\------生成9x9乘法表  
\---使用pivot  
select a1,a2,a3,a4,a5,a6,a7,a8,a9 from (  
select a.lv lv\_a,b.lv lv\_b,(a.lv || 'x' || b.lv || '=' || a.lv\*b.lv) as rt from  
(select level lv from dual connect by level <= 9 ) a,  
(select level lv from dual connect by level <= 9 ) b  
where a.lv <=b.lv  
)  
pivot(  
max(rt)  
for lv\_a in( 1 as a1,  
2 as a2,  
3 as a3,  
4 as a4,  
5 as a5,  
6 as a6,  
7 as a7,  
8 as a8,  
9 as a9)  
)  
order by lv\_b;  
  
\--使用listagg -- rpad,lpad ：：左填充，右填充  
select listagg(m.rt,' ') within group (order by lv\_a) from(  
select a.lv lv\_a,b.lv lv\_b,(a.lv || 'x' || b.lv || '=' || rpad(to\_char(a.lv\*b.lv),2,' ')) as rt from  
(select level lv from dual connect by level <= 9 ) a,  
(select level lv from dual connect by level <= 9 ) b  
where a.lv <=b.lv  
) m  
group by lv\_b;  
  
\---- rpad,lpad  
/\*\*  
函数参数：lpad( string1, padded\_length, \[ pad\_string \] )  
其中  
string1：源字符串  
padded\_length:最终返回的字符串的长度，如果最终返回的字符串的长度比源字符串的小，那么此函数实际上对源串进行截断处理  
pad\_string:用于填充的字符，可以不填，默认为空字符  
\*\*/  
\--a.返回值长度大于源字符串长度，将进行填充  
select lpad('a',10,'-') from dual;-- ---------a  
select rpad('a',10,'-') from dual;-- a---------  
  
\--b.返回值长度小于源字符串长度，将进行截断  
select lpad('abcdefg',3,'-') from dual; --abc  
select rpad('abcdefg',3,'-') from dual; --abc  
  
\--c.员字符串为空，则什么也不返回  
select rpad('',2,'-') from dual; --返回空  
  
1  
2  
3  
4  
5  
6  
7  
8  
9  
10  
11  
12  
13  
14  
15  
16  
17  
18  
19  
20  
21  
22  
23  
24  
25  
26  
27  
28  
29  
30  
31  
32  
33  
34  
35  
36  
37  
38  
39  
40  
41  
42  
43  
44  
45  
46  
47  
48  
49  
50  
51  
52  
53  
54  
55  
56  
57  
58  
59  
60  
61  
62  
63  
64  
65  
66  
67  
68  
69  
70  
71  
72  
73  
74  
75  
76  
77  
78  
79  
80  
81  
82  
83  
84  
85  
86  
87  
88  
89  
90  
91  
92  
93  
94  
95  
96  
97  
98  
99  
100  
101  
102  
103  
104  
105  
106  
107  
108  
109  
110  
111  
112  
113  
114  
115  
116  
117  
118  
119  
120  
121  
122  
123  
124  
125  
126  
127  
128  
129  
130  
131  
132  
133  
134  
135  
136  
137  
  
第 12 章：分层查询  
  
\-----简单的树查询  
\--例1.  
select empno,ename,mgr from emp  
start with mgr is null --start with 查询的起点  
connect by (prior empno) = mgr -- 上一条的empno 为下一条记录的mgr. ---向下递归查员工  
  
\--例2：  
select empno,ename,mgr from emp  
start with empno = 7934 --start with 查询的起点  
connect by empno = (prior mgr) -- 上一条的mgr 为下一条记录的mgr. ---向上递归查领导  
  
\--！！！！ps:: prior 在= 左边还是右边无关，只跟 临近的字段有关系！！！！  
  
  
\-----根节点，分支节点，叶子节点 :: connect\_by\_isleaf  
select lpad('-',(level-1)\*2,'-') || empno ,  
ename,  
mgr ,  
level ,  
decode(level,1,1) root,  
decode(connect\_by\_isleaf,1,1) leaf,  
decode(level ,1,'root',decode(connect\_by\_isleaf,1,'leaf','branch')) node\_type  
from emp  
start with mgr is null  
connect by (prior empno) = mgr ;  
  
  
\------sys\_connect\_by\_path :: tree查询路径 /a/b/c  
select  
empno,  
ename,  
mgr ,  
level ,  
sys\_connect\_by\_path(ename,'/') as parent\_names,  
sys\_connect\_by\_path(empno,'/') as parent\_nos  
from emp  
start with mgr is null  
connect by (prior empno) = mgr ;  
  
  
\------tree查询中排序 :::order siblings by xx, 直接使用order by 会使得树变形  
select lpad('-',(level-1)\*2,'-') || empno ,  
ename,  
mgr  
from emp  
start with mgr is null  
connect by (prior empno) = mgr  
order siblings by empno;  
  
  
\-------tree查询中使用where  
\--a.错误范例  
select empno ,  
ename,  
mgr,  
deptno  
from emp  
where deptno=20  
start with mgr is null  
connect by (prior empno) = mgr  
order siblings by empno; ---查询的结果中 没有 mgr is null的数据， 结果明显不对！  
  
\--b.正确示例  
select empno ,  
ename,  
mgr,  
deptno  
from (select \* from emp where deptno = 20)  
start with mgr is null  
connect by (prior empno) = mgr ---结果为空，correct  
  
\--c.结论：：： tree查询时， 一定要先过滤where,然后嵌套子查询进行tree查询.  
  
  
  
\-------tree查询某一分支  
select empno ,  
ename,  
mgr  
from emp  
start with empno = 7698 --start with指定某一分支  
connect by (prior empno) = mgr  
  
\-------tree查询 减去一个分支  
select empno ,  
ename,  
mgr  
from emp  
start with mgr is null  
connect by (prior empno) = mgr  
and empno != 7698 --and 剔除某一分支  
  
  
\------list内值去重复  
create or replace view v as  
select 1 as id,'ab,b,c,ab,d,e' as a1 from dual  
union all  
select 2 as id,'11,2,3,4,11,2' as a1 from dual;  
  
\-----nocycle , (prior dbms\_random.value()) is not null --不明白！ sos  
select id,a1,level,regexp\_substr(a1,'\[^,\]+',1,level) a2 from v  
connect by nocycle (level <= regexp\_count(a1,',')+1 and (prior id)= id and (prior dbms\_random.value()) is not null) ;  
  
\--最终结果：：：  
select id,a1,listagg(a2,',') within group(order by a2) from (  
select distinct id,a1,a2 from(  
select id,a1,level,regexp\_substr(a1,'\[^,\]+',1,level) a2 from v  
connect by nocycle (level <= regexp\_count(a1,',')+1 and (prior id)= id and (prior dbms\_random.value()) is not null)  
) order by id ,a2  
) group by id,a1