Oraclewith使用方法以及递归

数据准备

表结构

-- 部门表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 dtsWHERE 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

-- OracleWITH 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 ='积水潭';