数据准备
表结构
-- 部门表 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;
递归-地铁线路换乘问题
示例表和脚本下载: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 ='积水潭';