首页 > 数据库 > 数据库其它

Oracle进阶(三)常用函数

admin 数据库其它 2021-04-26 18:16:18 oracle   plsql   sql   数据库  
后台-系统设置-扩展变量-手机广告位-内容正文底部

 

目录

1、聚合函数

2、转换函数

3、字符函数

4、数值函数

5、日期函数

6、判断函数

7、分析函数

附表

1、聚合函数

常用的SQL 聚合函数有5个:最大(MAX) 最小(MIN) 平均(AVG) 总和(SUM) 求数(COUNT) 

--一、聚合函数 基本语法

--MAX|MIN|AVG|SUM|COUNT (DISTINCT|ALL)

--其它聚合函数 类似,但中位数 DISTINCT会提示错误:DISTINCT 选项在此函数中禁用。
SELECT MAX(DISTINCT SAL) FROM EMP;
SELECT MAX(SAL) FROM EMP

--二、聚合函数 常见需求:

--1、查询EMP表里所有部门的名称和最高工资

SELECT E.DEPTNO,D.DNAME,MAX(E.SAL) 
FROM EMP E 
LEFT JOIN DEPT D 
ON E.DEPTNO = D.DEPTNO
GROUP BY E.DEPTNO,D.DNAME;

--2、查询EMP表里所有部门的名称和最低工资

SELECT E.DEPTNO,D.DNAME,MAX(E.SAL),MIN(E.SAL) 
FROM EMP E 
LEFT JOIN DEPT D 
ON E.DEPTNO = D.DEPTNO
GROUP BY E.DEPTNO,D.DNAME;

--3、计算EMP表里超过1人的每个部门

SELECT E.DEPTNO,COUNT(*) 
FROM EMP E
GROUP BY DEPTNO
HAVING COUNT(*) > 1;

--4、 查询EMP表里每个部门的汇总工资

SELECT E.DEPTNO,SUM(E.SAL) AS 工资总和 
FROM EMP E
GROUP BY DEPTNO
HAVING COUNT(*) > 1;

--5、查询EMP表里每个部门的平均薪酬

SELECT E.DEPTNO,AVG(E.SAL) AS 平均工资
FROM EMP E
GROUP BY DEPTNO
HAVING COUNT(*) > 1;

--其它:标准差(STDDEV) 协方差(VARIANCE) 中位数(MEDIAN) 

2、转换函数

常用的SQL 转换函数有3个:

TO_CHAR():将DATE或者NUMBER转换为字符串
TO_DATE():将NUMBER、CHAR转换为DATE
TO_NUMBER():将CHAR转换为NUMBER

--1、TO_CHAR():将DATE或者NUMBER转换为字符串

 SELECT TRUNC(TO_DATE(SYSDATE),'Y') FROM DUAL;
 SELECT TO_CHAR(DATE'2021-04-25','YYYY') FROM DUAL;  -- 年
 SELECT TO_CHAR(DATE'2021-04-25','YYYYMM') FROM DUAL; -- 年月
 SELECT TO_CHAR(DATE'2021-04-25','WW') FROM DUAL;     -- 第17周
 SELECT TO_CHAR(DATE'2021-04-25','IW') FROM DUAL;     -- 第16周(自然周)
 SELECT TO_CHAR(DATE'2021-04-25','Q') FROM DUAL;     -- 第2季度
 SELECT TO_CHAR(DATE'2021-04-25','MM') FROM DUAL;    -- 月
 SELECT TO_CHAR(DATE'2021-04-25','DD') FROM DUAL;    -- 日
 SELECT TO_CHAR(DATE'2021-04-25','D') FROM DUAL;       --本周第几天(星期天为第一天)

--2、 TO_DATE():将NUMBER、CHAR转换为DATE

--注:Date 强制转换,要有'-'才能转换,数字只会出现错误
--注:字符或数字中的年月日必须是日期范围内的值
--注:字符日期中有-或/转化为日期类型必须加-或/   

  SELECT DATE'2021-01-01' FROM DUAL;  -- 强制转换为日期格式
  SELECT TO_DATE('2021-01-01','YYYY/MM/DD') FROM DUAL;
  SELECT TO_DATE('2021-01-01','YYYY-MM-DD') FROM DUAL;
  SELECT TO_DATE('2021-01-01','YYYYMMDD') FROM DUAL;  -- 错误:无效的月份
  SELECT TO_DATE('2021-13-08','YYYY-MM-DD'),TO_DATE(20210732,'YYYY-MM-DD') FROM DUAL;-- 错误:无效的月份
  SELECT TO_DATE('2021-07-08','YYYYMMDD') FROM DUAL;-- 错误:无效的月份
  SELECT TO_DATE(20210708,'YYYY-MM-DD')  FROM DUAL;

