Oracle高級查詢

文章目錄

概述

sql窗口修改已執行的sql

分組查詢

分組函數的概念

分組函數的語法

常用分組函數

常見分組函數的使用

avg()/sum()

min()/max()

count()

distinct 關鍵字

wm_concat()行轉列

wm_concat不同版本的區別

nvl()/nvl2()

group by

語法

使用多個列分組

過濾分組-having子句的使用以及和where的區別

having子句語法

having子句和where的區別

在分組函數中使用order by

分組函數的嵌套

group by語句的增強

理解rollup

引申 GROUPING SETS 、 CUBE 、GROUPING

GROUPING SETS

CUBE

GROUPING

多表連接

多表查詢

笛卡爾積

oracle表的四種連接方式

等值連接

不等值連接

外連接

右外連接

左外連接

自連接

自連接存在的問題和解決辦法

問題：不適合大量數據的表

解決辦法：層次查詢connetct by

層次查詢概述

基本語法:

connect by生成序列：

子查詢

子查詢概述

語法

分類

子查詢需要注意的10個問題

子查詢語法中的小括號問題

子查詢的書寫風格問題

可以使用子查詢的位置

不可以使用子查詢的位置

from后面的子查詢

主查詢和子查詢可以不是同一張表

子查詢的排序問題

主查詢和子查詢的執行順序

單行子查詢和多行子查詢

子查詢中的空值問題 null

案例

案例1

案例2

案例3

使用函數方式

使用子查詢和dual偽表

概述

高級查詢在數據庫的開發過程中應用廣泛，本博文將從分組查詢、多表查詢和子查詢三個方面介紹oracle的高級查詢，最后典型案例的應用。

sql窗口修改已執行的sql

ed表示編輯上個sql

/ 表示執行上個sql

分組查詢

分組函數的概念

分組函數作用于一組數據，并對一組數據返回一個值。

分組函數的語法

常用分組函數

avg

sum

min

max

count

wm_contact 行轉列

更多及用法請參考oracle函數

常見分組函數的使用

avg()/sum()

求出員工的平均工資和工資總和。

SQL> select avg(sal) , sum(sal) from emp ; AVG(SAL) SUM(SAL) ---------- ---------- 2073.21428 29025

1

2

3

4

5

6

min()/max()

SQL> select min(sal), max(sal) from emp; MIN(SAL) MAX(SAL) ---------- ---------- 800 5000 SQL>

1

2

3

4

5

6

7

count()

SQL> select count(1) from emp; COUNT(1) ---------- 14

1

2

3

4

5

distinct 關鍵字

SQL> select distinct(deptno) from emp; DEPTNO ------ 30 20 10

1

2

3

4

5

6

7

8

wm_concat()行轉列

SQL> select deptno 部門 ,wm_concat(ename) 部門總的員工 from emp group by deptno; 部門 部門總的員工 ---- -------------------------------------------------------------------------------- 10 CLARK,MILLER,KING 20 SMITH,FORD,ADAMS,SCOTT,JONES 30 ALLEN,JAMES,TURNER,BLAKE,MARTIN,WARD

1

2

3

4

5

6

7

8

10.2.0.4以前，wm_concat返回的是varchar2，10.2.0.5開始，是CLOB.

nvl()/nvl2()

分組函數會自動忽略空值, nvl()函數可以使分組函數不忽略空值

NVL (expr1, expr2)

【功能】若expr1為NULL，返回expr2；expr1不為NULL，返回expr1。

NVL2 (expr1, expr2, expr3)

【功能】expr1不為NULL，返回expr2；expr2為NULL，返回expr3。

expr2和expr3類型不同的話，expr3會轉換為expr2的類型

group by

語法

在select列表中所有未包含在函數中的列都應該包含在group by子句中,否則會拋出 ora-00937 not a singel-group group function。

select a, b, c ,avg(d) from table_name group by a, b ,c ;

1

2

3

包含在group by子句中的列，不必包含在select列表中。

select avg(sal) from emp group by deptno;

1

2

3

使用多個列分組

按照部門、不同的職位，統計員工的工資總和。

select deptno, job, sum(sal) from emp group by deptno, job order by deptno;

1

先按照deptno分組，再按照job分組，如果都一樣，則是同一組數據。

過濾分組-having子句的使用以及和where的區別

having子句語法

having子句和where的區別

where子句中不能使用組函數，having后可以使用；

當where和having可以通用的情況下，優先使用where，效率更高

where 先過濾后分組

having 先分組后過濾

優先使用where

舉例：

在分組函數中使用order by

