Oracle查询优化改写-笔记

《Oracle查询优化改写 技巧与案例》学习笔记。写此博文，方便以后查找使用。

第1章：单表查询

--查找空值select * from emp where comm is null; /* * replace(1,2,3) * 1:字段,2:旧内容 ，3:新内容*/select replace('abcdefg','a',null) from dual;--coalesceselect 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%' ;-- _一个字符

第2章：给查询结果排序

--order by 1,2,3select 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 lastselect empno,ename,comm  from emp order by 3 nulls first ;

第3章：操作多个表

---union与orselect empno,ename from emp where empno=7788 or ename='SCOTT';select empno,ename from emp where empno=7788UNIONselect empno,ename from emp where ename='SCOTT';--创建索引create index index_emp_empno on emp(empno);create index index_emp_ename on emp(ename);--planalter 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=7788UNIONselect 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 joinexplain 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;

第4章：插入、更新、删除

--defaultcreate 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 设置 defaultinsert into test(c1,c2) values(default,default); --c3,c4 依然设置 defaultinsert 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');--正确执行，为了屏蔽程序手动输入c4select * 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;--仅复制数据1insert into test3(c1) (select ename from emp)--仅复制数据2---view;::with check optionINSERT INTO (       SELECT C1,C2,C4 FROM TEST WHERE C4 <= SYSDATE WITH CHECK OPTION --c4:必须<= sysdate)VALUES('C1','C2',SYSDATE+11); --- 提示 违反with check option规约--inset allinsert 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 bonusesmerge into bonuses a  --只能更新 bonuses using(select empno,sal from emp where empno>=7900 ) bon(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 <1000when 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)--2delete 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);

第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);--字符串中包含引号：： q-quote：可以用<>,{},(),[] 必须成对出现select 'g''day mater' from dualunionrselect q'[hahah'hahah]' from dualunionselect q'{xixi'xiixi}' from dualunion 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_countselect length(regexp_replace(str,'[^,]')) from v; --regexp_replaceselect length(translate(str,','||str,',')) from v; --translatecreate 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]+$');---listaggselect deptno,sum(sal) as total_sal ,listagg(ename,',') within group (order by ename) as total_name from emp group by deptnocreate 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...） ---多个字段columnscreate 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）.... ---多条记录rowscreate or replace view v as select  'CLCK,KING,MILLER' as names from dual;select names,       level,       regexp_substr(names,'[^,]+',1,level)from vconnect 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 ;

第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 empgroup by deptno;---生成累计之和 sum(xx) over(order by xxx);select empno,ename,sal, sum(sal) over(order by empno)from emporder 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 aorder 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) asselect 1,100,'PR'from dual unionselect 2,100,'PR'from dual unionselect 3,50,'PY'from dual unionselect 4,100,'PR'from dual unionselect 5,200,'PY'from dual unionselect 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,2from emp order by 1,3 desc----返回最值所在行的数据：：max() ,first,last--------aselect 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--------bselect 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;---------cselect 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;

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

第 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;----时间间隔  intervalselect 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-03from dual;----提取时间：extract  ：： 与to_char(date,'yyyy-mm-dd hh24:mi:ss')类似， 不同的是返回的是numberselect   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 ) t1right join(select extract(year from sysdate) -level+1 as year from dual connect by level <= extract(year from sysdate) -1980 order by 1) t2on t1.year = t2.yearorder by 1

第 9 章：范围处理

----定位连续值的范围create or replace view v(prj_id,start_date,end_date) asselect 1,date '2005-01-01' ,date '2005-01-02' from dual union allselect 2,date '2005-01-02' ,date '2005-01-03' from dual union allselect 3,date '2005-01-03' ,date '2005-01-04' from dual union allselect 4,date '2005-01-04' ,date '2005-01-05' from dual union allselect 5,date '2005-01-06' ,date '2005-01-07' from dual union allselect 6,date '2005-01-16' ,date '2005-01-17' from dual union allselect 7,date '2005-01-17' ,date '2005-01-18' from dual union allselect 8,date '2005-01-18' ,date '2005-01-19' from dual union allselect 9,date '2005-01-19' ,date '2005-01-20' from dual union allselect 10,date '2005-01-21' ,date '2005-01-22' from dual union allselect 11,date '2005-01-26' ,date '2005-01-27' from dual union allselect 12,date '2005-01-27' ,date '2005-01-28' from dual union allselect 13,date '2005-01-28' ,date '2005-01-29' from dual union allselect 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) asselect 'hr',timestamp '2017-11-01 8:05:31'  from dual union allselect 'hr',timestamp '2017-11-01 8:21:02'  from dual union allselect 'hr',timestamp '2017-11-01 9:31:50'  from dual union allselect 'hr',timestamp '2017-11-01 16:57:08'  from dual union allselect 'hr',timestamp '2017-11-04 7:31:20'  from dual union allselect 'qa',timestamp '2017-10-28 6:35:20'  from dual union allselect 'qa',timestamp '2017-11-21 8:00:00'  from dual union allselect '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;

