--通用函數

1、userenv('language') 查看當前客戶端使用的字符集

SELECT USERENV('LANGUAGE') FROM DUAL;

2、GREATEST(參1,參2,參3。。。。) 多個參數中的最大值

3、least(參1,參2,。。。。) 多個參數中的最小值

注：參數的類型必須一致

如果參數中有null 那么最后的結果就是空(數值比較null最大)

SELECT GREATEST('A','B','C'),GREATEST(1,2,3,4),

GREATEST(DATE'2008-1-1',DATE'2012-12-20',SYSDATE),

GREATEST(1,22,44,NULL) FROM DUAL;

SELECT LEAST('A','B','C'),LEAST(1,2,3,4),

LEAST(DATE'2008-1-1',DATE'2012-12-20',SYSDATE),

LEAST(1,22,44,NULL) FROM DUAL;

4、coalesce(參1,參2,參3。。。) 多個參數中第一個不為空的值

注：參數的類型必須一致

SELECT COALESCE(NULL,1,2,3), COALESCE(NULL,NULL,'A','B','C'),

COALESCE(NULL,DATE'2020-1-23',SYSDATE) FROM DUAL;

--查詢每個人的姓名和月薪（月薪=工資+提成）

SELECT ENAME,SAL,COMM,SAL+COALESCE(COMM,0) 月薪 FROM EMP;

5、nvl(參1,參2) 參1為空 返回參2 否則返回參1

注：參數類型必須是一致的

SELECT ENAME,SAL,COMM,SAL+NVL(COMM,0) FROM EMP;

--查詢員工姓名和經理編號 如果經理編號為空 返回大哥

SELECT ENAME,MGR,NVL(TO_CHAR(MGR),'大哥') FROM EMP;

6、nvl2(參1,參2,參3) 參1為空返回參3 否則返回參2

--如果經理編號為空 返回老大 否則返回小弟

SELECT EMPNO,MGR,NVL2(MGR,'小弟','老大') FROM EMP;

--如果comm列為空或者為0 返回無獎金 否則返回有獎金

SELECT ENAME,COMM,NVL2(REPLACE(COMM,'0'),'有獎金','無獎金') 獎金 FROM EMP;

7、distinct 去重

注：多列去重時 只有完全一致才會去重

SELECT DISTINCT DEPTNO FROM EMP;

SELECT DISTINCT DEPTNO,JOB FROM EMP;

8、CASE WHEN

(一) CASE 列 WHEN 列中值1 THEN 值1

WHEN 列中值2 THEN 值2

WHEN 3 THEN 值3

............

[ELSE 值n]

END

--如果部門是10 顯示十部門 20 顯示20部門 30 顯示三十部門

SELECT DEPTNO,CASE DEPTNO WHEN 10 THEN '十部門'

WHEN 20 THEN '二十部門'

ELSE '三十部門'

END 中文部門

FROM EMP;

--查詢員工表中的職位和對應的中文含義

SELECT JOB,CASE JOB WHEN 'CLERK' THEN '職員'

WHEN 'SALESMAN' THEN '銷售'

WHEN 'MANAGER' THEN '經理'

WHEN 'ANALYST' THEN '分析員'

ELSE '懂事長'

END 中文職位

FROM EMP;

(二) CASE WHEN 條件1 THEN 值1

WHEN 條件2 THEN 值2

WHEN 條件3 THEN 值3

[ELSE 值n]

end

--如果部門是10 顯示十部門 20 顯示20部門 30 顯示三十部門

SELECT DEPTNO,CASE WHEN DEPTNO=10 THEN '十部門'

WHEN DEPTNO=20 THEN '二十部門'

ELSE '三十部門'

END 中文部門

FROM EMP;

----查詢員工表中的職位和對應的中文含義

/*如果工資大于3000 返回一級 大于2000 返回二級 大于1000 返回三級 否則 返回窮鬼 */

SELECT SAL,CASE WHEN SAL>3000 THEN '一級'