--3、 TO_NUMBER():将CHAR转换为NUMBER

  SELECT TO_NUMBER('1 2') FROM DUAL;--错误:无效数字
  SELECT TO_NUMBER(' 12') FROM DUAL;
  SELECT TO_NUMBER('12 ') FROM DUAL;
  SELECT TO_NUMBER(' 12 ') FROM DUAL;
  SELECT TO_NUMBER('12D') FROM DUAL;--错误:无效数字

  SELECT  TO_NUMBER(TO_DATE(20210425,'YYYYMMDD')) FROM DUAL;  --错误:无效数字
  SELECT  TO_NUMBER(TO_CHAR(TO_DATE(20210425,'YYYYMMDD'),'YYYYMMDD')) FROM DUAL; 

3、字符函数

--字符函数

--1 ASCII 返回参数的十进制数字表示
SELECT ASCII('A') FROM DUAL; --65

--2 CHR 返回参数的代码所指定的字符
SELECT CHR(65) FROM DUAL;  --A

--3 CONCAT 返回arg1与arg2的拼接结果
SELECT CONCAT('ABC','DEF') FROM DUAL; --ABCDEF

--4 INITCAP 将参数arg1每个单词的首字母大写
SELECT INITCAP('yi er shan ren') FROM DUAL; --Yi Er Shan Ren

--5 INSTR(arg1,子字符串,开始检索的位置,搜索第几次出现子字符串)
--从左面第1个字符开始搜索,第一次出现ABC的位置
SELECT INSTR('HABCHABC','ABC',1,1) FROM DUAL; --2

--6 LENGTH 返回参数arg1的长度
SELECT LENGTH('abcdefg') FROM DUAL; --7

--7 lower 返回参数arg1的小写形式
SELECT LOWER('AbCdEfG') FROM DUAL; --abcdefg

--8 LPAD(arg1,n,arg3) 返回在arg1左边填充若干个arg3组成长度为n新字符串
SELECT LPAD('ABC',10,'Ha') FROM DUAL; --HaHaHaHABC

--9 LTRIM(arg1,arg2)在arg1左面删除连续出现的arg2.不指定arg2则默认删除空格
SELECT LTRIM('AAAASA','A') FROM DUAL; --SA

--10 RPAD(arg1,n,arg3) 在字符串arg1右边连续重复填充arg3直到填充后的字符串总长度到达n为止
SELECT RPAD('thr',5,'ee') FROM DUAL; --three

--11 RTRIM(arg1,arg2) 从arg1中删除右侧连续出现的arg2.不指定arg2则默认删除空格.
SELECT RTRIM('abcdabcabc','abc') FROM DUAL; --abcd

--12 REGEXP_INSTR(源字符串,正则表达式,[开始搜索的位置],[描述执行多少次替换],[描述匹配字符串的位置的整数],[允许修改正则表达式引擎的默认行为的字符串])
SELECT REGEXP_INSTR('ABC123ABC','[0-9]A') FROM DUAL; --6

--13 REGEXP_REPLACE(源字符串,正则表达式,当正则表达式匹配时所使用的替换字符串,[开始搜索的位置的整数],[描述执行多少次替换的数据],[允许修改正则表达式引擎的默认行为的字符串])
SELECT REGEXP_REPLACE('abc123abc123','[0-9]','#') FROM DUAL; --abc###abc###

--14 EGEXP_SUBSTR(源字符串,正则表达式,[开始搜索的位置],[描述执行多少次替换的数据],[允许修改正则表达式引擎的默认行为的字符串])
SELECT REGEXP_substr('abc133abc456','([0-9]{3})abc([0-9]{3})') FROM DUAL; --133abc456

--15 REPLACE(arg1,search,replace) 该函数用replace参数替换出现的所有search参数
SELECT REPLACE('abcdabce','abc','x') FROM DUAL; --xdxe

--16 SUBSTR(arg1,pos,len) 
SELECT SUBSTR('1234567890',3,5) FROM DUAL; --34567

--17 TRANSLATE(arg1,match,replace)
SELECT TRANSLATE('PI*R*2','*R','#@') FROM DUAL;  --PI#@#2

