Oracle with使用方法以及递归

数据准备

表结构

-- 部门表
CREATE TABLE DEPT (
    dept_no VARCHAR2(5) NOT NULL,
    dept_name VARCHAR2(255) NOT NULL,
    PRIMARY KEY(dept_no)
);
-- 添加注释
COMMENT ON TABLE DEPT IS '部门表';
COMMENT ON COLUMN DEPT.dept_no IS '部门编码';
COMMENT ON COLUMN DEPT.dept_name IS '部门名称';
-- 员工表
CREATE TABLE EMP (
	emp_no VARCHAR2(8) NOT NULL,
    emp_name VARCHAR2(20) NOT NULL,
    dept_no VARCHAR2(5) NOT NULL,
    salary NUMBER(10, 2),
    PRIMARY KEY(emp_no)
);
-- 添加注释
COMMENT ON TABLE EMP IS '员工表';
COMMENT ON COLUMN EMP.emp_no IS '员工编码';
COMMENT ON COLUMN EMP.emp_name IS '员工名称';
COMMENT ON COLUMN EMP.dept_no IS '所属部门编码';
COMMENT ON COLUMN EMP.salary IS '工资';

演示数据

-- 插入部门
insert into DEPT(dept_no, dept_name) values ('D001', '总经理部');
insert into DEPT(dept_no, dept_name) values ('D002', '人力资源部');
insert into DEPT(dept_no, dept_name) values ('D003', '行政后勤部');
insert into DEPT(dept_no, dept_name) values ('D004', '销售一部');
insert into DEPT(dept_no, dept_name) values ('D005', '销售二部');
insert into DEPT(dept_no, dept_name) values ('D006', '研发一部');
insert into DEPT(dept_no, dept_name) values ('D007', '研发二部');

-- 批量插入员工数据
declare
	type e_name is varray(7) of varchar2(20);
	e_name_arr e_name :=e_name('陈天龙','李晓红','田萌','张三','李四', '王五', '赵六');
begin
	for d in 1..7 loop
		for i in 1..(d*3) Loop
			 insert into EMP(emp_no, emp_name, dept_no, salary) values (
             	'E' || d || replace(lpad(i,5),' ','0'),
                 e_name_arr(d) || i || '号',
                 'D' || replace(lpad(d,3),' ','0'),
                 trunc(dbms_random.value(3,80)) * 1000
             );
		end loop;
	end loop;
end;

基本语法

简单的with语句:

WITH t AS 
(SELECT * FROM EMP)
SELECT * FROM t;

在视图中使⽤WITH语句进⾏连接:

CREATE OR REPLACE VIEW V_EMP_DETAIL AS 
WITH W_DEPT AS (
    SELECT * FROM DEPT
),
W_EMP AS (
    SELECT * FROM EMP
)
SELECT d.dept_name, e.* 
FROM W_EMP e 
LEFT JOIN W_DEPT d ON d.dept_no = e.dept_no;

总结：

• 使⽤WITH AS 语句可以为⼀个⼦查询语句块定义⼀个名称，在查询语句的其他地⽅引⽤这个⼦查询。

• Oracle 数据库像对待内联视图或临时表⼀样对待 被引⽤的⼦查询名称，从⽽起到⼀定的优化作⽤

• 在同级select前有多个查询定义的时候，第1个⽤with，后⾯的不⽤with，并且⽤逗号隔开。

• 最后⼀个with ⼦句与下⾯的查询之间不能有逗号，只通过右括号分割,with ⼦句的查询必须⽤括号括起来

WITH语句的优点：

1. SQL可读性增强。⽐如对于特定with⼦查询取个有意义的名字等。
2. with⼦查询只执⾏⼀次，将结果存储在⽤户临时表空间中，可以引⽤多次，增强性能。

示例

1、查询出部门的总工资⼤于所有部门平均总工资的部门。

分析：做这个查询，⾸先必须计算出所有部门的总工资，然后计算出所有部门的平均总工资，再筛选出部门的总工资⼤于所有部门总工资平均工资的部门。