WHEN SAL>2000 THEN '二級'

WHEN SAL>1000 THEN '三級'

ELSE '窮鬼'

end 等級

FROM EMP;

/如果入職日期在1981年之前 返回部門編號 在1981~1982年 返回入職的年份如果在1982年之后 返回入職的月份/

SELECT HIREDATE,CASE WHEN TO_CHAR(HIREDATE,'YYYY')

WHEN TO_CHAR(HIREDATE,'YYYY') IN(1981,1982) THEN

TO_CHAR(HIREDATE,'YYYY')

ELSE TO_CHAR(HIREDATE,'MM')

END AAAAA

FROM EMP;

9、DECODE(列,列中值1,值1,列中值2,值2,列中值3,值3。。。。[值N])

注：等價于case when的第一種語法

SELECT DEPTNO,DECODE(DEPTNO,10,'十部門',20,'二十部門','三十部門') 中文部門

FROM EMP;

--員工表中職位和中文職位

SELECT JOB,DECODE(JOB,'CLERK','職員','SALESMAN','銷售','MANAGER','經理',

'ANALYST','分析員','董事長') 中文職位

FROM EMP;

10、nullif(參1,參2) 判斷兩個參數是否相等 相等返回空 不等返回參數1

兩個參數類型必須一致 參數1不能為空

sql中有除法運算時 為了避免除數為0報錯 可以使用這個函數

NULLIF(除數,0)

--查詢工資是提成的多少倍

SELECT ENAME,SAL,COMM,SAL/NULLIF(COMM,0) 倍數 FROM EMP;

--聚合函數（分組函數）

注：空值不會參與計算

1、min()

2、max()

3、avg() --只能是數值

4、sum() --只能是數值

5、count()

SELECT MIN(SAL),MAX(SAL),AVG(SAL),SUM(SAL),COUNT(COMM),COUNT(SAL)

FROM EMP; --把整張表看成一個小組

SELECT COUNT(1),COUNT(*),COUNT(COMM)

FROM EMP;

COUNT(1) COUNT() COUNT(列)的區別？count(列) 不會統計列中的空值count（1） count() 統計小組中有多少條數據 速度：count(主鍵/索引列)>count(*)>count(常數)>count(列)

--查詢最大和最小的員工姓名 最大最小的入職日期 平均工資 有獎金的人數

SELECT MAX(ENAME),MIN(ENAME),MAX(HIREDATE),MIN(HIREDATE),

AVG(SAL),COUNT(COMM)

FROM EMP;

分組 GROUP BY 列

--查詢每個部門的人數和平均工資

SELECT DEPTNO,COUNT(1),AVG(SAL) FROM EMP

GROUP BY DEPTNO;

--查詢每種職位的人數 平均工資 最高工資 總工資

SELECT JOB,COUNT(1),AVG(SAL),MAX(SAL),SUM(SAL) FROM EMP

GROUP BY JOB;

--查詢每個經理的下屬人數 有獎金的人數 最低工資

SELECT COUNT(1),COUNT(COMM),MIN(SAL) FROM EMP

GROUP BY MGR;

--查詢每個部門每種職位最早 最晚的入職日期

SELECT DEPTNO,JOB,MIN(HIREDATE),MAX(HIREDATE) FROM EMP

GROUP BY DEPTNO,JOB;

分組之后的篩選 having

--查詢每個職位的人數 只顯示人數大于3的

SELECT JOB,COUNT(1) FROM EMP

GROUP BY JOB

HAVING COUNT(1)>3;

--查詢部門和部門的最高工資 只顯示最高工資大于2900的部門

SELECT DEPTNO,MAX(SAL) FROM EMP

GROUP BY DEPTNO

HAVING MAX(SAL)>2900;

--查詢最早的入職日期在1981-6-1之前的職位

SELECT JOB,MIN(HIREDATE) FROM EMP

GROUP BY JOB

HAVING MIN(HIREDATE)<DATE'1981-6-1';