--18 TRIM([LEADING][TRAILING][BOTH] char FROM source)
--截断串首连续出现的字符1
SELECT TRIM(LEADING '1' FROM '1122332211') FROM DUAL;--22332211
SELECT TRIM(BOTH ' ' from ' ! ') as BOTH,TRIM(' ! ') as source FROM DUAL; --!  --!

4、数值函数

--数值函数

--1 ABS 绝对值 
SELECT  ABS(-2) FROM DUAL;  -- 2

--2 ROUND 四舍五入
SELECT ROUND(2.281,1) FROM DUAL; -- 2.3

--3 MOD 求余
SELECT MOD(10,3) FROM DUAL; --- 1

--4 POWER 平方 求导
SELECT POWER(2,3) AS 平方,POWER(2,-1) AS 求导 FROM DUAL;--8 --.5

--5 TRUNC 直接截取,不四舍五入
SELECT TRUNC(4.1231,2) FROM DUAL; --4.12

5、日期函数

--日期函数

--日期格式:
/*
YYYY:四位表示的年份 
YYY,YY,Y:年份的最后三位、两位或一位,缺省为当前世纪 
IYYY:ISO标准的四位年份
MM:01~12的月份编号 
MON:缩写字符集表示 
MONTH:全拼字符集表示的月份,右边用空格填补
Q:季度
W:当月第几周
WW:当年第几周 
IW:ISO标准的年中的第几周
D:当周第几天 
DD:当月第几天 
DDD:当年第几天 
DY:缩写字符集表示
DAY:全拼字符集表示的天 如(星期六)
HH,HH12:一天中的第几个小时,12进制表示法 
HH24:一天中的第几个小时,取值为00~23 
MI:一小时中的分钟 
SS:一分钟中的秒 
SSSS:从午夜开始过去的秒数 
*/
SELECT TO_CHAR(SYSDATE, 'YYYY') 年,
       TO_CHAR(SYSDATE, 'MM') 月,
       TO_CHAR(SYSDATE, 'DD') 日,
       TO_CHAR(SYSDATE, 'HH24') 时,
       TO_CHAR(SYSDATE, 'MI') 分,
       TO_CHAR(SYSDATE, 'SS') 秒,
       TO_CHAR(SYSDATE, 'DAY') 天,
       TO_CHAR(SYSDATE, 'Q') 第几季度,
       TO_CHAR(SYSDATE, 'W') 当月第几周,
       TO_CHAR(SYSDATE, 'WW') 当年第几周,
       TO_CHAR(SYSDATE, 'D') 当周第几天,
       TO_CHAR(SYSDATE, 'DDD') 当年第几天    
  FROM DUAL;
	
--注:日期可以加减数字,表示加减多少天;日期减去日期表示相隔多少天;日期不能相加
--注:MONTHS_BETWEEN的第一个日期要大于第二个日期,否则返回负数。

  SELECT DATE'2021-04-25'+2,DATE'2021-04-25'-2,DATE'2021-02-25'-DATE'2021-04-25' FROM DUAL;--2021-4-27 --2021-4-23  --59
			
--1 ADD_MONTHS(日期,月数)(n可以为负值) 增减月份
  SELECT ADD_MONTHS(SYSDATE, 1),ADD_MONTHS(DATE'2021-04-25',-2) FROM DUAL;--2021-5-25 16:17:38 --2021-2-25
    
--2 LAST_DAY(日期),返回指定日期当月的最后一天
  SELECT LAST_DAY(SYSDATE),LAST_DAY(DATE'2021-04-25') FROM DUAL;--2021-4-30 16:19:46 --2021-4-30

--3 MONTHS_BETWEEN (date1, date2),用于计算date1和date2之间有几个月
  SELECT MONTHS_BETWEEN(TO_DATE('2021-4-25', 'yyyy-mm-dd'), TO_DATE('2021-1-1', 'yyyy-mm-dd')) MON_DIFF FROM DUAL;--3.77419354838709677419354838709677419355
  
--4 FLOOR:计算两个日期间的天数
  SELECT FLOOR(TO_DATE('2021-05-03 20:00:00','yyyy-mm-dd hh24:mi:ss')-TO_DATE('2021-05-01 12:00:00','yyyy-mm-dd hh24:mi:ss')) A FROM DUAL;--2

--5 NEXT_DAY(d,string):(准确来说一个礼拜是从星期日开始到星期六结束的)
  SELECT NEXT_DAY(SYSDATE,'星期五') NEXT_DAY FROM DUAL; --2021-4-30 16:34:32

