oracle 高级查询案例

分页查询显示员工信息，显示员工号，姓名，月薪

每页显示四条记录，显示第二页的员工，按照月薪降序排列

SELECT R,EMPNO,ENAME,SAL
  2  FROM (SELECT ROWNUM R,EMPNO,ENAME,SAL
  3        FROM(SELECT ROWNUM,EMPNO,ENAME,SAL FROM EMP ORDER BY SAL DESC) E1
  4        WHERE ROWNUM<=8) E2
  5   WHERE R>=5;

         R      EMPNO ENAME             SAL
---------- ---------- ---------- ----------
         5       7698 BLAKE            2850
         6       7782 CLARK            2450
         7       7499 ALLEN            1600
         8       7844 TURNER           1500

找到员工表中薪水大于本部门平均薪水的员工

--EXPLAIN PLAN FOR
SELECT E.EMPNO,E.ENAME,E.SAL,D.AVGSAL
  2  FROM EMP E,(SELECT DEPTNO,AVG(SAL) AVGSAL FROM EMP GROUP BY DEPTNO) D
  3  WHERE E.DEPTNO=D.DEPTNO AND E.SAL>D.AVGSAL;

     EMPNO ENAME             SAL     AVGSAL
---------- ---------- ---------- ----------
      7499 ALLEN            1600 1566.66667
      7566 JONES            2975       2175
      7698 BLAKE            2850 1566.66667
      7788 SCOTT            3000       2175
      7839 KING             5000 2916.66667
      7902 FORD             3000       2175
--SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);
--使用相关子查询更好,通过执行计划查询

按部门统计人数，按照如下格式输出（员工入职年份已知）

SELECT COUNT(*)  TOTAL,
  2           SUM(DECODE(TO_CHAR(hiredate,'YYYY'),'1981',1,0)) "1981",
  3           SUM(DECODE(TO_CHAR(hiredate,'YYYY'),'1980',1,0)) "1980",
  4   SUM(DECODE(TO_CHAR(hiredate,'YYYY'),'1982',1,0)) "1982",
  5   SUM(DECODE(TO_CHAR(hiredate,'YYYY'),'1987',1,0)) "1987"
  6* FROM EMP
  7  /

     TOTAL       1981       1980       1982       1987
---------- ---------- ---------- ---------- ----------
        14         10          1          1          2
SELECT
  2  (SELECT COUNT(*) FROM EMP) TOTAL,
  3  (SELECT COUNT(*) FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')='1980') "1980",
  4  (SELECT COUNT(*) FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')='1981') "1981",
  5  (SELECT COUNT(*) FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')='1982') "1982",
  6  (SELECT COUNT(*) FROM EMP WHERE TO_CHAR(HIREDATE,'YYYY')='1987') "1987"
  7  FROM DUAL;

     TOTAL       1980       1981       1982       1987
---------- ---------- ---------- ---------- ----------
        14          1         10          1          2

练习

 DESC PM_CI
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 CI_ID                                     NOT NULL VARCHAR2(20)
 STU_IDS                                            VARCHAR2(100)
 DESC PM_STU
 名称                                      是否为空? 类型
 ----------------------------------------- -------- ----------------------------
 STU_ID                                    NOT NULL VARCHAR2(20)
 STU_NAME                                           VARCHAR2(20)
SELECT * FROM PM_CI;

CI_ID
--------------------
STU_IDS
--------------------------------------------------------------------------------
1
1,2,3,4

2
1,4
 SELECT * FROM PM_STU;

STU_ID               STU_NAME
-------------------- --------------------
1                    张三
2                    李四
3                    王五
4                    赵六
 SELECT  C.CI_ID AS ID,
  2  --LISTAGG(S.STU_NAME,',') WITHIN GROUP(ORDER BY S.STU_NAME) AS NAME
  3  TO_CHAR(WM_CONCAT(S.STU_NAME)) AS NAME
  4  FROM PM_CI C,PM_STU S
  5  WHERE INSTR(C.STU_IDS,S.STU_ID)>0
  6  GROUP BY C.CI_ID;

ID
--------------------
NAME
--------------------------------------------------------------------------------
1
张三,赵六,王五,李四

2
张三,赵六