select deptno , avg(sal) from emp group by deptno order by avg(sal) ;--按表達式排序

1

2

3

4

select deptno , avg(sal) 平均工資 from emp group by deptno order by 平均工資 ;--按別名排序

1

2

3

4

5

select deptno , avg(sal) 平均工資 from emp group by deptno order by 2 ; --按序號排序，表示第二列。 如果只有2列，不能出現比2大的值

1

2

3

4

分組函數的嵌套

栗子： 求部門平均工資的最大值

先求出部門的平均工資

再求出平均工資中的最大值

select max(avg(sal)) from emp group by deptno;

1

包含在group by子句中的列，不必包含在select列表中。

group by語句的增強

分析一下這個報表

第一個紅框內的是 按照部門和職位統計 工作總和 select deptno,job,sum(sal) from emp group by deptno,job order by deptno; 第二個小籃筐是 部門工資的總和 select deptno ,sum(sal) from emp group by deptno order by deptno; 第三個總計是 工資總和 select sum(sal) from emp ;

1

2

3

4

5

6

7

8

9

10

11

整合一下：

select * from ( select deptno , job, sum(sal) from emp group by deptno , job union all select deptno ,null ,sum(sal) from emp group by deptno union all select null ,null ,sum(sal) from emp ) order by deptno, job ;

1

2

3

4

5

6

我們可以通過oracle提供的rollup函數來簡化書寫的過程。

select deptno, job, sum(sal) from emp group by rollup(deptno, job);

1

可以得到同樣的結果

理解rollup

rollup官方文檔

select a, b, c, sum( d ) from t group by rollup(a, b, c);

1

2

3

等效于

select * from ( select a, b, c, sum( d ) from t group by a, b, c union all select a, b, null, sum( d ) from t group by a, b union all select a, null, null, sum( d ) from t group by a union all select null, null, null, sum( d ) from t )

1

2

3

4

5

6

7

8

9

引申 GROUPING SETS 、 CUBE 、GROUPING

數據

create table students (id number(15,0), area varchar2(10), stu_type varchar2(2), score number(20,2)); insert into students values(1, '111', 'g', 80 ); insert into students values(1, '111', 'j', 80 ); insert into students values(1, '222', 'g', 89 ); insert into students values(1, '222', 'g', 68 ); insert into students values(2, '111', 'g', 80 ); insert into students values(2, '111', 'j', 70 ); insert into students values(2, '222', 'g', 60 ); insert into students values(2, '222', 'j', 65 ); insert into students values(3, '111', 'g', 75 ); insert into students values(3, '111', 'j', 58 ); insert into students values(3, '222', 'g', 58 ); insert into students values(3, '222', 'j', 90 ); insert into students values(4, '111', 'g', 89 ); insert into students values(4, '111', 'j', 90 ); insert into students values(4, '222', 'g', 90 ); insert into students values(4, '222', 'j', 89 ); commit;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

GROUPING SETS官方文檔

select id,area,stu_type,sum(score) score from students group by grouping sets((id,area,stu_type),(id,area),id) order by id,area,stu_type;

1

2

3

4

5

理解grouping sets

select a, b, c, sum( d ) from t group by grouping sets ( a, b, c )

1

2

等效于

select * from ( select a, null, null, sum( d ) from t group by a union all select null, b, null, sum( d ) from t group by b union all select null, null, c, sum( d ) from t group by c )

1

2

3

4

5

6

7

CUBE 官方文檔

select id,area,stu_type,sum(score) score from students group by cube(id,area,stu_type) order by id,area,stu_type;

1

2

3

4

5

理解cube

select a, b, c, sum( d ) from t group by cube( a, b, c)

1

2

等效于

select a, b, c, sum( d ) from t group by grouping sets( ( a, b, c ), ( a, b ), ( a ), ( b, c ), ( b ), ( a, c ), ( c ), () )

1

2

3

4

5

6

7

GROUPING官方文檔

從上面的結果中我們很容易發現,每個統計數據所對應的行都會出現null。

如何來區分到底是根據那個字段做的匯總呢,grouping函數判斷是否合計列!

select decode(grouping(id),1,'all id',id) id, decode(grouping(area),1,'all area',to_char(area)) area, decode(grouping(stu_type),1,'all_stu_type',stu_type) stu_type, sum(score) score from students group by cube(id,area,stu_type) order by id,area,stu_type;

1

2

3

4

5

6

7

多表連接

多表查詢

笛卡爾積

列等于兩個表列數的和

行等于兩個表行數的乘積

笛卡爾積中并不是全部正確的數據，要根據連接條件進行篩選。

