文章目录
显示
? 优质资源分享 ?
学习路线指引(点击解锁) | 知识定位 | 人群定位 |
---|---|---|
? Python实战微信订餐小程序 ? | 进阶级 | 本课程是python flask+微信小程序的完美结合,从项目搭建到腾讯云部署上线,打造一个全栈订餐系统。 |
?Python量化交易实战? | 入门级 | 手把手带你打造一个易扩展、更安全、效率更高的量化交易系统 |
数据准备
表结构
-- 部门表
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 ='积水潭';
转载请注明:xuhss » Oracle with使用方法以及递归