第 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) asselect 1,100 from dual union allselect 1,100 from dual union allselect 2,100 from dual union allselect 2,100 from dual union allselect 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) bon (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) asselect 1 ,'1','2','3'  from dual union allselect 2 ,'1','3','2'  from dual union allselect 3 ,'3','2','1'  from dual union allselect 4 ,'4','2','1'  from dual;--a.列转行select * from testunpivot(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);

第 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_30from emp group by job;--使用pivotselect * 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 allselect job , '10' as deptno ,D10_S as sal from v_tempunionselect job , '20' as deptno ,D20_S as sal from v_tempunionselect job , '30' as deptno ,D30_S as sal from v_temp--使用unpivotselect * from v_tempunpivot 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乘法表---使用pivotselect 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) mgroup by lv_b;---- rpad,lpad/**函数参数：lpad( string1, padded_length, [ pad_string ] )其中string1：源字符串padded_length:最终返回的字符串的长度，如果最终返回的字符串的长度比源字符串的小，那么此函数实际上对源串进行截断处理pad_string:用于填充的字符，可以不填，默认为空字符**/--a.返回值长度大于源字符串长度，将进行填充 select lpad('a',10,'-') from dual;-- ---------aselect rpad('a',10,'-') from dual;-- a-----------b.返回值长度小于源字符串长度，将进行截断select lpad('abcdefg',3,'-') from dual; --abcselect rpad('abcdefg',3,'-') from dual; --abc--c.员字符串为空，则什么也不返回select rpad('',2,'-') from dual; --返回空

第 12 章：分层查询

-----简单的树查询 --例1.select empno,ename,mgr from empstart with mgr is null  --start with 查询的起点connect by (prior empno) = mgr -- 上一条的empno 为下一条记录的mgr. ---向下递归查员工--例2：select empno,ename,mgr from empstart 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_typefrom empstart with mgr is null connect by (prior empno) = mgr ;------sys_connect_by_path :: tree查询路径 /a/b/cselect        empno,       ename,       mgr ,       level ,       sys_connect_by_path(ename,'/') as parent_names,       sys_connect_by_path(empno,'/') as parent_nosfrom empstart with mgr is null connect by (prior empno) = mgr ;------tree查询中排序 :::order siblings by  xx, 直接使用order by 会使得树变形select lpad('-',(level-1)*2,'-') || empno ,       ename,       mgrfrom empstart with mgr is null connect by (prior empno) = mgr order siblings by empno;-------tree查询中使用where--a.错误范例select empno ,       ename,       mgr,       deptnofrom empwhere deptno=20start with mgr is null connect by (prior empno) = mgr order siblings by empno;  ---查询的结果中 没有 mgr is null的数据， 结果明显不对！--b.正确示例select empno ,       ename,       mgr,       deptnofrom  (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,       mgrfrom empstart with empno =  7698 --start with指定某一分支connect by (prior empno) = mgr -------tree查询 减去一个分支select empno ,       ename,       mgrfrom empstart with mgr is nullconnect by (prior empno) = mgr and empno != 7698  --and 剔除某一分支------list内值去重复create or replace view v asselect 1 as id,'ab,b,c,ab,d,e' as a1 from dualunion allselect 2 as id,'11,2,3,4,11,2' as a1 from dual;-----nocycle , (prior dbms_random.value()) is not null  --不明白！ sosselect id,a1,level,regexp_substr(a1,'[^,]+',1,level) a2 from vconnect 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