--6 EXTRACT(fmt FROM d):提取日期中的特定部分
SELECT SYSDATE ,
       EXTRACT(YEAR FROM SYSDATE) "YEAR",
       EXTRACT(MONTH FROM SYSDATE) "MONTH",
       EXTRACT(DAY FROM SYSDATE) "DAY",
       EXTRACT(HOUR FROM SYSTIMESTAMP) "HOUR",
       EXTRACT(MINUTE FROM SYSTIMESTAMP) "MINUTE",
       EXTRACT(SECOND FROM SYSTIMESTAMP) "SECOND"
  FROM DUAL;

--7 TO_TIMESTAMP(char[fmt[,'nls_param’]]):应注意char、fmt、nls_param之间的对应关系。
SELECT TO_TIMESTAMP('25-4月-21 07.46.41.000000000 上午','dd-MON-yy hh:mi:ss.ff AM') FROM DUAL;--25-4月 -21 07.46.41.000000000 上午
SELECT CAST(SYSDATE AS TIMESTAMP) DATE_TO_TIMESTAMP from dual;--25-4月 -21 04.36.04.000000 下午

6、判断函数

6.1 NVL(expr1,expr2):若expr1参数为空则显示expr2参数的值,反之则显示expr1参数本来的值

--1 NVL(expr1,expr2):若expr1参数为空则显示expr2参数的值,反之则显示expr1参数本来的值
 SELECT NVL(NULL,0) FROM DUAL;     -- 0
 SELECT NVL(NULL,100) FROM DUAL;   -- 100
 SELECT NVL('', 99) FROM DUAL;   -- 99
 SELECT NVL(10, 100) FROM DUAL;   --10
 SELECT NVL('AB',25) FROM DUAL;     -- AB

6.2 NVL2(expr1,expr2, expr3):若expr1参数为空则显示expr2的值,反之则显示expr3参数的值

--2 NVL2(expr1,expr2, expr3):若expr1参数为空则显示expr2的值,反之则显示expr3参数的值
 SELECT NVL2(NULL, 0, 1) FROM DUAL;      -- 1
 SELECT NVL2('', 99, 2) FROM DUAL;       -- 2
 SELECT NVL2(10, 3, 100) FROM DUAL;      -- 3
 SELECT NVL2('AD', 'AB', 25) FROM DUAL;  -- AB

6.3 DECODE(value,if1,then1,if2,then2,if3,then3,...,else):若value与第一个值相等,则与第一个值的匹配值,以此类推,最后都没有的,就返回默认值

--3 DECODE(value,if1,then1,if2,then2,if3,then3,...,else):若value与第一个值相等,则与第一个值的匹配值,以此类推,最后都没有的,就返回默认值。
 SELECT DECODE(12, -- value
           1, 2, --if1,then1
          12, 10 --if2,then2
					) FROM DUAL;  -- 10				
 SELECT DECODE(12, 1, 2, 13, 10) FROM DUAL;-- 空			
 SELECT DECODE(12, 1, 2, 13, 10, 
           7 --默认值
					 ) FROM DUAL; --7				 
 SELECT DECODE(12, 1, 2, 12, 10, 12, 88,7) FROM DUAL; --10

6.4 CASE WHEN 多条件判断

--4 CASE WHEN 多条件判断

--1、与DECODE函数等效,对单字段等值判断。

  SELECT (CASE DNAME
       WHEN  'MLB' THEN 'MLB部'
       WHEN  'HDI' THEN 'HDI部'
       WHEN  'POW' THEN 'POW部'
       ELSE '事业部'
       END) AS 部门
    FROM DEPT D; 

--2、可对多字段判断,而且不需要等值匹配。

  SELECT (CASE 
      WHEN SAL >=50000 THEN '富裕'
      WHEN SAL>=10000 THEN '中产'
      ELSE  '普通'
      END) AS SALGRADE 
      FROM EMP E

--*********************************************

--CASE WHEN 常见需求

--1 SELECT CASE WHEN 
--与上文类似

--2 WHERE CASE WHEN

  SELECT D.*
  FROM DEPT D
  WHERE (CASE 
	         WHEN D.DEPTNO = 'CN-HDI-1' AND D.LOC LIKE '二栋%' THEN 1
             WHEN D.DEPTNO != 'CN-HDI-1' AND D.LOC NOT LIKE '二栋%' THEN 1
             ELSE 0
         END) = 1

--3 UPDATE CASE WHEN

  UPDATE EMP SET SAL= (CASE 
	                         WHEN JOB='CLERK' THEN SAL*(1+0.1)
                             WHEN JOB='SALESMAN' THEN SAL*(1+0.05)
                             WHEN JOB='PRESIDENT' THEN SAL+2000
                             WHEN JOB='MANAGER' THEN SAL*(1+0.06)
                             ELSE SAL 
                        END)
                         WHERE DEPTNO='MLB';

--4 GROUP BY CASE WHEN

SELECT (CASE
           WHEN E.SAL <= 5000 THEN '1'  
           WHEN E.SAL > 5000 AND E.SAL <= 6000  THEN '2'  
           WHEN E.SAL > 6000 AND E.SAL <= 8000  THEN '3'  
           WHEN E.SAL > 8000 AND E.SAL <= 10000 THEN '4'  
           ELSE NULL 
        END) SAL_CLASS,  
COUNT(*)  
FROM    EMP E  
GROUP BY
(CASE
      WHEN E.SAL <= 5000 THEN '1'  
      WHEN E.SAL > 5000 AND E.SAL <= 6000  THEN '2'  
      WHEN E.SAL > 6000 AND E.SAL <= 8000  THEN '3'  
      WHEN E.SAL > 8000 AND E.SAL <= 10000 THEN '4'  
      ELSE NULL 
 END);

7、分析函数

7.1 基本语法

--分析函数 基本语法

FUNCTION_NAME(<ARGUMENT>,<ARGUMENT>...) OVER(<PARTITION_CLAUSE><ORDER BY_CLAUSE><WINDOWING_CLAUSE>);

--FUNCTION_NAME():函数名称,如COUNT(),SUM(),AVG(),MAX(),MIN()等
--ARGUMENT:参数
--OVER( ):开窗函数
--PARTITION_CLAUSE:分区(分组)子句,数据记录集分组,GROUP BY...
--ORDER BY_CLAUSE:排序子句,数据记录集排序,累计计算,ORDER BY...
--WINDOWING_CLAUSE:开窗子句,定义分析函数在操作行的集合,三种开窗方式:ROWS、RANGE、SPECIFYING

--注:使用开窗子句时一定要有排序子句

7.2 经典案例

7.2.1 函数为聚合函数:

COUNT() OVER();
SUM() OVER();
AVG () OVER();
MAX,MIN用法与AVG() OVER()相似。

--1 COUNT(<ARGUMENT>,<ARGUMENT>...) OVER(<PARTITION_CLAUSE><ORDER BY_CLAUSE>);

--1.1 总计数:统计总人数 
  SELECT ENAME,JOB,COUNT(*) OVER() FROM EMP;  
--1.2 递加计数:按SAL排序,统计总人数 
  SELECT ENAME,JOB,SAL,COUNT(*) OVER(ORDER BY SAL) FROM EMP; 
--1.3 分组计数:统计各个部门的总人数
  SELECT ENAME,JOB,SAL,DEPTNO,COUNT(*) OVER(PARTITION BY DEPTNO) FROM EMP; 
--1.4 分组递加计数:按照各个部门的薪资水平排序
  SELECT ENAME,JOB,DEPTNO,SAL,COUNT(*) OVER(PARTITION BY DEPTNO ORDER BY SAL) FROM EMP; 

--2 SUM(<ARGUMENT>,<ARGUMENT>...) OVER(<PARTITION_CLAUSE><ORDER BY_CLAUSE>);

--2.1 求出所有部门的薪资总和
  SELECT ENAME,JOB,SAL,HIREDATE,SUM(SAL) OVER() FROM EMP;
--2.2 求出各个部门薪资的累计值,按部门累计
  SELECT ENAME,JOB,SAL,HIREDATE,SUM(SAL) OVER(ORDER BY DEPTNO) FROM EMP;
--2.3 求出各自部门薪资的总和
  SELECT ENAME,JOB,DEPTNO,SAL,HIREDATE,SUM(SAL) OVER(PARTITION BY DEPTNO) FROM EMP
--2.4 求出各部门薪资累计值
  SELECT ENAME,JOB,DEPTNO,SAL,HIREDATE,SUM(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) FROM EMP;

--3 AVG (<ARGUMENT>,<ARGUMENT>...) OVER(<PARTITION_CLAUSE><ORDER BY_CLAUSE>);

--3.1 求出所有人的平均薪资
  SELECT ENAME,SAL,HIREDATE,AVG(SAL) OVER() FROM EMP;
--3.2 求出部门的累计薪资。
  SELECT ENAME,DEPTNO,SAL,HIREDATE,AVG(SAL) OVER(ORDER BY DEPTNO) FROM EMP;
--3.3 求出各个部门的平均薪资
  SELECT ENAME,DEPTNO,SAL,HIREDATE,AVG(SAL) OVER(PARTITION BY DEPTNO) FROM EMP;
--3.4 求出各个部门的平均累计薪资
  SELECT ENAME,DEPTNO,SAL,HIREDATE,AVG(SAL) OVER(PARTITION BY DEPTNO ORDER BY SAL) FROM EMP;

--4 5  MAX,MIN用法与AVG() OVER()相似。

7.2.2 函数为排序函数:
ROW_NUMBER () OVER(ORDER BY_CLAUSE);
RANK () OVER(ORDER BY_CLAUSE);
DENSE_RANK() OVER(ORDER BY_CLAUSE);
提示: ORDER BY_CLAUSE 之后可接ASC DESC,ASC DESC可选

--1 ROW_NUMBER (<ARGUMENT>,<ARGUMENT>...) OVER(<PARTITION_CLAUSE> ORDER BY_CLAUSE );

--注:排序,排序序号无重复值,PARTITION BY可选,ORDER BY必选

--1.1 按照薪资水平从高到低输出所有人的信息
  SELECT ENAME,DEPTNO,SAL,HIREDATE,ROW_NUMBER() OVER(ORDER BY SAL DESC) FROM EMP;
--1.2 先按部门分组,再按照薪资水平从高到低输出所有人的信息
  SELECT ENAME,DEPTNO,SAL,HIREDATE,ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) FROM EMP;
--1.3 求出各个部门前2名薪资最高的人员信息
  SELECT * FROM (SELECT ENAME,DEPTNO,SAL,HIREDATE,ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RN FROM EMP) WHERE RN < 3;
 
 --2 RANK (<ARGUMENT>,<ARGUMENT>...) OVER(<PARTITION_CLAUSE> ORDER BY_CLAUSE );
 
 --注:跳跃排序,排序序号会有重复,PARTITION BY可选,ORDER BY必选
 
 --2.1 先按照部门降序,再按照薪资降序排序
  SELECT ENAME,DEPTNO,SAL,HIREDATE,RANK() OVER(ORDER BY DEPTNO DESC,SAL DESC) FROM EMP;
 --2.2 先按照部门分区(分组),再按照薪资升序排序
  SELECT ENAME,DEPTNO,SAL,HIREDATE,RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL) FROM EMP;

--3 DENSE_RANK(<ARGUMENT>,<ARGUMENT>...) OVER(<PARTITION_CLAUSE> ORDER BY_CLAUSE );

--注:连续排序,排序序号会有重复,PARTITION BY可选,ORDER BY必选

--3.1 先按部门排序,再只能找工资排序
  SELECT ENAME,DEPTNO,SAL,HIREDATE,DENSE_RANK() OVER(ORDER BY DEPTNO DESC,SAL DESC) FROM EMP;
--3.2 先按照部门分组,再按照薪资排序
  SELECT ENAME,DEPTNO,SAL,HIREDATE,DENSE_RANK() OVER(PARTITION BY DEPTNO ORDER BY SAL) FROM EMP; 

 7.2.3 函数为位移函数
LEAD上移(列,参数)
LAG  下移(列,参数)

--1 NTILE(N) OVER():将记录平均分成N份,多出的按照次序分给前面的组。PARTITION BY可选,ORDER BY必选。

--1.1 先按照部门分组,再将每个部门的数据按照薪资从低到高分成4份。
  SELECT ENAME,DEPTNO,SAL,HIREDATE,NTILE(4) OVER(PARTITION BY DEPTNO ORDER BY SAL) FROM EMP;

--2 FIRST_VALUE() OVER():取出分区中第一条记录字段的值。PARTITION BY可选 ,ORDER BY可选。

--3 LAST_VALUE() OVER() :取出分区中最后一条记录字段的值。PARTITION BY可选,ORDER BY可选。

--4 LAG(FIELD,NUM,DEFAULTVALUE) OVER():取出前N行数据,加到当前行作为一个新的列。PARTITION BY可选,ORDER BY必选。

-- 注:FIELD为要查找的字段,NUM代表往前查找NUM行的数据,DEFAULTVALUE代表没有符合条件的默认值。

--4.1 将前一个用户的薪资作为新的列FORWARD_SAL加入到当前用户信息中。
  SELECT ENAME,DEPTNO,SAL,HIREDATE,LAG(SAL,1,0) OVER(ORDER BY SAL) FORWARD_SAL FROM EMP;
--4.2 按照部门分组,再将前一个用户的薪资作为新的列FORWARD_SAL加入到当前用户信息中
  SELECT ENAME,DEPTNO,SAL,HIREDATE,LAG(SAL,1,0) OVER(PARTITION BY DEPTNO ORDER BY SAL) FORWARD_SAL FROM EMP;

--5 LEAD() OVER():取出后N行数据,加到当前行作为一个新的列。PARTITION BY可选,ORDER BY必选。

--5.1 按照SAL从小到大排序,取出后一行的SAL数据添加到当前行,作为一个新的列
  SELECT ENAME,DEPTNO,SAL,HIREDATE,LEAD(SAL,1,0) OVER(ORDER BY SAL) FROM EMP;

7.3 开窗子句

--1 显示各部门员工的工资,并附带显示该部分的最高工资。
  SELECT E.DEPTNO,
       E.EMPNO,
       E.ENAME,
       E.SAL,
       LAST_VALUE(E.SAL) 
       OVER(PARTITION BY E.DEPTNO 
            ORDER BY E.SAL ROWS 
            --UNBOUNDED PRECEDING AND UNBOUNED FOLLOWING针对当前所有记录的前一条、后一条记录,也就是表中的所有记录
            --UNBOUNDED:不受控制的,无限的
            --PRECEDING:在...之前
            --FOLLOWING:在...之后
            BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) MAX_SAL
  FROM EMP E;
	
--2 对各部门进行分组,并附带显示:
--2.1 第一行至当前行的汇总
  SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注:ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW  是指第一行至当前行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) SUM_SAL
  FROM SCOTT.EMP;
	
