文章转自：https://wenku.baidu.com/view/17e91c6fa98271fe910ef9ee.html
版权归原作者！

一、with表拥有某物

Mary married a man with a lot of money .
马莉嫁给了一个有着很多钱的男人。
I often dream of a big house with a nice garden .
我经常梦想有一个带花园的大房子。
The old man lived with a little dog on the lonely island .
这个老人和一条小狗住在荒岛上。

二、with表用某种工具或手段

I cut the apple with a sharp knife .
我用一把锋利的刀削平果。
Tom drew the picture with a pencil .
汤母用铅笔画画。

三、with表人与人之间的协同关系

make friends with sb
talk with sb
quarrel with sb
struggle with sb
fight with sb
play with sb
work with sb
cooperate with sb
I have been friends with Tom for ten years since we worked with each other, and I have never quarreled with him .
自从我们一起工作以来，我和汤姆已经是十年的朋友了，我们从没有吵过架。

四、with 表原因或理由

John was in bed with high fever .
约翰因发烧卧床。
He jumped up with joy .
他因高兴跳起来。
Father is often excited with wine .
父亲常因白酒变的兴奋。

五、with 表“带来”，或“带有,具有”，在…身上，在…身边之意

The girl with golden hair looks beautiful .
那个金头发的女孩看起来漂亮。
The famous director will come to the meeting with the leading actor and
actress.
那个有名的导演将带着男女主角来到会场。
A storm with a hurricane has come onto its way .
带有飓风的风暴要来了。
Do you have money with you .
身上带着钱吗？
Take the umbrella with you in case it rains .
随身带伞，以防下雨。

六、with表想法，信念，态度与…一致

I agree with you on how to deal with it .
关于此事如何处理，我同意你的看法。
I believe with the headmaster that a good teacher should not only teach a student what to learn but also how to learn it .
我相信校长所说，一个好老师不但教学生学什么，而且应教学生怎么去学。

七、with表示让步,“虽有,尽管”

With all his money and fame, he is not happy .
有着钱和名誉，他还是不快乐。
With good teachers and warmhearted classmates ,the new comer feels lonely .
虽有着好老师和热心的同学，这个新来的还是感觉孤独。

八、with表同时，或同一方向，“随着”

The temperature of cold-blooded animals change with the temperature of the surroundings .
冷血动物的体温随着周围的环境的改变而变化。
The big ship is sailing with the wind .
这个大船正随风向航行。
And with the last words , she turned away.
随着最后一句话说完，他转身离开了。

九、with的复合结构表行为方式或伴随情况

1、 with + n/pron + adj.
He left the room with the door open .
门开着，他就离开了屋子。
Don’t talk with your mouse full .
嘴里满是东西时不要说话。

2、with + n/pron + adv.
With the radio on ,grandma slept deeply in the chair .
收音机开者，奶奶在椅子里沉沉的睡着。
With Tom away , I always feel lonely .
汤母不在，我一直感觉孤独。

3、with + n/pron + done
The fellow stood there with his hands crossed .
这个家伙两手交叉，站在那里。
The peasants have a good harvest with all the crops got in .
庄稼都收割了，农民们有了一个好收成。

4、with + n/pron + to do
With this book to help you , you can finish your work as soon as possible .
有这本书来帮忙，你能尽快完成工作。
With so many problems to settle ,the new manager was too worried to eat anything .
有着太多问题要去处理，新经理担忧的吃不下任何东西。

5、with + n/pron + n
The old man looks down upon everyone ,with his son chairman of the company .
因为他儿子是公司的主席，这个老头瞧不起任何人。
He was taken to hospital with his legs a mass of bleeding flesh .
他被抬到医院，他的腿血肉模糊。

6、with + n/pron + 介词短语
The man left the meeting with a book in his hand .
这个男人手里拿着书离开了会议。
The woman with a diamond necklace around the neck must be wealthy .
那个脖子上带项链的女人一定很有钱。

十、with其他用法主要出现在一些常用词和习语中，记住其特定含义即可。

It is a long time not to get in touch with Tom.
很长时间没有和汤母取得联系了。
Down with imperialism.
打倒帝国主义。

🚀 优质资源分享 🚀

数据准备

表结构

-- 部门表
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 ='积水潭';