一基礎單表查詢
1.1查詢表結構
desc 表名
SQL> desc emp
Name Null? Type
EMPNO NOT NULL NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)
DEPTNO NUMBER(2)
1.2查找空值
使用 is null
SQL> select empno from emp where comm is null;
8 rows selected.
1.3 将空值轉換成實際值，推薦使用coalesce
SQL> select empno,nvl(comm,0) from emp where comm is null;
SQL> select empno,nvl2(comm,comm,0) from emp where comm is null;
SQL> select empno,nullif(0,comm) from emp where comm is null;
SQL> select empno,coalesce(comm,0) from emp where comm is null;
NVL(expr1,expr2)
如果expr1和expr2的資料類型一緻，則：
如果expr1為空(null),那麼顯示expr2，
如果expr1的值不為空，則顯示expr1。
NVL2(expr1,expr2, expr3)
如果expr1不為NULL，傳回expr2； expr1為NULL，傳回expr3。
expr2和expr3類型不同的話，expr3會轉換為expr2的類型，轉換不了，則報錯。
NULLIF(expr1,expr2)
如果expr1和expr2相等則傳回空(NULL)，否則傳回expr1。
coalesce(expr1, expr2, expr3….. exprn)
傳回表達式中第一個非空表達式，如果都為空則傳回空值。
所有表達式必須是相同類型，或者可以隐式轉換為相同的類型，否則報錯。
Coalese函數和NVL函數功能類似，隻不過選項更多。
1.4 在SELECT語句中使用條件邏輯
SQL> select empno,
2 ename,
3 sal,
4 case
5 when sal<=2000 then '過低'
6 when sal>=4000 then '過高'
7 else 'OK'
8 end as status
9 from emp
10 where deptno=10;
1.5限制傳回行數
SQL> select empno from emp where rownum<=2;
1.6從表中随機傳回n條記錄
SQL> select empno,ename from (select empno,ename from emp order by dbms_random.value()) where rownum<=3;
1.7 TRANSLATE替換
2.SQL> select TRANSLATE('ab 你好 abcdef','abcdef','123456') as newstring from dual;
12 你好 123456
SQL> select TRANSLATE('ab 你好 abcdef','abcdef','1234') as newstring from dual;
12 你好 1234
SQL> select TRANSLATE('ab 你好 abcdef','acdef','1234') as newstring from dual;
1b 你好 1b234
SQL> select TRANSLATE('ab 你好 abcdef','acdef','') as newstring from dual;
替換值為空，傳回空
SQL> select TRANSLATE('ab 你好 abcdef','1abcdef','1') as newstring from dual;
你好
替換wei位置沒有字元則删除
1.8 混合字元串按字母排序
SQL> set line 100
SQL> col TRANSLATE(EMPNO||''||ENAME,'-1234567890','-') format A40
SQL> select empno||' '||ename as data,translate(empno||' '||ename,'- 1234567890','-') from emp e order by 2 ;
7499 ALLEN ALLEN
7698 BLAKE BLAKE
7782 CLARK CLARK
7902 FORD FORD
7900 JAMES JAMES
7566 JONES JONES
7839 KING KING
7654 MARTIN MARTIN
7934 MILLER MILLER
7369 SMITH SMITH
7844 TURNER TURNER
7521 WARD WARD
12 rows selected.
SQL> select empno||' '||ename as data from emp e order by translate(empno||' '||ename,'- 1234567890','-') ;
7499 ALLEN
7698 BLAKE
7782 CLARK
7902 FORD
7900 JAMES
7566 JONES
7839 KING
7654 MARTIN
7934 MILLER
7369 SMITH
7844 TURNER
7521 WARD
1.9 NULL排序使用NULLS FIRST/LAST
1.10按條件區不同列中值來排序
3 sal
4 from emp
5 where deptno=30
6 order by Case
7 when sal>=1000 and sal <2000 then
8 empno
9 else ename
10 end,
11 sal;
ename,
*
ERROR at line 2:
ORA-00932: inconsistent datatypes: expected NUMBER got CHAR
7 when sal>=1000 and sal <2000 then 1
8 else 2
9 end, 3;
6 rows selected.
二 多表操作
2.1 union all與空字元串
SQL> select 'a' as c1 from dual
2 union all
3 select '' as c1 from dual;
a
2.2 union與or
SQL> select empno,ename from emp where empno=7782 or ename='WARD';
SQL> select empno,ename from emp where empno=7782
2 union
3 select empno,ename from emp where ename='WARD';
SQL> alter session set"_b_tree_bitmap_plans"=false;
Session altered.
SQL> explain plan for select empno,ename from emp where empno=7782 or ename='WARD';
Explained.
SQL> select * from table(dbms_xplan.display);
Plan hash value: 3956160932
1 - filter("EMPNO"=7782 OR "ENAME"='WARD')
13 rows selected.
SQL> explain plan for select empno,ename from emp where empno=7782
Plan hash value: 1027572458
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Ti
me |
| 0 | SELECT STATEMENT | | 2 | 20 | 6 (34)| 00
:00:01 |
| 1 | SORT UNIQUE | | 2 | 20 | 6 (34)| 00
| 3 | TABLE ACCESS BY INDEX ROWID| EMP | 1 | 10 | 1 (0)| 00
|* 4 | INDEX UNIQUE SCAN | PK_EMP | 1 | | 0 (0)| 00
|* 5 | TABLE ACCESS FULL | EMP | 1 | 10 | 3 (0)| 00
4 - access("EMPNO"=7782)
5 - filter("ENAME"='WARD')
18 rows selected.
實際上ENAME也可以建索引那樣更快
需要注意的
SQL> select deptno from emp where EMPNO=7698 or job='SALESMAN' ORDER BY 1;
SQL> select deptno,empno from emp where EMPNO=7698 or job='SALESMAN' ORDER BY 1;
3 select deptno from emp where job='SALESMAN';
避免這樣問題出現可以用唯一列，主鍵列或rowid
SQL> select deptno,empno from emp where EMPNO=7698
3 select deptno,empno from emp where job='SALESMAN';
SQL> with
2 e as (select rownum as sn,deptno,empno,job from emp)
3 select deptno
4 from
5 (
6 select sn,deptno from e where EMPNO=7698
7 union
8 select sn,deptno from e where job='SALESMAN'
9 )
10 order by 1;
2.3 組合相關的行
SQL> select e.empno,e.ename,d.dname,d.loc
2 from emp e
3 inner join dept d on (e.deptno=d.deptno)
4 where e.deptno =10;
3 inner join dept d using(deptno)
4 where deptno =10;
2.4 IN,EXISTS和INNER JOIN
alter session set"_b_tree_bitmap_plans"=false
ERROR at line 1:
ORA-12571: TNS:packet writer failure
SQL> conn scott/tiger@clonepdb_plug
Connected.
SQL> explain plan for select empno,ename,job,deptno,sal
2 from emp
3 where (empno,ename,sal) in (select empno,ename,sal from emp )
4 ;
SQL> select * from table(dbms_xplan.display());
1 - filter("ENAME" IS NOT NULL AND "SAL" IS NOT NULL)
2 from emp a
3 where exists (select null
4 from emp b
5 where b.ename=a.ename
6 and b.job=a.job
7 and b.sal=a.sal);
Plan hash value: 977554918
1 - access("B"."ENAME"="A"."ENAME" AND "B"."JOB"="A"."JOB" AND
"B"."SAL"="A"."SAL")
16 rows selected.
Plan hash value: 3638257876
1 - access("B"."JOB"="A"."JOB" AND "B"."ENAME"="A"."ENAME" AND
SQL> explain plan for select a.empno,ename,job,sal,a.deptno
2 from emp a inner join emp b using(job,ename,sal)
3 ;
1 - access("A"."SAL"="B"."SAL" AND "A"."ENAME"="B"."ENAME" AND
"A"."JOB"="B"."JOB")
2.5 INNER JOIN、LEFT JOIN、RIGHT JOIN、FULL JOIN差別
INNER JOIN 傳回必配資料
LEFT JOIN 左表為主，右表隻傳回左表比對資料，右表沒有顯示的為空 等同于右(+)
RIGHT JOIN與上面相反等同于左(+)
FULL JOIN 左右表均傳回索引資料，比對的顯示一行
2.6 自關聯
SQL> run/
1 select a.empno as "員工編号",
2 a.ename as "員工姓名",
3 a.job as "職位",
4 b.empno as "主管編号",
5 b.ename as "主管姓名"
6 from emp a
7 left join emp b on(a.mgr=b.empno)
8* order by 1
員工編号 員工姓名 職位 主管編号 主管姓名
2.7 NOT IN、NOT EXISTS和 LEFT JOIN
SQL> select count(*) from emp where deptno =40;
SQL> select * from dept where deptno not in (select deptno from emp where deptno is null);
no rows selected
SQL> select dept.* from dept left join emp on dept.deptno=emp.deptno where emp.deptno is null;
2.8 外連接配接的條件不能亂放
SQL> select dept.* from dept left join emp on(dept.deptno=emp.deptno and emp.deptno is null);
SQL> explain plan for select dept.* from dept left join emp on(dept.deptno=emp.deptno and emp.deptno is null);
Plan hash value: 2251696546
| 0 | SELECT STATEMENT | | 3 | 69 | 6 (17)| 00
| 1 | MERGE JOIN OUTER | | 3 | 69 | 6 (17)| 00
| 2 | TABLE ACCESS BY INDEX ROWID| DEPT | 3 | 60 | 2 (0)| 00
| 3 | INDEX FULL SCAN | PK_DEPT | 3 | | 1 (0)| 00
|* 4 | SORT JOIN | | 1 | 3 | 4 (25)| 00
|* 5 | TABLE ACCESS FULL | EMP | 1 | 3 | 3 (0)| 00
4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))
filter("DEPT"."DEPTNO"="EMP"."DEPTNO"(+))
5 - filter("EMP"."DEPTNO"(+) IS NULL)
19 rows selected.
Plan hash value: 1353548327
| 0 | SELECT STATEMENT | | 1 | 23 | 6 (17)| 00
| 1 | MERGE JOIN ANTI | | 1 | 23 | 6 (17)| 00
|* 4 | SORT UNIQUE | | 12 | 36 | 4 (25)| 00
| 5 | TABLE ACCESS FULL | EMP | 12 | 36 | 3 (0)| 00
4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")
filter("DEPT"."DEPTNO"="EMP"."DEPTNO")
2.9 檢查兩個表中資料及對應資料條數是否相等
SQL> run
1 select a.empno,a.ename,b.empno,b.ename
3 full join emp b on(b.empno=a.empno)
4* where b.empno is null or b.empno is null
SQL> 4
4 where b.empno is null or b.empno is null
SQL> del
3 full join emp b on(b.empno=a.empno)
2.10多表查詢的空值處理
比ALLEN提成低的
SQL> select a.ename,a.comm
3 where coalesce(a.comm,0)<(select b.comm from emp b where b.ename='ALLEN');
ENAME COMM
SMITH
JONES
BLAKE
CLARK
KING
TURNER 0
JAMES
FORD
MILLER
9 rows selected.
第三插入、更新與删除
SQL> create table test(
2 c1 varchar2(10) default '預設1',
3 c2 varchar2(10) default '預設2',
4 c3 varchar2(10) default '預設3',
5 c4 date default sysdate
6 );
Table created.
SQL> insert into test(c1,c2,c3) values(default,null,'test');
1 row created.
SQL> select * from test
2 ;
C1 C2 C3 C4
預設1 test 2017-12-26 09:46:20
3.1阻止對某幾列插入
SQL> create or replace view v_test as select c1,c2,c3 from test;
View created.
SQL> insert into V_TEST values ('手輸1',null,'不改4');
SQL> select * from test;
手輸1 不改4 2017-12-26 09:57:36
SQL> insert into V_TEST values (default,null,'不改4');
insert into V_TEST values (default,null,'不改4')
ORA-32575: Explicit column default is not supported for modifying views
3.2複制表定義與結構
SQL> create table test1 as select * from test where 1=2;
SQL> select * from test1;
SQL> create table test2 as select * from test;
SQL> select * from test2;
3.3利用with check option限制資料輸入
SQL> alter table test modify c3 not null;
Table altered.
SQL> create or replace view v_test1 as select c1,c2,c3 from test with check option;
SQL> insert into V_TEST1 values ('test',null,null);
insert into V_TEST1 values ('test',null,null)
ORA-01400: cannot insert NULL into ("SCOTT"."TEST"."C3")
3.4多表插入語句
無條件insert
SQL> insert all
2 into test1(c1,c2,c3) values ('1','2','3')
3 into test2(c1,c2,c3) values ('1','2','3')
4 into test(c1,c2,c3) values ('1','2','3')
5 select from test1 ;
插入次數取決于select 行數
要一行的話建議用select from dual;
有條件insert
1 insert all
2 when job in ('CLERK','SALESMAN') then
3 into test (c1,c2,c3) values (ENAME,JOB,mgr)
4 when job='MANAGER' then
5 into test1 (c1,c2,c3) values (ENAME,JOB,mgr)
6 else
7 into test2 (c1,c2,c3) values (ENAME,JOB,mgr)
8 select from emp
12 rows created.
1 2 3 2017-12-26 10:29:31
SMITH CLERK 7902 2017-12-26 10:39:54
ALLEN SALESMAN 7698 2017-12-26 10:39:54
WARD SALESMAN 7698 2017-12-26 10:39:54
MARTIN SALESMAN 7698 2017-12-26 10:39:54
TURNER SALESMAN 7698 2017-12-26 10:39:54
JAMES CLERK 7698 2017-12-26 10:39:54
MILLER CLERK 7782 2017-12-26 10:39:54
10 rows selected.
11 12 13
JONES MANAGER 7839
BLAKE MANAGER 7839
CLARK MANAGER 7839
21 22 23
KING PRESIDENT
FORD ANALYST 7566
SQL> insert first
4 when empno in (7900,7934,7566) then
8 select job,ename,mgr,empno from emp;
SMITH CLERK 7902 2017-12-26 10:53:18
ALLEN SALESMAN 7698 2017-12-26 10:53:18
WARD SALESMAN 7698 2017-12-26 10:53:18
MARTIN SALESMAN 7698 2017-12-26 10:53:18
TURNER SALESMAN 7698 2017-12-26 10:53:18
JAMES CLERK 7698 2017-12-26 10:53:18
MILLER CLERK 7782 2017-12-26 10:53:18
7 rows selected.
3.5Merge into用法總結
MERGE INTO table_name alias1
USING (table|view|sub_query) alias2
ON (join condition)
WHEN MATCHED THEN
UPDATE table_name
SET col1 = col_val1,
col2 = col_val2
WHEN NOT MATCHED THEN
INSERT (column_list) VALUES (column_values);
嚴格意義上講，”在一個同時存在Insert和Update文法的Merge語句中，總共Insert/Update的記錄數，就是Using語句中alias2的記錄數”。
3.6删除重複記錄
SQL> insert into test values (1,2,3,default)
SQL> insert into test values (1,2,3,default);
1 2 3 2017-12-26 11:08:14
1 2 3 2017-12-26 11:08:18
SQL> select rowid as rid,
2 c1,
3 row_number() over(partition by c1 order by c4) as seq
4 from test
5 order by 2,3;
RID C1 SEQ
AAASXpAALAAAACuAAA 1 1
AAASXpAALAAAACuAAB 1 2
SQL> delete
2 from test
3 where rowid in (select rid
4 from (select rowid as rid,
5 row_number() over(partition by c1 order by c4) as seq
6 from test)
7 where seq>1);
1 row deleted.
2 from test a
3 where exists(select /+hash_sj/ null from test b where b.c1=a.c1 and b.rowid>a.rowid);
保留最新的<保留老的
1 2 3 2017-12-26 13:32:18
第四字元串
4.1 周遊字元串
SQL> select level from dual connect by level<=4;
SQL> select "拼音",level,substr("拼音",level,1) from (select 'TTXS' as "拼音" FROM DUAL) connect by level <=4;
拼音 LEVEL SUB
TTXS 1 T
TTXS 2 T
TTXS 3 X
TTXS 4 S
4.2 字元串'
SQL> select 'g''day mate' qmarks from dual;
g'day mate
下面是10g
SQL> select q'[g'day mate]' qmarks from dual;
SQL> select q'{g'day mate}' qmarks from dual;
SQL> select q'<g'day mate>' qmarks from dual;
SQL> select q'(g'day mate)' qmarks from dual;
4.3 統計字元串出現次數
11g
SQL> select regexp_count('wo shi wo','o') from dual;
SQL> select length(translate('wo shi wo','wo shi wo','o')) from dual;
4.4 從字元裡面删除不需要的
SQL> select ename,translate(ename,'1AEIOU','1') from emp;
ENAME TRANSLATE(ENAME,'1AEIOU','1')
SMITH SMTH
ALLEN LLN
WARD WRD
JONES JNS
MARTIN MRTN
BLAKE BLK
CLARK CLRK
KING KNG
TURNER TRNR
JAMES JMS
FORD FRD
MILLER MLLR
SQL> select ename,regexp_replace(ename,'[AEIOU]') from emp;
ENAME REGEXP_REPLACE(ENAME,'[AEIOU]'
4.5 将字母與數字分開
SQL> select dname||deptno,translate(dname||deptno,'a0123456789','a') as data,translate(DNAME||DEPTNO,'0123456789'||dname||deptno,'0123456789') as data1 from dept;
ACCOUNTING10 ACCOUNTING 10
RESEARCH20 RESEARCH 20
SALES30 SALES 30
SQL> select dname||deptno,regexp_replace(dname||deptno,'[0-9]','') as data,regexp_replace(DNAME||DEPTNO,'[^0-9]','') as data1 from dept;
4.6 ^,$.,+意義
表示自少比對6零次
4.7 姓名字母首字大寫
SQL> select regexp_replace('Michael Hartstein','([[:upper:]])(.)([[:upper:]])(.)','\1.\3') from dual;
M.H
4.8 按字元串中數字排序
SQL> select dname||deptno||loc from dept order by translate(dname||deptno||loc,'0123456789'||dname||deptno||loc,'0123456789') desc;
SALES30CHICAGO
RESEARCH20DALLAS
ACCOUNTING10NEW YORK
SQL> select dname||deptno||loc from dept order by regexp_replace(dname||deptno||loc,'[^0-9]') desc;
4.9 建立分割清單
SQL> select deptno,sal,ename from emp;
SQL> col TOTAL_SAL format 999999
SQL> col TOTAL_name format A100
SQL> select deptno,
2 sum(sal) as total_sal,
3 listagg(ename,',') within group(order by ename) as total_name
5 group by deptno;
4.10 提取第n個分割子串
1 with
2 a as
3 (
4 select listagg(ename,',') within group(order by ename) as name from emp where deptno in(10,20) group by deptno
5 )
6* select regexp_substr(a.name,'[^,]+',1,2) as "子串" from a
4.11 分解ip位址
1 select regexp_substr(v.ip,'[^.]+',1,1 ) a
2 ,regexp_substr(v.ip,'[^.]+',1,2 ) b
3 ,regexp_substr(v.ip,'[^.]+',1,3) c
4 ,regexp_substr(v.ip,'[^.]+',1,4 ) d
5* from (select '192.168.0.1' as ip from dual) v
A B C D
192 168 0 1
4.12 将分個資料轉換成多值IN
SQL> var v_emps varchar2(30);
SQL> exec :v_emps :='CLARK,KING,MILLER';
PL/SQL procedure successfully completed.
SQL> SET LINE 1000
1 SELECT FROM EMP WHERE ENAME IN
2 (
3 SELECT REGEXP_SUBSTR(:v_emps,'[^,]+',1,level) as ename from dual
4 connect by level <=(length(translate(:v_emps,','||:v_emps,','))+1)
5 )
第五 使用數字
5.1 累計和
4 sum(sal) over (order by empno)
5 from emp
6 where deptno=30
7 order by empno;
5.2 傳回各部門排行前三的員工
1 select deptno,
2 empno,
4 row_number() over (partition by deptno order by sal desc) as row_num,
5 rank() over (partition by deptno order by sal desc) as rank,
6 dense_rank() over (partition by deptno order by sal desc) as dense_rank
7 from emp
8 where deptno in (20,30)
9* order by 1,3 desc
5.3 傳回最大值所在行資料
3 max(ename) keep(dense_rank first order by sal) over (partition by deptno),
4 max(ename) keep(dense_rank last order by sal) over (partition by deptno),
5 ename,
6 sal
8 where deptno=10
9* order by 1,6 desc
3 first_value(ename) over (partition by deptno),
4 ename,
5 sal
6 from emp
7 where deptno=10
8 order by 1,5 desc;
5.4 求和百分比
3 ename,
4 sal,
5 round(ratio_to_report(sal) over(partition by deptno)100,2)
7 order by 1,2
第六 日期
6.1 年月日加減
SQL> select hiredate,
2 hiredate -5,
3 hiredate +5,
4 add_months(hiredate,-5),
5 add_months(hiredate,5),
6 add_months(hiredate,-512),
7 add_months(hiredate,512)
8 from emp
9 where rownum<=1;
HIREDATE HIREDATE-5 HIREDATE+5 ADD_MONTHS(HIREDATE ADD_MONTHS(HIREDATE ADD_MONTHS(HIREDATE ADD_MONTHS(HIREDATE
1980-12-17 00:00:00 1980-12-12 00:00:00 1980-12-22 00:00:00 1980-07-17 00:00:00 1981-05-17 00:00:00 1975-12-17 00:00:00 1985-12-17 00:00:00
6.2 時分秒加減
1 select hiredate,
2 hiredate -5/24/60/60,
3 hiredate +5/24/60/60,
4 hiredate -5/24/60,
5 hiredate +5/24/60,
6 hiredate -5/24,
7 hiredate +5/24
9* where rownum<=1
HIREDATE HIREDATE-5/24/60/60 HIREDATE+5/24/60/60 HIREDATE-5/24/60 HIREDATE+5/24/60 HIREDATE-5/24
HIREDATE+5/24
1980-12-17 00:00:00 1980-12-16 23:59:55 1980-12-17 00:00:05 1980-12-16 23:55:00 1980-12-17 00:05:00 1980-12-16 19:00:00 1980-12-17 05:00:00
6.3 時間間隔
SQL> select max(hiredate)-min(hiredate),
2 (max(hiredate)-min(hiredate))24,
3 (max(hiredate)-min(hiredate))2460,
4 (max(hiredate)-min(hiredate))246060
6 where ename in('WARD','ALLEN')
7 ;
MAX(HIREDATE)-MIN(HIREDATE) (MAX(HIREDATE)-MIN(HIREDATE))24 (MAX(HIREDATE)-MIN(HIREDATE))2460 (MAX(HIREDATE)-MIN(HIREDATE))246060
172800
6.4 日期間隔
1 select max(hiredate)-min(hiredate),
2 months_between(max(hiredate),min(hiredate)),
3 months_between(max(hiredate),min(hiredate))/12
4* from emp
MAX(HIREDATE)-MIN(HIREDATE) MONTHS_BETWEEN(MAX(HIREDATE),MIN(HIREDATE)) MONTHS_BETWEEN(MAX(HIREDATE),MIN(HIREDATE))/12
6.5 目前記錄和下一條記錄差
3 hiredate,
4 lead(hiredate) over(order by hiredate)
6* where deptno=10
4 lag(hiredate) over(order by hiredate)
+04-02
SQL> select interval '99' day as day from dual;
+99 00:00:00
SQL> select interval '80' hour as hour from dual;
+03 08:00:00
SQL> select interval '5' year as year from dual;
+05-00
6.8 extract
SQL> select extract(year from systimestamp) as year from dual;
SQL> select extract(month from systimestamp) as month from dual;
SQL> select extract(day from systimestamp) as day from dual;
SQL> select extract(hour from systimestamp) as hour from dual;
第七 報表和資料倉庫
7.1 行轉列
SQL> select job,
2 case deptno when 10 then sal end as deptno10,
3 case deptno when 20 then sal end as deptno20,
4 case deptno when 30 then sal end as deptno30,
7 order by 1;
JOB DEPTNO10 DEPTNO20 DEPTNO30 SAL
ANALYST 3000 3000
CLERK 1300 1300
CLERK 950 950
CLERK 800 800
MANAGER 2975 2975
MANAGER 2850 2850
MANAGER 2450 2450
PRESIDENT 5000 5000
SALESMAN 1500 1500
SALESMAN 1250 1250
SALESMAN 1600 1600
2 sum(case deptno when 10 then sal end) as deptno10,
3 sum(case deptno when 20 then sal end) as deptno20,
4 sum(case deptno when 30 then sal end) as deptno30,
5 sum(sal) as sal
7 group by job
8 order by 1;
CLERK 1300 800 950 3050
MANAGER 2450 2975 2850 8275
SALESMAN 5600 5600
SQL> select *
2 from (select job,
4 ,deptno
5 from emp)
6 pivot(sum(sal) as s
7 for deptno in (10 as d10,
8 20 ,
9 30 as d30)
10 )
11 order by 1;
JOB D10_S 20_S D30_S
ANALYST 3000
CLERK 1300 800 950
MANAGER 2450 2975 2850
PRESIDENT 5000
SALESMAN 5600
7.2 控制結果集重複值
SQL> select job ,ename from emp where deptno=30 order by emp.job,ename;
JOB ENAME
CLERK JAMES
MANAGER BLAKE
SALESMAN ALLEN
SALESMAN MARTIN
SALESMAN TURNER
SALESMAN WARD
SQL> select case
2 when lag(job) over(order by job,ename)=job then
3 null
4 else
5 job
6 end as job,
7 ename
9 where deptno=30
10 order by emp.job,ename;
MARTIN
TURNER
WARD
7.3 簡單小計
SQL> select deptno,sum(sal) as s_sal from emp group by rollup(deptno)
7.4 分組函數詳解
SQL> select DEPTNO,sum(sal) from emp group by deptno;
grouping值為0時說明這個值是資料庫中本來的值，為1說明是統計的結果
SQL> select DEPTNO,sum(sal) from emp group by cube(deptno) order by 1;
仔細觀察一下，CUBE與ROLLUP之間的細微差别
rollup(a,b) 統計列包含：(a,b)、(a)、()
rollup(a,b,c) 統計列包含：(a,b,c)、(a,b)、(a)、()
……以此類推ing……
cube(a,b) 統計列包含：(a,b)、(a)、(b)、()
cube(a,b,c) 統計列包含：(a,b,c)、(a,b)、(a,c)、(b,c)、(a)、(b)、(c)、()
CUBE在ROLLUP的基礎上進一步從各種次元上給出細化的統計彙總結果。
SQL> select DEPTNO,job,sum(sal) from emp group by grouping sets(deptno,job) order by 1;
Grouping_id()的傳回值事實上就是參數中的每列的grouping()值的二進制向量。假設grouping(a)=1，grouping(b)=1，則grouping_id(A,B)的傳回值就是二進制的11。轉成10進制就是3。
參數能夠是多個，但必須為group by中出現的列。
7.5 不同組進行統計
SQL> select ename,deptno,count() over(partition by deptno),job,count()over(partition by job),count(*) over()
2 from emp;
ENAME DEPTNO COUNT()OVER(PARTITIONBYDEPTNO) JOB COUNT()OVER(PARTITIONBYJOB) COUNT(*)OVER()
KING 10 3 PRESIDENT 1 12
CLARK 10 3 MANAGER 3 12
MILLER 10 3 CLERK 3 12
JONES 20 3 MANAGER 3 12
SMITH 20 3 CLERK 3 12
FORD 20 3 ANALYST 1 12
ALLEN 30 6 SALESMAN 4 12
WARD 30 6 SALESMAN 4 12
TURNER 30 6 SALESMAN 4 12
MARTIN 30 6 SALESMAN 4 12
JAMES 30 6 CLERK 3 12
BLAKE 30 6 MANAGER 3 12
7.6 移動範圍内值計算
2 sal,
3 sum(sal) over(order by hiredate range between interval '3' month preceding and current row)
6 order by 1;
HIREDATE SAL SUM(SAL)OVER(ORDERBYHIREDATERANGEBETWEENINTERVAL'3'MONTHPRECEDINGANDCURRENTROW)
1981-02-20 00:00:00 1600 1600
1981-02-22 00:00:00 1250 2850
1981-05-01 00:00:00 2850 5700
1981-09-08 00:00:00 1500 1500
1981-09-28 00:00:00 1250 2750
1981-12-03 00:00:00 950 3700
第八 分層查詢
8.1 簡單樹形結構
1 select empno,
3 mgr,
4 prior ename
6 start with empno=7566
7 connect by(prior empno)=mgr
8*
8.2 根節點，分支節點，葉子節點
1 select lpad('-',(level-1)2,'-')||empno as empno,
4 level,
5 decode(level,1,1) as root,
6 decode(connect_by_isleaf,1,1) as leaf,
7 case
8 when(connect_by_isleaf=0 and level>1) then
9 1
10 end as fenzi
11 from emp
12 start with empno=7566
13 connect by (prior empno)=mgr
EMPNO ENAME MGR LEVEL ROOT LEAF FENZI
7566 JONES 7839 1 1
--7902 FORD 7566 2 1
----7369 SMITH 7902 3 1
8.3 sys_connect_by_path ==listagg
4 sys_connect_by_path(ename,',') as enames
7* connect by (prior empno)=mgr
EMPNO ENAME MGR ENAMES
7566 JONES 7839 ,JONES
7902 FORD 7566 ,JONES,FORD
7369 SMITH 7902 ,JONES,FORD,SMITH
8.4 樹形查詢排序
SQL> select lpad('-',(level-1)*2,'-')||empno as empno,
3 mgr
5 start with empno=7839
6 connect by (prior empno)=mgr
7 order siblings by emp.empno desc;
EMPNO ENAME MGR
--7782 CLARK 7839
----7934 MILLER 7782
--7698 BLAKE 7839
----7900 JAMES 7698
----7844 TURNER 7698
----7654 MARTIN 7698
----7521 WARD 7698
----7499 ALLEN 7698
--7566 JONES 7839
----7902 FORD 7566
------7369 SMITH 7902
8.5 樹型查詢使用where
2 mgr,
4 deptno
5 from(select * from emp where deptno=20) emp
6 start with mgr is null
7 connect by(prior empno)=mgr;
8.6 查詢樹型的一個分支
4 level
6 start with empno=7698
EMPNO MGR ENAME LEVEL
7698 7839 BLAKE 1
7499 7698 ALLEN 2
7521 7698 WARD 2
7654 7698 MARTIN 2
7844 7698 TURNER 2
7900 7698 JAMES 2
8.7 減去一個分支
6 start with mgr is NULL
7839 KING 1
7566 7839 JONES 2
7902 7566 FORD 3
7369 7902 SMITH 4
7698 7839 BLAKE 2
7499 7698 ALLEN 3
7521 7698 WARD 3
7654 7698 MARTIN 3
7844 7698 TURNER 3
7900 7698 JAMES 3
7782 7839 CLARK 2
7934 7782 MILLER 3
SQL> RUN
7 connect by (prior empno)=mgr
8* and empno !=7698
第九 調優案例分享
9.1 不建議使用标量子查詢，使用left join優化标量子查詢
4 deptno,
5 (select dname from dept where dept.deptno=emp.deptno)
6 from emp;
EMPNO ENAME SAL DEPTNO (SELECTDNAMEFR
7369 SMITH 800 20 RESEARCH
7499 ALLEN 1600 30 SALES
7521 WARD 1250 30 SALES
7566 JONES 2975 20 RESEARCH
7654 MARTIN 1250 30 SALES
7698 BLAKE 2850 30 SALES
7782 CLARK 2450 10 ACCOUNTING
7839 KING 5000 10 ACCOUNTING
7844 TURNER 1500 30 SALES
7900 JAMES 950 30 SALES
7902 FORD 3000 20 RESEARCH
7934 MILLER 1300 10 ACCOUNTING
SQL> select e.empno,
2 e.ename,
3 e.sal,
4 e.deptno,
5 d.dname
6 from emp e
7 left join dept d on(e.deptno=d.deptno);
EMPNO ENAME SAL DEPTNO DNAME
1 select /+use_nl(e,d)/
7* left join dept d on(e.deptno=d.deptno)
ENAME SAL DEPTNO DNAME
SMITH 800 20 RESEARCH
ALLEN 1600 30 SALES
WARD 1250 30 SALES
JONES 2975 20 RESEARCH
MARTIN 1250 30 SALES
BLAKE 2850 30 SALES
CLARK 2450 10 ACCOUNTING
KING 5000 10 ACCOUNTING
TURNER 1500 30 SALES
JAMES 950 30 SALES
FORD 3000 20 RESEARCH
MILLER 1300 10 ACCOUNTING
9.2 使用left jion 優化标量子查聚合
SQL> select d.department_id,
2 d.department_name,
3 d.location_id,
4 nvl((select sum(e.salary)
5 from employees e
6 where e.department_id=d.department_id),
7 0) as sum_sal
8 from departments d;
DEPARTMENT_ID DEPARTMENT_NAME LOCATION_ID SUM_SAL
27 rows selected.
4 COALESCE(e.sum_sal,0) as sum_sal
5 from departments d
6 left join (select e.department_id,sum(e.salary) as sum_sal
7 from employees e
8 group by e.department_id) e on ( e.department_id=
9 d.department_id);
本文轉自whshurk 51CTO部落格，原文連結：http://blog.51cto.com/shurk/2054155，如需轉載請自行聯系原作者