比如剛才的dept和emp, 滿足連接條件emp.deptno=dept.deptno才是正確的數據。

在實際運行環境下，應避免使用笛卡兒積全集。

連接條件至少有n-1個。

ORACLE表的四種連接方式

等值連接

通過兩個表具有相同意義的列，可以建立相等連接條件。

只有連接列上在兩個表中都出現且值相等的行才會出現在查詢結果中。

select a.empno, a.ename, a.sal, b.dname from emp a, dept b where a.deptno = b.deptno;

1

2

3

不等值連接

兩個表中的相關的兩列進行不等連接,

比較符號一般為>,<,…,between… and…(小值在前 大值災后)

select e.empno, e.ename, e.sal, s.grade from emp e, salgrade s where e.sal between s.losal and s.hisal;

1

2

3

外連接

對于外連接，Oracle中可以使用“(+)”來表示，還可以使用LEFT/RIGHT/FULL OUTER JOIN 。

外連接就是為了解決：通過外鏈接，把對于連接條件上不成立的記錄，仍然包含在最后的結果中.

A) 左條件(+) = 右條件;

代表除了顯示匹配相等連接條件的信息之外,還顯示右條件所在的表中無法匹配相等連接條件的信息。

此時也稱為"右外連接".另一種表示方法是:

SELECT ... FROM 表1 RIGHT OUTER JOIN 表2 ON 連接條件

1

出現在表2中的字段，如果表1不存在該值，依然輸出

B) 左條件 = 右條件(+);

代表除了顯示匹配相等連接條件的信息之外,還顯示左條件所在的表中無法匹配相等連接條件的信息。

此時也稱為"左外連接"

SELECT ... FROM 表1 LEFT OUTER JOIN 表2 ON 連接條件

1

存在表1的數據，如果表2不存在，依然輸出

數據說明：

部門表有個id為40的部門。而員工表中卻沒有deptno=40的員工。

select p.deptno 部門號, p.dname 部門名稱, count(e.empno) 部門人數 from emp e ,dept p where e.deptno (+)= p.deptno --右外連接 ， 顯示右邊的表 不能匹配的信息 注意等號兩側表的順序 group by p.deptno , p.dname order by p.deptno; select p.deptno 部門號, p.dname 部門名稱, count(e.empno) 部門人數 from emp e ,dept p where p.deptno = e.deptno (+) --左外連接 ， 顯示左邊的表 不能匹配的信息 注意等號兩側表的順序 group by p.deptno , p.dname order by p.deptno; select p.deptno 部門號, p.dname 部門名稱, count(e.empno) 部門人數 from emp e right join dept p on e.deptno = p.deptno group by p.deptno , p.dname order by p.deptno; ---不推薦這樣寫，因為dept不是主表。 select p.deptno 部門號, p.dname 部門名稱, count(e.empno) 部門人數 from dept p left join emp e on e.deptno = p.deptno group by p.deptno , p.dname order by p.deptno;

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

自連接

自連接核心：通過別名，將同一張表視為多張表 ,多表做笛卡兒相等連接。

數據說明 mgr字段

select a.empno 員工工號, a.ename 員工姓名, a.mgr 領導工號, b.empno 領導工號, b.ename 領導姓名 from emp a join emp b on a.mgr = b.empno order by a.empno;

1

2

3

4

5

6

7

8

9

自連接不適合大量數據的表：因為查詢同一個表看做多個表，他們的笛卡爾全集的記錄數至少為 行數的平方 。如果看做3個表這是立方關系。假設emp有1億條數據…做自連接的話 可想而知

oracle中的select語句可以用START WITH…CONNECT BY PRIOR子句實現遞歸查詢，connect by 是結構化查詢中用到的.

SELECT [LEVEL],column,expression, ... FROM table [WHERE conditions] [[START WITH start_condition] [CONNECT BY PRIOR prior_condition]];

1

2

3

4

5

6

7

8

level是偽列，代表樹的層級，根節點level為1，子節點為2等。

from后面只能是一個表或一個視圖。

where條件可以限制查詢返回的行，但不影響層次關系，屬于將節點截斷，但是這個被截斷的節點的下層child不受影響.

注意，徹底剪枝條件應放在connect by（connect by之后也可跟過濾條件，它將該條件節點后的所有子孫后代一并去除不顯示，如：connect by prior employee_id=manager_id and employee_id>10），單點剪掉條件應放在where子句（入層后不輸出）。因為connect by的優先級要高于where，也就是sql引擎先執行connect by。

start_condition定義層次化查詢的起點，如employee_id=1。