--2.2 当前行至最后一行的汇总
  SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注:ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING 指当前行到最后一行的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN CURRENT ROW AND UNBOUNDED FOLLOWING) SUM_SAL
  FROM SCOTT.EMP;
	
--2.3 当前行的上一行(ROWNUM-1)到当前行的汇总
  SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注:ROWS BETWEEN 1 PRECEDING AND CURRENT ROW 是指当前行的上一行(ROWNUM-1)到当前行的汇总 
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME ROWS 
                     BETWEEN 1 PRECEDING AND CURRENT ROW) SUM_SAL
  FROM SCOTT.EMP;
	
--2.4 当前行的上一行(ROWNUM-1)到当前行的下两行(ROWNUM+2)的汇总     
  SELECT EMPNO,
       ENAME,
       DEPTNO,
       SAL,
       --注:ROWS BETWEEN 1 PRECEDING AND 1 FOLLOWING 是指当前行的上一行(ROWNUM-1)到当前行的下两行(ROWNUM+2)的汇总
       SUM(SAL) OVER(PARTITION BY DEPTNO 
                     ORDER BY ENAME 
                     ROWS BETWEEN 1 PRECEDING AND 2 FOLLOWING) SUM_SAL
  FROM SCOTT.EMP;

附表

附表为Oracle自带SCOTT用户下的员工表&部门表&工资等级表:EMP、DEPT、SALGRADE。(可直接执行以下语句得到表结构和数据)

