源码-PL/SQL从入门到精通-第九章-SQL内置函数-Part 3

这一章的SQL语句比较长，第一次接触，感觉用不习惯，可能需要在用到的时候查资料了。不管怎样，先把源码备在这里。

--排序子句(Order by)--未指定排序时，AVG值相同SELECT ename, sal, AVG(sal) OVER()FROM   empWHERE  ROWNUM <= 3;--指定排序后，计算累计平均值SELECT ename, sal, AVG(sal) OVER(ORDER BY ename)FROM   empWHERE  ROWNUM <= 3;select ((select sal from emp where ename='JONES')+(select sal from emp where ename='PANDENG'))/2.0 from dual;select sum(sal) from emp;--开窗子句（windowing）SELECT deptno,       SUM(sal) 部门薪资小计,       SUM(SUM(sal)) OVER(ORDER BY deptno ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) 部门总计FROM   empGROUP  BY deptno;--9.2.4分析函数列表--COUNT函数SELECT empno, ename, COUNT(*) OVER(PARTITION BY deptno  ORDER BY empno) 条数小计FROM   emp;--Range betweenSELECT empno,       ename,       sal,       COUNT(*) OVER(ORDER BY sal RANGE BETWEEN 50 PRECEDING AND 150 FOLLOWING) 薪水差异个数FROM   emp--AVG函数         SELECT deptno,                empno,                ename,                sal,                AVG(sal) OVER(PARTITION BY deptno ORDER BY deptno) avg_sal         FROM   emp;--MIN和MAX函数SELECT deptno,       empno,       ename,       hiredate,       sal,       MIN(sal) OVER(PARTITION BY deptno ORDER BY hiredate RANGE UNBOUNDED PRECEDING) 最低薪水,       MAX(sal) OVER(PARTITION BY deptno ORDER BY hiredate RANGE UNBOUNDED PRECEDING) 最高薪水FROM   emp;--RANK,DENSE_RANK和ROW_NUMBER函数SELECT deptno,       ename,       sal,       mgr,       RANK() OVER(ORDER BY deptno) RANK结果,       DENSE_RANK() OVER(ORDER BY deptno) DENSE_RANK结果,       ROW_NUMBER() OVER(ORDER BY deptno) ROW_NUMBER结果FROM   empWHERE  deptno IN (10, 20, 30) AND mgr IS NOT NULLORDER  BY DEPTNO;--Min, Max函数SELECT deptno,       MIN(sal) KEEP(DENSE_RANK FIRST ORDER BY comm) 最低提成薪水,       MAX(sal) KEEP(DENSE_RANK LAST ORDER BY comm) 最高提成薪水FROM   empWHERE  deptno IN (10, 20, 30)GROUP  BY deptno;select min(sal),max(sal) from emp WHERE  deptno IN (10, 20, 30)--FIRST和LAST函数(不太明白)SELECT deptno,       empno,       sal,       FIRST_VALUE(sal) OVER(PARTITION BY deptno  ORDER BY empno) "第一个值",       LAST_VALUE(sal) OVER(PARTITION BY deptno  ORDER BY empno) "最后一个值"FROM   empWHERE  deptno IN (10, 20);--LAG和LEAD函数（暂且起名错位函数）SELECT ename,       hiredate,       sal,       deptno,       LAG(sal, 1, 0) OVER(ORDER BY hiredate)  AS 前一个雇员薪水,       LEAD(sal, 1, 0) OVER(ORDER BY hiredate) AS 后一个雇员薪水FROM   empWHERE  deptno = 20;--9.3分析函数使用示例--记录排名SELECT deptno,       empno,       ename,       SUM(sal) dept_sales,       RANK() OVER(PARTITION BY deptno ORDER BY SUM(sal) DESC NULLS LAST) 薪资排名_跳号,       DENSE_RANK() OVER(PARTITION BY deptno ORDER BY SUM(sal) DESC NULLS LAST) 薪资排名_同级同号,       ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY SUM(sal) DESC NULLS LAST) 薪资排名_不跳号FROM   empGROUP  BY deptno, empno, enameDELETEFROM   EMPWHERE  deptno IS NULL;SELECT *FROM   EMPORDER  BY SAL           SELECT deptno, MIN(SAL), MAX(SAL)           FROM   EMP           GROUP  BY deptno;--首尾记录查询（First, last）SELECT MIN(empno) KEEP(DENSE_RANK FIRST ORDER BY SUM(sal) DESC NULLS LAST) 薪资排名首位,       MIN(empno) KEEP(DENSE_RANK LAST ORDER BY SUM(sal) DESC NULLS LAST) 薪资排名尾位FROM   empWHERE  sal IS NOT NULL AND deptno IS NOT NULLGROUP  BY empno;--前后排名查询SELECT deptno, empno, ename, dept_sales, 薪资排名FROM   (SELECT deptno,               empno,               ename,               SUM(sal) dept_sales,               RANK() OVER(PARTITION BY deptno ORDER BY SUM(sal) DESC NULLS LAST) 薪资排名        FROM   emp        WHERE  deptno IS NOT NULL        GROUP  BY deptno, empno, ename)WHERE  薪资排名 <= 2;SELECT deptno, empno, ename, dept_sales, 薪资排名FROM   (SELECT deptno,               empno,               ename,               SUM(sal) dept_sales,               RANK() OVER(PARTITION BY deptno ORDER BY SUM(sal) NULLS LAST) 薪资排名        FROM   emp        WHERE  deptno IS NOT NULL        GROUP  BY deptno, empno, ename)WHERE  薪资排名 <= 2;--层次查询(ntile)SELECT *FROM   (SELECT deptno,               empno,               ename,               SUM(sal) dept_sales,               NTILE(3) OVER(PARTITION BY deptno ORDER BY SUM(sal) NULLS LAST) RANK_RATIO        FROM   emp        WHERE  deptno IS NOT NULL        GROUP  BY deptno, empno, ename)WHERE  RANK_RATIO = 1 --范围统计查询（看不大懂）  SELECT empno,         ename,         hiredate,         sal,         MAX(sal) OVER(ORDER BY hiredate ROWS BETWEEN 10 PRECEDING AND CURRENT ROW) "前10天入职最高薪资",         MAX(sal) OVER(ORDER BY hiredate ROWS BETWEEN CURRENT ROW AND 10 FOLLOWING) "后10天入职最高薪资"        FROM   emp        WHERE  deptno IN (10, 20, 30) AND sal IS NOT NULL;--相邻记录比较SELECT ename,       hiredate,       deptno,       sal,       sal - prev_sal "与前面的差异",       sal - next_sal "与后面的差异"FROM   (SELECT ename,               hiredate,               sal,               deptno,               LAG(sal, 1, 0) OVER(ORDER BY hiredate) AS prev_sal,               LEAD(sal, 1, 0) OVER(ORDER BY hiredate) AS next_sal        FROM   emp        WHERE  deptno IS NOT NULL AND SAL IS NOT NULL);--抑制重复SELECT *FROM   (SELECT empno,               ename,               sal,               hiredate,               ROW_NUMBER() OVER(PARTITION BY EXTRACT(YEAR FROM hiredate) ORDER BY empno) rn        FROM   emp        WHERE  hiredate IS NOT NULL AND               EXTRACT(YEAR FROM hiredate) between 1980 and 2016)WHERE  rn = 1;--行列转换（不太明白）SELECT deptno,       ename,       ROW_NUMBER() OVER(PARTITION BY deptno ORDER BY sal DESC NULLS LAST) seqFROM   emp         SELECT MAX(DECODE(job, '职员', ename, NULL)) "职员名称",                MAX(DECODE(job, '职员', empno, NULL)) "职员编号",                MAX(DECODE(job, '分析人员', ename, NULL)) "分析人员名称",                MAX(DECODE(job, '分析人员', empno, NULL)) "分析人员编号",                MAX(DECODE(job, '经理', ename, NULL)) "经理名称",                MAX(DECODE(job, '经理', empno, NULL)) "经理编号",                MAX(DECODE(job, '老板', ename, NULL)) "老板名称",                MAX(DECODE(job, '老板', empno, NULL)) "老板编号",                MAX(DECODE(job, '销售人员', ename, NULL)) "销售人员名称",                MAX(DECODE(job, '销售人员', empno, NULL)) "销售人员编号"         FROM   (SELECT job,                        empno,                        ename,                        ROW_NUMBER() OVER(PARTITION BY job ORDER BY ename) rn                 FROM   emp                 WHERE  job IS NOT NULL) x         GROUP  BY rn;SELECT MAX(DECODE(job, '职员', ename, NULL)) "职员名称",       MAX(DECODE(job, '分析人员', ename, NULL)) "分析人员名称",       MAX(DECODE(job, '经理', ename, NULL)) "经理名称",       MAX(DECODE(job, '老板', ename, NULL)) "老板名称",       MAX(DECODE(job, '销售人员', ename, NULL)) "销售人员名称"FROM   (SELECT job,               empno,               ename,               ROW_NUMBER() OVER(PARTITION BY job ORDER BY ename) rn        FROM   emp        WHERE  job IS NOT NULL) xGROUP  BY rn;SELECT job, empno, ename, ROW_NUMBER() OVER(PARTITION BY job ORDER BY ename) rnFROM   empWHERE  job IS NOT NULL --在PL/SQL中使用分析函数DECLARE TYPE refempcur IS REF CURSOR;empcur refempcur;jobname VARCHAR(20); --职位名ename VARCHAR2(20); --员工名empno NUMBER; --员工编号rn INT; --排名BEGIN  --打开游标  OPEN empcur FOR    SELECT job,           empno,           ename,           ROW_NUMBER() OVER(PARTITION BY job ORDER BY ename) rn    FROM   emp    WHERE  job IS NOT NULL;  --循环提取游标内容  LOOP    EXIT WHEN empcur%NOTFOUND;    FETCH empcur      INTO jobname, empno, ename, rn;    --输出游标内容    DBMS_OUTPUT.put_line(jobname || '   ' || empno || '   ' || ename || '   ' || rn);  END LOOP;END;