Oracle with使用方法以及递归

技术分享 3年前 (2022-04-25) 0 999+

数据准备

表结构

-- 部门表 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语句的优点：

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

示例

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

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

那么第1步 with 查询查出所有部门的总工资
第2步⽤with 从第1 步获得的结果表中查询出平均工资
最后利⽤这两次的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到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元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 ='积水潭';

发表评论