--附表1:SCOTT表中的EMP表:员工表

SELECT * FROM EMP
--DROP TABLE EMP

-- CREATE TABLE
CREATE TABLE EMP
(
  EMPNO NUMBER(4),--员工编号,EMP表主键
	ENAME VARCHAR2(10), --员工姓名
	JOB VARCHAR2(9), --员工工作
	MGR NUMBER(4),  --员工的领导编号,引用EMPNO
	HIREDATE DATE , --入职日期 
	SAL NUMBER(7,2),  --员工工资 
	COMM NUMBER(7,2),  --员工奖金
	DEPTNO NUMBER(2)  --员工部门编号,是表DEPT的外键。
);
-- ADD COMMENTS TO THE TABLE 
COMMENT ON TABLE EMP
  IS '员工表';
-- ADD COMMENTS TO THE COLUMNS 
COMMENT ON COLUMN EMP.EMPNO
  IS '员工编号,EMP表主键';
COMMENT ON COLUMN EMP.ENAME
  IS '员工姓名';
COMMENT ON COLUMN EMP.JOB
  IS '员工工作';
COMMENT ON COLUMN EMP.MGR
  IS '员工的领导编号,引用EMPNO ';
COMMENT ON COLUMN EMP.HIREDATE
  IS '入职日期 ';
