plsql高级操作

627

---group by 子句的增强

--rollup
使用Rollup 产生常规分组汇总行以及分组小计
SELECT department_id, job_id, SUM(salary)
FROM employees
WHERE department_id < 60
GROUP BY ROLLUP(department_id, job_id);
▲Rollup 后面跟了n个字段，就将进行n+1次分组，从左到右每次减少一个字段进行分组；
然后进行union

--cube
在Group By 中使用Cube 产生Rollup结果集+ 多维度的交叉表数据源：
SELECT department_id, job_id, SUM(salary)
  FROM employees
WHERE department_id < 60
GROUP BY CUBE(department_id, job_id);
▲Cube 后面跟了n个字段，就将进行2的N次方的分组运算，然后进行；

--grouping函数
当用rollup和cube进行分组时，可以使用grouping函数判断哪些行是针对那些列或者列的组合进行
分组运算的结果的，没有被Grouping到返回1，否则返回0
SELECT department_id DEPTID,
       job_id JOB,
       SUM(salary),
       GROUPING(department_id) GRP_DEPT,
       GROUPING(job_id) GRP_JOB
  FROM employees
WHERE department_id < 50
GROUP BY ROLLUP(department_id, job_id);

--使用Grouping Set 来代替多次UNION
SELECT department_id, job_id, manager_id, avg(salary)
  FROM employees
GROUP BY GROUPING SETS((department_id, job_id),(job_id, manager_id));



---子查询进阶

--非相关子查询当作一张表来用
SELECT a.last_name, a.salary, a.department_id, b.salavg
  FROM employees a,
       (SELECT department_id, AVG(salary) salavg
          FROM employees
         GROUP BY department_id) b
WHERE a.department_id = b.department_id
   AND a.salary > b.salavg;

--相关子查询，子查询中参考了主表
SELECT last_name, salary, department_id
  FROM employees
outer WHERE salary > (SELECT AVG(salary)
                         FROM employees
                        WHERE department_id = outer.department_id);

--exists 操作
SELECT employee_id, last_name, job_id, department_id
  FROM employees
outer WHERE EXISTS
(SELECT 'X' FROM employees WHERE manager_id = outer.employee_id

--使用with子句
WITH dept_costs AS
(SELECT d.department_name, SUM(e.salary) AS dept_total
    FROM employees e, departments d
   WHERE e.department_id = d.department_id
   GROUP BY d.department_name),
avg_cost AS
(SELECT SUM(dept_total) / COUNT(*) AS dept_avg FROM dept_costs)
SELECT *
  FROM dept_costs
WHERE dept_total > (SELECT dept_avg FROM avg_cost)
ORDER BY department_name;



---递归查询

SELECT LEVEL, column, expr .. .
  FROM table
WHERE condition(s)
START WITH condition(s)
CONNECT BY PRIOR condition(s);
-查询从101开始，从下往上的各级员工。
SELECT employee_id, last_name, job_id, manager_id
  FROM employees
START WITH employee_id = 101
CONNECT BY PRIOR manager_id = employee_id;

--使用level关键字和lpad函数，显示树形层次
SELECT LPAD(last_name, LENGTH(last_name) + (LEVEL * 2) - 2, '_') AS org_chart
  FROM employees
START WITH last_name = 'King'
CONNECT BY PRIOR employee_id = manager_id


---insert增强

--一个来源插入多个目标表
INSERT ALL INTO sal_history
VALUES
  (EMPID, HIREDATE, SAL) INTO mgr_history
VALUES
  (EMPID, MGR, SAL)
  SELECT employee_id EMPID, hire_date HIREDATE, salary SAL, manager_id MGR
    FROM employees
   WHERE employee_id > 200;

--一个来源插入多个目标表（有条件，首次匹配即跳到下一条）
INSERT FIRST WHEN SAL > 25000 THEN INTO special_sal
VALUES
  (DEPTID, SAL) WHEN HIREDATE like
  ('%00%') THEN INTO hiredate_history_00
VALUES
  (DEPTID, HIREDATE) WHEN HIREDATE like
  ('%99%') THEN INTO hiredate_history_99
VALUES
  (DEPTID, HIREDATE) ELSE INTO hiredate_history
VALUES
  (DEPTID, HIREDATE)
  SELECT department_id DEPTID, SUM(salary) SAL, MAX(hire_date) HIREDATE
    FROM employees
   GROUP BY department_id;

--列转行（一行变多行，交叉报表的反操作）。
INSERT ALL INTO sales_info
VALUES
  (employee_id, week_id, sales_MON) INTO sales_info
VALUES
  (employee_id, week_id, sales_TUE) INTO sales_info
VALUES
  (employee_id, week_id, sales_WED) INTO sales_info
VALUES
  (employee_id, week_id, sales_THUR) INTO sales_info
VALUES
  (employee_id, week_id, sales_FRI)
  SELECT EMPLOYEE_ID,
         week_id,
         sales_MON,
         sales_TUE,
         sales_WED,
         sales_THUR,
         sales_FRI
    FROM sales_source_data;