--查詢每個經理的下屬的平均工資 只顯示平均工資在1000~2000的經理

SELECT MGR,AVG(SAL) FROM EMP

GROUP BY MGR

HAVING AVG(SAL) BETWEEN 1000 AND 2000;

排序 ORDER BY 列 ASC|DESC

注：order by后面可以加多個列 當前面的列值相等時按照后面的列排序

null last 將null 值放在最后

null first 將null值放在前面

SELECT * FROM EMP

ORDER BY SAL,ENAME DESC;

SELECT * FROM EMP

ORDER BY 6 DESC;

--數字表示select后面的第幾個列

--查詢每個部門的平均工資 按照部門升序

SELECT DEPTNO,AVG(SAL) FROM EMP

GROUP BY DEPTNO

ORDER BY 1;

where 分組前的篩選->分組->having 分組后的篩選

--查詢每個部門中名字包含A的員工人數 只顯示人數小于3的

SELECT DEPTNO,COUNT(1) FROM EMP

WHERE ENAME LIKE'%A%'

GROUP BY DEPTNO

HAVING COUNT(1)<3;

--統計員工表 每個職位上半年入職的人數 只顯示人數小于3的

SELECT JOB,COUNT(1) FROM EMP

WHERE TO_CHAR(HIREDATE,'MM')<7

GROUP BY JOB

HAVING COUNT(1)<3;

--查詢工資小于3000的員工每年入職的平均工資 顯示平均工資在1000~3000 按入職年

--份升序

SELECT TO_CHAR(HIREDATE,'YYYY'),AVG(SAL) FROM EMP

WHERE SAL<3000

GROUP BY TO_CHAR(HIREDATE,'YYYY')

HAVING AVG(SAL) BETWEEN 1000 AND 3000

ORDER BY 1;

6、wm_concat(列) 分組連接字符串

只能使用逗號分隔 不能排序

--查詢每個部門的員工姓名

SELECT DEPTNO,WM_CONCAT(ENAME) FROM EMP

GROUP BY DEPTNO;

7、listagg(列,'分隔符')within group(order by 列) 分組連接字符串

可以執行分隔符 還可以執行排序的列

SELECT DEPTNO,LISTAGG(ENAME,'/')WITHIN GROUP(ORDER BY SAL DESC)

FROM EMP GROUP BY DEPTNO;

NULL的小結：

null 不參與統計

空不占存儲 空不等于0 不等于空格 也不完全等價于 '' --空字符串

對一行值進行計算 單行函數/四則運算 空和任何值最后的結果都是空

對一列值值進行計算 聚合函數/分析函數 空不參與運算

可以使用NVL/NVL2/COALESCE對空進行處理

--執行順序

SELECT 5

FROM 1

WHERE 2

GROUP BY 3

HAVING 4

ORDER BY 6

WHERE和HAVING的區別？

1、where是對分組前的數據做篩選

having是對分組后的數據做篩選

2、where后面不能加聚合函數

having后面可以加聚合函數

--分析函數（開窗函數）

分析函數和聚合函數的區別？

聚合函數一個組只能看到一個聚合后的結果

分析函數既可以看到聚合后的結果 還能看到組內的詳細信息

語法：

函數名([列])over([partition by 列])

1、min()

2、max()

3、avg() --數值

4、sum() --數值

5、count()

--查詢每個部門的最低工資

SELECT DEPTNO,MIN(SAL)

FROM EMP

GROUP BY DEPTNO;

SELECT E.*,MIN(SAL)OVER(PARTITION BY DEPTNO) AAAA

FROM EMP E;

--查詢每個職位的平均工資 全表有提成的人數

SELECT ENAME,SAL,JOB,AVG(SAL)OVER(PARTITION BY JOB) 職位平均工資,

COUNT(COMM)OVER() 全表有提成人數

FROM EMP;

--查詢每個人的姓名 部門編號 經理編號 部門最低工資 經理下屬人數 全表最高工資