COMMENT ON COLUMN EMP.SAL
  IS '员工工资';
COMMENT ON COLUMN EMP.COMM
  IS '员工奖金';
COMMENT ON COLUMN EMP.DEPTNO
  IS '员工部门编号,是表DEPT的外键。';
	
--附表2:SCOTT表中的DEPT表:部门表

SELECT * FROM DEPT
--DROP TABLE DEPT

-- CREATE TABLE
CREATE TABLE DEPT
(
  DEPTNO NUMBER(2), --部门编号,主键
	DNAME VARCHAR2(14), --部门名称
	LOC VARCHAR2(13) --部门位置
);
-- ADD COMMENTS TO THE TABLE 
COMMENT ON TABLE DEPT
  IS '部门表';
-- ADD COMMENTS TO THE COLUMNS 
COMMENT ON COLUMN DEPT.DEPTNO
  IS '部门编号,主键';
COMMENT ON COLUMN DEPT.DNAME
  IS '部门名称';
COMMENT ON COLUMN DEPT.LOC
  IS '部门位置';
	
--附表3:SCOTT表中的SALGRADE表:工资等级表

SELECT * FROM SALGRADE

-- CREATE TABLE
CREATE TABLE SALGRADE
(
  GRADE NUMBER, --等级
	LOSAL NUMBER, --此等级下最低工资
	HISAL NUMBER --此等级下最高工资
);
-- ADD COMMENTS TO THE TABLE 
COMMENT ON TABLE SALGRADE
  IS '工资等级表';