1. 那么第1步 with 查询查出所有部门的总工资
2. 第2步⽤with 从第1 步获得的结果表中查询出平均工资
3. 最后利⽤这两次 的with 查询⽐较总工资⼤于平均工资的结果

WITH W_DEPT_TOTAL_SALARY AS -- 查询出部门的总⼯资
(	SELECT d.dept_name, SUM(e.salary) total_salary
    FROM DEPT d
 	JOIN EMP e ON e.dept_no = d.dept_no
    GROUP BY d.dept_name
),
W_DEPT_AVG_SALARY AS -- 查询出部门的平均⼯资，在后⼀个WITH语句中可以引⽤前⼀个定义的WITH语句
(
	SELECT SUM(total_salary) / COUNT(1) avg_salary 
    FROM W_DEPT_TOTAL_SALARY
)
SELECT *
FROM W_DEPT_TOTAL_SALARY dts
WHERE dts.total_salary > ( -- 进⾏⽐较
    SELECT das.avg_salary 
    FROM W_DEPT_AVG_SALARY das
);

2. 统计数据并关联到每条员工数据

展⽰根据查询结果查询出的数据，并把根据查询出的结果进⾏统计，如最⼤⼯资，最⼩⼯资，平均⼯资，
进⾏级联，由于查询的统计数据的条数为1条，所以不会发⽣笛卡⼉积的错误，

WITH W_EMP AS -- 查询基础数据
(
    SELECT emp_no, emp_name, dept_no, salary
    FROM EMP
),
W_EMP_DATA AS -- 查询统计数据
(	
    SELECT MAX(salary) as max_salary, 
    	MIN(salary) as min_salary, 
    	SUM(salary) as total_salary
 	FROM W_EMP
)
SELECT *
FROM W_EMP, W_EMP_DATA -- 进⾏级联，由于查询的统计数据的条数为1条，所以不会发⽣笛卡⼉积的错误

3. 后⾯的with定义可以引⽤前⾯的结果集，但是with⼦查询不可嵌套定义。

下⾯的语句错误：因为不允许嵌套定义with语句

WITH W_EMP_2 AS
-- with中有嵌套with,不允许
(
	WITH W_EMP AS
   	(
		SELECT emp_name FROM EMP WHERE emp_no='E100001'
    )
	SELECT emp_name FROM W_EMP
)
SELECT * FROM W_EMP_2;  

递归案例

1. 实现从1到10的输出

   with w_num(n) as (
   	select 1 as n from dual
       union all
       select n+1 from w_num where n<10
   )
   select n from w_num;
   

2. 空瓶换啤酒最多能喝几瓶问题

   /**
   	2元1瓶啤酒
   	4个瓶盖换1瓶啤酒
   	2个空瓶换1瓶啤酒
   	问：10元可以喝几瓶
   */
   with w_drink_beer(beer, bottle, lid) AS
   (
   	select 10/2 as beer, 10/2 as bottle, 10/2 as lid
       from dual
       union all
       select 
       	beer + trunc(bottle/2) + trunc(lid/4) as beer,
       	mod(bottle, 2) + trunc(bottle/2) + trunc(lid/4) as bottle,
       	mod(lid, 4) + trunc(bottle/2) + trunc(lid/4) as lid
       from w_drink_beer
       where trunc(bottle/2) != 0 or trunc(lid/4) != 0
   )
   select beer as '喝了几瓶啤酒', bottle as '剩下几个瓶子', lid as '剩下几个瓶盖'
   from w_drink_beer;
   

递归-地铁线路换乘问题

SQL案例分析：地铁换乘线路查询

示例表和脚本下载：https://github.com/dongxuyang1985/sql_in_action

-- Oracle
WITH transfer (start_station, stop_station, stops, path) AS (
  SELECT station_name, next_station, 1, line_name||station_name||'->'||line_name||next_station
    FROM bj_subway WHERE station_name = '王府井'
   UNION ALL
  SELECT p.start_station, e.next_station, stops + 1, p.path||'->'||e.line_name||e.next_station
    FROM transfer p
    JOIN bj_subway e
      ON p.stop_station = e.station_name AND (INSTR(p.path, e.next_station) = 0)
)
SELECT * FROM transfer WHERE stop_station ='积水潭';