SELECT ENAME,DEPTNO,MGR,MIN(SAL)OVER(PARTITION BY DEPTNO) 部門最低,

COUNT(1)OVER(PARTITION BY MGR) 經理下屬數,

MAX(SAL)OVER() 全表最高

FROM EMP;

--查詢全表除10部門外 多少人有提成

SELECT COUNT(COMM)OVER()

FROM EMP

WHERE DEPTNO!=10;

--如果要求累計值 只要在over中加上order by即可

-按值累計

--求平均工資 工資總和

SELECT SAL,AVG(SAL)OVER(ORDER BY SAL) 平均工資,

SUM(SAL)OVER(ORDER BY SAL) 工資和

FROM EMP;

-按行累計 在以上的基礎上加上

ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW

SELECT SAL,AVG(SAL)OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 平均工資,

SUM(SAL)OVER(ORDER BY SAL ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) 工資和

FROM EMP;

--按照部門分組按照名字排序 查詢名字 部門 累計總工資 累計平均工資

SELECT ENAME,DEPTNO,SAL,SUM(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL) 累計總,

AVG(SAL)OVER(PARTITION BY DEPTNO ORDER BY SAL) 累計平均

FROM EMP

ORDER BY ENAME;

6、ROW_NUMBER()OVER([PARTITION BY] ORDER BY) --必須排序

不重復 沒有并列的情況 1 2 3 4 5

7、RANK()OVER([PARTITION BY] ORDER BY) --必須排序

并列跳一級 1 2 2 4 5 5 5 8

8、DENSE_RANK()OVER([PARTITION BY] ORDER BY) --必須排序

并列不跳級 1 2 2 3 3 4 5

--查詢員工姓名 工資 工資的排名

SELECT ENAME,SAL,ROW_NUMBER()OVER(ORDER BY SAL DESC) 排名A,

rank()over(order by sal desc) 排名B,

DENSE_RANK()OVER(ORDER BY SAL DESC) 排名C

FROM EMP;

--查詢姓名 部門 工資 以及工資在每個部門內的排名

SELECT ENAME,DEPTNO,SAL,ROW_NUMBER()OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) 排名

FROM EMP;

9、LEAD(列[,數[,值]])over([PARTITION BY 列]ORDER BY 列) --必須排序

向上拉

10、LAG(列[,數[,值]])over([PARTITION BY 列]ORDER BY 列) --必須排序

向下拉

數：拉幾行 不寫默認拉1行

值：上啦或下拉之后要填充的內容 必須和列中類型一致 默認是空

--查詢每個人的工資和下一個人的工資

SELECT ENAME,SAL,LEAD(SAL)OVER(ORDER BY SAL) 下個人的工資,

LEAD(SAL,2,0)OVER(ORDER BY SAL) 下下個人工資

FROM EMP;

--按照工資降序 查詢每個人的工資比上一個人的工資少多少

SELECT ENAME,SAL,LAG(SAL)OVER(ORDER BY SAL DESC)上個人的工資,

LAG(SAL)OVER(ORDER BY SAL DESC)-SAL 少多少

FROM EMP;

11、WM_CONCAT(列)OVER() 分組連接字符串

12、LISTAGG(列,'分割符')WITHIN GROUP(ORDER BY 列)OVER() 分組連接字符串

SELECT ENAME,DEPTNO,WM_CONCAT(ENAME)OVER(PARTITION BY DEPTNO)

FROM EMP;

SELECT ENAME,DEPTNO,LISTAGG(ENAME,'/')WITHIN GROUP(ORDER BY SAL)OVER(PARTITION BY DEPTNO)

FROM EMP;

--查詢每個人的姓名 職位 以及每種職位對應的人名

SELECT ENAME,JOB,WM_CONCAT(ENAME)OVER(PARTITION BY JOB)

FROM EMP;

總結

以上是生活随笔為你收集整理的ORACLE 学习（五）的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。