prior_condition定義父行和子行之間的關系，如父子關系定義為employee_id=manager_id，表示父節點employee_id和子節點manager_id之間存在關系。

如果不加PRIOR關鍵字則不會進行遞歸，只是相當于一個過濾條件，只能得到根節點。

另外，該關鍵字可放在前列前，也可放在后列前，放在哪列前哪列就是根節點

如果connect by prior中的prior被省略，則查詢將不進行深層遞歸,只能得到根節點。

select level,--是oracle中的偽列，其實在emp表中并沒有該字段 empno, ename, sal, mgr from emp start with mgr is null /**只有根節點可以用 is null 這種寫法 ，或者 empno=7839 也表示是從根節點開始 .當然也可以從任意節點開始遍歷，獲取特定子樹 .比如遍歷 JONES下面的所有子節點 empno=7566**/ connect by prior empno = mgr order by 1;--按照第一個字段排序 即按照levle排序

1

2

3

4

5

6

7

8

9

10

connect by prior empno = mgr 等號左右兩側的字段順序不要搞反了。表示父節點empno 和子節點mgr 之間存在關系。

如生成一個1到10的序列：

SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 10;

1

其原理在于：省略start with則以所以點為根節點，而dual表只有一行所有只有一個節點，而connect by則對所有輸入內容進行遍歷。

上面的方法受制于rownum偽列的限制，想得到指定始尾的序列我們也可以借助level偽列，如：select level from dual where level >= 5 connect by level <= 10;

自連接和層次查詢各有利弊，看使用場景。層次查詢：不存在多表查詢,但是查詢結果沒有自查詢直觀。

子查詢

子查詢概述

語法

分類

子查詢分為 單行子查詢 和 多行子查詢

select * from emp where sal>(select sal from emp where ename='SCOTT')

1

子查詢需要注意的10個問題

不要忘記子查詢語法中的小括號

形成良好的子查詢的書寫風格

可以使用子查詢的位置：Where,select,having,from

不可以使用子查詢的位置：group by

強調：from后面的子查詢

主查詢和子查詢可以不是一張表

一般不在自查詢中，使用排序；但是在Top-N分析問題中，必須對子查詢排序

一般先執行子查詢，再執行主查詢；但相關子查詢例外

單行子查詢只能使用單行操作符；多行子查詢只能多行操作符

注意：子查詢中是Null值的問題

子查詢語法中的小括號問題

子查詢必須有小括號，否則會拋出ora-00936 :missing expression

子查詢的書寫風格問題

注意換行和縮進

可以使用子查詢的位置

可以使用子查詢的位置：Where,select,having,from

where:

select * from emp where sal > (select sal from emp where ename = 'SCOTT');

1

2

3

4

5

select:

having:

select deptno , avg(sal) from emp group by deptno having avg(sal) > (select max(sal) from emp where deptno=30);

1

2

3

4

5

6

having不能換成where, 因為where后面不能使用分組函數。

from:

select * from ( select a.empno ,a.ename ,a.deptno from emp a);

1

2

3

4

不可以使用子查詢的位置

不可以在group by 后使用子查詢

from后面的子查詢

select * from (select empno,ename,sal from emp);

1

2

select * from (select empno,ename,sal,12*sal 年薪 from emp);

1

2

主查詢和子查詢可以不是同一張表

select * from emp where deptno=(select deptno from dept where dname='SALES');

1

2

3

4

5

當然也可以使用多表查詢的方式：

select e.* from emp e,dept d where e.deptno=d.deptno and d.dname='SALES' ;

1

2

3

理論上應該盡量使用多表查詢，因為上面的子查詢有兩個from語句，所以要對數據庫訪問查詢兩次，而下面的多表查詢只訪問了一次！這是理論上的結論，并沒有考慮實際比如多表查詢中產生的笛卡爾積的大小，具體情況還是要具體對待。

子查詢的排序問題

select rownum,empno,ename,sal from (select * from emp order by sal desc) where rownum<=3;

1

2

3

rownum 行號，oracle提供的偽列。

將排序后的表作為一個集合放到from（）中 生成一個新表

重新再查詢rownum 就可以讓rownum也實現排序了

行號需要注意的兩個問題

1、行號永遠按照默認的順序生成

2、行號只能使用<，<=；不能使用>，>=

針對1的情況 舉個栗子：

我們按照工資排序下，在看下rownum的順序

即使用order by排序，也不會打亂rownum默認生成行號的順序 。

針對2的情況 舉個栗子：

可以看到 當使用rownum >號時，獲取到的結果為空。

主查詢和子查詢的執行順序

一般先執行子查詢，再執行主查詢；但相關子查詢例外。