-- ADD COMMENTS TO THE COLUMNS 
COMMENT ON COLUMN SALGRADE.GRADE
  IS '等级';
COMMENT ON COLUMN SALGRADE.LOSAL
  IS '此等级下最低工资';
COMMENT ON COLUMN SALGRADE.HISAL
  IS '此等级下最高工资';

--插入数据

INSERT INTO DEPT (DEPTNO, DNAME, LOC)VALUES (10, 'ACCOUNTING', 'NEW YORK');
INSERT INTO DEPT (DEPTNO, DNAME, LOC)VALUES (20, 'RESEARCH', 'DALLAS');
INSERT INTO DEPT (DEPTNO, DNAME, LOC)VALUES (30, 'SALES', 'CHICAGO');
INSERT INTO DEPT (DEPTNO, DNAME, LOC)VALUES (40, 'OPERATIONS', 'BOSTON');
COMMIT;

INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('17-12-1980', 'DD-MM-YYYY'), 1000, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('20-02-1981', 'DD-MM-YYYY'), 1800, 300, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('22-02-1981', 'DD-MM-YYYY'), 1450, 500, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('02-04-1981', 'DD-MM-YYYY'), 2975, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('28-09-1981', 'DD-MM-YYYY'), 1450, 1400, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('01-05-1981', 'DD-MM-YYYY'), 2850, NULL, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('09-06-1981', 'DD-MM-YYYY'), 2450, NULL, 10);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('19-04-1987', 'DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7839, 'KING', 'PRESIDENT', NULL, TO_DATE('17-11-1981', 'DD-MM-YYYY'), 5000, NULL, 10);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('08-09-1981', 'DD-MM-YYYY'), 1700, 0, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('23-05-1987', 'DD-MM-YYYY'), 1300, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7900, 'JAMES', 'CLERK', 7698, TO_DATE('03-12-1981', 'DD-MM-YYYY'), 1150, NULL, 30);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('03-12-1981', 'DD-MM-YYYY'), 3000, NULL, 20);
INSERT INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES (7934, 'MILLER', 'CLERK', 7782, TO_DATE('23-01-1982', 'DD-MM-YYYY'), 1500, NULL, 10);
COMMIT;

INSERT INTO SALGRADE (GRADE, LOSAL, HISAL)
VALUES (1, 700, 1200);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL)
VALUES (2, 1201, 1400);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL)
VALUES (3, 1401, 2000);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL)
VALUES (4, 2001, 3000);
INSERT INTO SALGRADE (GRADE, LOSAL, HISAL)
VALUES (5, 3001, 9999);
COMMIT;

 

文章来源:https://blog.csdn.net/qq_31652795/article/details/116126431

后台-系统设置-扩展变量-手机广告位-内容正文底部
版权声明

本文仅代表作者观点,不代表本站立场。
本文系作者授权发表,未经许可,不得转载。
本文地址:https://jcdi.cn/shujuku/4314526cdc2ce0031a0c06d6a1451ce0.html

留言与评论(共有 0 条评论)
   
验证码:
后台-系统设置-扩展变量-手机广告位-评论底部广告位

教程弟

https://www.jcdi.cn/

统计代码 | 京ICP1234567-2号

Powered By 教程弟 教程弟

使用手机软件扫描微信二维码