那什么是相關子查詢呢？

相關子查詢的典型結構如下：

select columnlist from table1 t1 where column2 in (select column3 from table2 t2 where t2.column3 = t1.column4)

1

2

3

4

也就是說

在子查詢中使用到了外查詢的表和相關的列。

這樣無法像嵌套子查詢一樣一次將子查詢的結果計算出來然后再和外查詢挨個比對，相關子查詢對于外部查詢的每一個值都會有一個結果與其對應，其計算的過程是這樣的：

1.掃描外查詢的第一條記錄

2.掃描子查詢，并將第一條記錄的對應值傳給子查詢，由此計算出子查詢的結果

3.根據子查詢的結果，返回外查詢的結果。

4.重復上述動作，開始掃描外查詢的第二條記錄，第三條記錄，直至全部掃描完畢

select empno, ename, sal, (select avg(sal) from emp where deptno = e.deptno) avgsal from emp e where sal > (select avg(sal) from emp where deptno = e.deptno);

1

2

3

4

5

6

7

單行子查詢和多行子查詢

單行子查詢：插敘結果為一個

多行查詢：查詢結果為兩個或兩個以上

單行子查詢可以使用單行操作符（也可以使用in啊）。

多行子查詢只能使用多行操作符。

單行操作符

多行子查詢

多行操作符

單行子查詢栗子

select * from emp e where e.job = (select job from emp b where b.empno=7566) and e.sal > (select sal from emp c where c.empno=7782)

1

2

3

4

select deptno, min(sal) from emp group by deptno having min(sal) > (select min(sal) from emp b where b.deptno = 20)

1

2

3

4

非法使用單行操作符

多行子查詢栗子

多行操作符 in

多行操作符 any

找出員工中，只要比部門號為30的員工中的任何一個員工的工資高的員工信息。也就是說只要比部門號為30的員工中的那個工資最少的員工的工資高就滿足條件。

any取的是集合的最小值。

select * from emp where sal > any (select sal from emp b where b.deptno = 30);

1

2

3

或者

單行操作符表示

select * from emp where sal > any (select sal from emp b where b.deptno = 30);

1

2

3

多行操作符 all

max取的是集合的最大值。

select * from emp where sal > all (select sal from emp b where b.deptno = 30);

1

2

3

4

select * from emp where sal > (select max(sal) from emp b where b.deptno = 30);

1

2

3

子查詢中的空值問題 null

單行子查詢的null問題

多行子查詢的null問題

先看下emp的數據

in相當于 =ANY

not in 相當于 <>ALL（其中如果子查詢返回值有NULL，則<>NULL當然沒有結果）

ORACLE官方文檔：

select * from emp where empno not in (select mgr from emp where mgr is not null);

1

案例

案例1

select rn , empno, ename, sal from (select rownum rn, empno, ename, sal from (select empno, ename, sal from emp order by sal desc) t1 where rownum <= 8) t2 where t2.rn >= 5;

1

2

3

4

5

案例2

最開始用的相關子查詢做的，

select empno, ename, sal, (select avg(sal) from emp where deptno = e.deptno) avgsal from emp e where sal > (select avg(sal) from emp where deptno = e.deptno);

1

2

3

4

5

6

現在用多表查詢的方式實現下

select e.empno, e.ename, e.sal, s.deptno, s.avgsal from emp e, (select deptno, avg(sal) avgsal from emp group by deptno) s where e.deptno = s.deptno and e.sal > s.avgsal

1

2

3

4

在pl/sql中，選中sql,按F5查看執行計劃

可以看到 相關子查詢的效果更好一些。

案例3

統計員工的入職年份

使用函數方式

select count(*) Total, sum(decode(to_char(hiredate, 'YYYY'), '1980', '1', '0')) "1980", sum(decode(to_char(hiredate, 'YYYY'), '1981', '1', '0')) "1981", sum(decode(to_char(hiredate, 'YYYY'), '1982', '1', '0')) "1982", sum(decode(to_char(hiredate, 'YYYY'), '1987', '1', '0')) "1987" from emp;

1

2

3

4

5

6

使用子查詢和dual偽表

select (select count(*) from emp) Total, (select count(*) from emp where to_char(hiredate, 'YYYY') = '1980') "1980", (select count(*) from emp where to_char(hiredate, 'YYYY') = '1981') "1981", (select count(*) from emp where to_char(hiredate, 'YYYY') = '1982') "1982", (select count(*) from emp where to_char(hiredate, 'YYYY') = '1987') "1987" from dual;

1

2

3

4

5

6

7

Oracle SQL

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。