小让の码场通用表表达式
通用表表达式（Comon Table Expression）能够实现查询结果的重复利用，因此可以用来简化复杂的子查询和连接查询，提高SQL语句的可读性和性能，实现具有层次结构或者树状结构数据的遍历。
基本介绍
其基本语法如下：
sql
WITH t1[(col1, col2, ...)] AS (
subquery
),
[t2[(col1, col2, ...)] AS (
subquery
), ...]
SELECT * FROM t1[, t2];其中，WITH 关键字表示定义通用表表达式（简称 CTE），因此通用表表达式也被称为 WITH 查询。t1 和 t2 指定了 CTE 的名称，后面括号中的内容为可选的字段名。AS 关键字后面的子查询是 CTE 的定义语句，定义了它的表结构以及数据。最后的 SELECT 是主查询语句，它可以引用前面定义的 CTE。除了 SELECT，主查询语句也可以是 INSERT、UPDATE 或 DELETE 等。 WITH 子句中可以定义多个 CTE，每个 CTE 之间使用逗号分隔，并且已经定义的 CTE 可以被后续的 CTE 引用。
举个简单的栗子：
sql
WITH t1(n) AS (SELECT 1),
t2(m) AS (SELECT n + 1 FROM t1)
SELECT * FROM t1 CROSS JOIN t2;在上面的例子中，定义了两个 CTE，t1 包含一个字段 n 并且只有一行数据；t2 包含一个字段 m，同时在 t2 的定义中引用了前面的 t1。最后，在主查询语句中通过 t1 和 t2 的交叉连接返回两个表中的数据。查询结果如下所示：
sh
+---+---+
| n | m |
+---+---+
| 1 | 2 |
+---+---+递归查询
除提高查询的可读性和性能外，CTE 还支持在定义中进行自我引用，也就是实现了编程语言中的递归调用。递归形式的通用表表达式可以用于遍历具有层次结构或者树状结构的数据，例如遍历组织结构、查询地铁换路线图等。
其基本语法如下：
sql
WITH RECURSIVE cte_name AS (
cte_query_initial
UNION [ALL]
cte_query_iterative
)
SELECT * FROM cte_name;其中，关键字 WITH RECURSIVE 表示定义递归形式的 CTE（即递归 CTE）。递归 CTE 的定义包含两部分：
- cte_query_initial：表示初始化查询语句，用于创建初始化结果集；
- cte_query_iterative：表示递归查询语句，可以对当前 CTE 进行自我引用。每一次递归查询语句执行的结果都会再次作为输入，传递给下一次查询。如果递归查询无法从上一次迭代中返回更多的数据，将会终止递归。最后，使用
UNION [ALL]运算符合并这些结果集并进行输出。
Tip
递归 CTE 的执行顺序：
- 首先，执行 cte_query_initial 查询语句得到基本结果集(R0)，将该结果集作为第一次递归查询时的输入；
- 然后，执行递归查询语句前获取前一次递归查询之后的结果集(Ri-1)作为输入，然后将本次递归查询之后的结果集(Ri)输出；
- 重复第二步，直至前一次递归查询之后的结果集为空，即没有返回任何数据时，则终止递归；
- 最后，使用 UNION [ALL] 运算符将R0~Rn的结果集进行合并并输出。
Note
递归查询必须要有一个终止递归的条件，否则的话，递归将会进入死循环！MySQL 默认递归1000次（由系统变量 cte_max_recursion_depth 控制）后终止递归，并提示错误。
递归条件可以是遍历完表中的所有数据后不再返回更多记录或者在递归查询部分的 WHERE 子句中指定一个终止条件。
限制递归次数的终止条件必须写在 CTE 的定义中，写在主查询语句中是没有作用的。
案例一：组织结构
脚本，先使用 CREATE_TABLE.sql 创建表结构，LOAD_DATA.sql 生成初始化数据，最后测试完示例后可以使用 DROP_TABLE.sql 删除表。
sql
-- 创建 4 个示例表和索引
CREATE TABLE department
( dept_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '部门编号，自增主键'
, dept_name VARCHAR(50) NOT NULL COMMENT '部门名称'
) ENGINE=InnoDB COMMENT '部门信息表';
CREATE TABLE job
( job_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '职位编号，自增主键'
, job_title VARCHAR(50) NOT NULL COMMENT '职位名称'
, min_salary NUMERIC(8,2) NOT NULL COMMENT '最低月薪'
, max_salary NUMERIC(8,2) NOT NULL COMMENT '最高月薪'
) ENGINE=InnoDB COMMENT '职位信息表';
CREATE TABLE employee
( emp_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '员工编号，自增主键'
, emp_name VARCHAR(50) NOT NULL COMMENT '员工姓名'
, sex VARCHAR(10) NOT NULL COMMENT '性别'
, dept_id INTEGER NOT NULL COMMENT '部门编号'
, manager INTEGER COMMENT '上级经理'
, hire_date DATE NOT NULL COMMENT '入职日期'
, job_id INTEGER NOT NULL COMMENT '职位编号'
, salary NUMERIC(8,2) NOT NULL COMMENT '月薪'
, bonus NUMERIC(8,2) COMMENT '年终奖金'
, email VARCHAR(100) NOT NULL COMMENT '电子邮箱'
, comments VARCHAR(500) COMMENT '备注信息'
, create_by VARCHAR(50) NOT NULL COMMENT '创建者'
, create_ts TIMESTAMP NOT NULL COMMENT '创建时间'
, update_by VARCHAR(50) COMMENT '修改者'
, update_ts TIMESTAMP COMMENT '修改时间'
, CONSTRAINT ck_emp_sex CHECK (sex IN ('男', '女'))
, CONSTRAINT ck_emp_salary CHECK (salary > 0)
, CONSTRAINT uk_emp_email UNIQUE (email)
, CONSTRAINT fk_emp_dept FOREIGN KEY (dept_id) REFERENCES department(dept_id)
, CONSTRAINT fk_emp_job FOREIGN KEY (job_id) REFERENCES job(job_id)
, CONSTRAINT fk_emp_manager FOREIGN KEY (manager) REFERENCES employee(emp_id)
) ENGINE=InnoDB COMMENT '员工信息表';
CREATE INDEX idx_emp_name ON employee(emp_name);
CREATE INDEX idx_emp_dept ON employee(dept_id);
CREATE INDEX idx_emp_job ON employee(job_id);
CREATE INDEX idx_emp_manager ON employee(manager);
CREATE TABLE job_history
( history_id INTEGER NOT NULL AUTO_INCREMENT PRIMARY KEY COMMENT '工作历史编号，自增主键'
, emp_id INTEGER NOT NULL COMMENT '员工编号'
, dept_id INTEGER NOT NULL COMMENT '部门编号'
, job_id INTEGER NOT NULL COMMENT '职位编号'
, start_date DATE NOT NULL COMMENT '开始日期'
, end_date DATE NOT NULL COMMENT '结束日期'
, CONSTRAINT fk_job_history_emp FOREIGN KEY (emp_id) REFERENCES employee(emp_id)
, CONSTRAINT fk_job_history_dept FOREIGN KEY (dept_id) REFERENCES department(dept_id)
, CONSTRAINT fk_job_history_job FOREIGN KEY (job_id) REFERENCES job(job_id)
, CONSTRAINT check_job_history_date CHECK (end_date >= start_date)
) ENGINE=InnoDB COMMENT '员工工作历史记录表';
CREATE INDEX idx_job_history_emp ON job_history(emp_id);
CREATE INDEX idx_job_history_dept ON job_history(dept_id);
CREATE INDEX idx_job_history_job ON job_history(job_id);sql
-- 生成初始化数据
INSERT INTO department(dept_name) VALUES ('行政管理部');
INSERT INTO department(dept_name) VALUES ('人力资源部');
INSERT INTO department(dept_name) VALUES ('财务部');
INSERT INTO department(dept_name) VALUES ('研发部');
INSERT INTO department(dept_name) VALUES ('销售部');
INSERT INTO department(dept_name) VALUES ('保卫部');
INSERT INTO job(job_title, min_salary, max_salary) VALUES ('总经理', 24000, 50000);
INSERT INTO job(job_title, min_salary, max_salary) VALUES ('副总经理', 20000, 30000);
INSERT INTO job(job_title, min_salary, max_salary) VALUES ('人力资源总监', 20000, 30000);
INSERT INTO job(job_title, min_salary, max_salary) VALUES ('人力资源专员', 5000, 10000);
INSERT INTO job(job_title, min_salary, max_salary) VALUES ('财务经理', 10000, 20000);
INSERT INTO job(job_title, min_salary, max_salary) VALUES ('会计', 5000, 8000);
INSERT INTO job(job_title, min_salary, max_salary) VALUES ('开发经理', 12000, 20000);
INSERT INTO job(job_title, min_salary, max_salary) VALUES ('程序员', 5000, 12000);
INSERT INTO job(job_title, min_salary, max_salary) VALUES ('销售经理', 8000, 20000);
INSERT INTO job(job_title, min_salary, max_salary) VALUES ('销售人员', 4000, 8000);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('刘备', '男', 1, NULL, '2000-01-01', 1, 30000, 10000, 'liubei@shuguo.com', NULL, 'Admin', '2000-01-01 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('关羽', '男', 1, 1, '2000-01-01', 2, 26000, 10000, 'guanyu@shuguo.com', NULL, 'Admin', '2000-01-01 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('张飞', '男', 1, 1, '2000-01-01', 2, 24000, 10000, 'zhangfei@shuguo.com', NULL, 'Admin', '2000-01-01 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('诸葛亮', '男', 2, 1, '2006-03-15', 3, 24000, 8000, 'zhugeliang@shuguo.com', NULL, 'Admin', '2006-03-15 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('黄忠', '男', 2, 4, '2008-10-25', 4, 8000, NULL, 'huangzhong@shuguo.com', NULL, 'Admin', '2008-10-25 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('魏延', '男', 2, 4, '2007-04-01', 4, 7500, NULL, 'weiyan@shuguo.com', NULL, 'Admin', '2007-04-01 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('孙尚香', '女', 3, 1, '2002-08-08', 5, 12000, 5000, 'sunshangxiang@shuguo.com', NULL, 'Admin', '2002-08-08 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('孙丫鬟', '女', 3, 7, '2002-08-08', 6, 6000, NULL, 'sunyahuan@shuguo.com', NULL, 'Admin', '2002-08-08 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('赵云', '男', 4, 1, '2005-12-19', 7, 15000, 6000, 'zhaoyun@shuguo.com', NULL, 'Admin', '2005-12-19 10:00:00', 'Admin', '2006-12-31 10:00:00');
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('廖化', '男', 4, 9, '2009-02-17', 8, 6500, NULL, 'liaohua@shuguo.com', NULL, 'Admin', '2009-02-17 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('关平', '男', 4, 9, '2011-07-24', 8, 6800, NULL, 'guanping@shuguo.com', NULL, 'Admin', '2011-07-24 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('赵氏', '女', 4, 9, '2011-11-10', 8, 6600, NULL, 'zhaoshi@shuguo.com', NULL, 'Admin', '2011-11-10 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('关兴', '男', 4, 9, '2011-07-30', 8, 7000, NULL, 'guanxing@shuguo.com', NULL, 'Admin', '2011-07-30 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('张苞', '男', 4, 9, '2012-05-31', 8, 6500, NULL, 'zhangbao@shuguo.com', NULL, 'Admin', '2012-05-31 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('赵统', '男', 4, 9, '2012-05-03', 8, 6000, NULL, 'zhaotong@shuguo.com', NULL, 'Admin', '2012-05-03 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('周仓', '男', 4, 9, '2010-02-20', 8, 8000, NULL, 'zhoucang@shuguo.com', NULL, 'Admin', '2010-02-20 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('马岱', '男', 4, 9, '2014-09-16', 8, 5800, NULL, 'madai@shuguo.com', NULL, 'Admin', '2014-09-16 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('法正', '男', 5, 2, '2017-04-09', 9, 10000, 5000, 'fazheng@shuguo.com', NULL, 'Admin', '2017-04-09 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('庞统', '男', 5, 18, '2017-06-06', 10, 4100, 2000, 'pangtong@shuguo.com', NULL, 'Admin', '2017-06-06 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('蒋琬', '男', 5, 18, '2018-01-28', 10, 4000, 1500, 'jiangwan@shuguo.com', NULL, 'Admin', '2018-01-28 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('黄权', '男', 5, 18, '2018-03-14', 10, 4200, NULL, 'huangquan@shuguo.com', NULL, 'Admin', '2018-03-14 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('糜竺', '男', 5, 18, '2018-03-27', 10, 4300, NULL, 'mizhu@shuguo.com', NULL, 'Admin', '2018-03-27 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('邓芝', '男', 5, 18, '2018-11-11', 10, 4000, NULL, 'dengzhi@shuguo.com', NULL, 'Admin', '2018-11-11 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('简雍', '男', 5, 18, '2019-05-11', 10, 4800, NULL, 'jianyong@shuguo.com', NULL, 'Admin', '2019-05-11 10:00:00', NULL, NULL);
INSERT INTO employee(emp_name, sex, dept_id, manager, hire_date, job_id, salary, bonus, email, comments, create_by, create_ts, update_by, update_ts) VALUES ('孙乾', '男', 5, 18, '2018-10-09', 10, 4700, NULL, 'sunqian@shuguo.com', NULL, 'Admin', '2018-10-09 10:00:00', NULL, NULL);
INSERT INTO job_history(emp_id, dept_id, job_id, start_date, end_date) VALUES (9, 4, 8, '2005-12-19', '2006-12-31');sql
-- 删除示例表
DROP TABLE job_history;
DROP TABLE employee;
DROP TABLE department;
DROP TABLE job;员工表 employee 中存储了员工的各种信息，包括员工编号、姓名以及员工经理的编号。该公司的组织结构如下图所示，其中 "刘备" 没有上级，他的经理字段 manager 为空。
利用递归 CTE 生成一个组织结构图，显示每个员工从上到下的管理路径，SQL 语句如下所示：
sql
WITH RECURSIVE employee_path AS (SELECT emp_id, emp_name, emp_name AS path
FROM employee WHERE manager IS NULL
UNION ALL
SELECT e.emp_id, e.emp_name, CONCAT(ep.path, '->', e.emp_name)
FROM employee e
JOIN employee_path ep ON e.manager = ep.emp_id)
SELECT emp_id, emp_name, path
FROM employee_path
ORDER BY emp_id;其中，employee_path 是一个递归 CTE，初始化查询语句用于查找上级经理为空的员工，也就是 "刘备"；path 字段用于保存从上到下的管理路径。查询结果如下所示：
sh
+--------+----------+------------------------+
| emp_id | emp_name | path |
+--------+----------+------------------------+
| 1 | 刘备 | 刘备 |
| 2 | 关羽 | 刘备->关羽 |
| 3 | 张飞 | 刘备->张飞 |
| 4 | 诸葛亮 | 刘备->诸葛亮 |
| 5 | 黄忠 | 刘备->诸葛亮->黄忠 |
| 6 | 魏延 | 刘备->诸葛亮->魏延 |
| 7 | 孙尚香 | 刘备->孙尚香 |
| 8 | 孙丫鬟 | 刘备->孙尚香->孙丫鬟 |
| 9 | 赵云 | 刘备->赵云 |
| 10 | 廖化 | 刘备->赵云->廖化 |
| 11 | 关平 | 刘备->赵云->关平 |
| 12 | 赵氏 | 刘备->赵云->赵氏 |
| 13 | 关兴 | 刘备->赵云->关兴 |
| 14 | 张苞 | 刘备->赵云->张苞 |
| 15 | 赵统 | 刘备->赵云->赵统 |
| 16 | 周仓 | 刘备->赵云->周仓 |
| 17 | 马岱 | 刘备->赵云->马岱 |
| 18 | 法正 | 刘备->关羽->法正 |
| 19 | 庞统 | 刘备->关羽->法正->庞统 |
| 20 | 蒋琬 | 刘备->关羽->法正->蒋琬 |
| 21 | 黄权 | 刘备->关羽->法正->黄权 |
| 22 | 糜竺 | 刘备->关羽->法正->糜竺 |
| 23 | 邓芝 | 刘备->关羽->法正->邓芝 |
| 24 | 简雍 | 刘备->关羽->法正->简雍 |
| 25 | 孙乾 | 刘备->关羽->法正->孙乾 |
+--------+----------+------------------------+如果说想对上面的查询限制层级，只查询比 "刘备" 低两级之内的员工信息，也就是不查询 "法正" 的下级员工，该如何实现？定义一个 depth 字段，初始值为0，每递归一次加一，当 depth >= 2 的时候终止递归即可。
sql
WITH RECURSIVE employee_path AS (SELECT emp_id, emp_name, emp_name AS path, 0 AS depth
FROM employee
WHERE manager IS NULL
UNION ALL
SELECT e.emp_id, e.emp_name, CONCAT(ep.path, '->', e.emp_name), ep.depth + 1
FROM employee e
JOIN employee_path ep on ep.emp_id = e.manager
WHERE ep.depth < 2)
SELECT emp_id, emp_name, path
FROM employee_path
ORDER BY emp_id;查询结果如下所示：
sh
+--------+----------+----------------------+
| emp_id | emp_name | path |
+--------+----------+----------------------+
| 1 | 刘备 | 刘备 |
| 2 | 关羽 | 刘备->关羽 |
| 3 | 张飞 | 刘备->张飞 |
| 4 | 诸葛亮 | 刘备->诸葛亮 |
| 5 | 黄忠 | 刘备->诸葛亮->黄忠 |
| 6 | 魏延 | 刘备->诸葛亮->魏延 |
| 7 | 孙尚香 | 刘备->孙尚香 |
| 8 | 孙丫鬟 | 刘备->孙尚香->孙丫鬟 |
| 9 | 赵云 | 刘备->赵云 |
| 10 | 廖化 | 刘备->赵云->廖化 |
| 11 | 关平 | 刘备->赵云->关平 |
| 12 | 赵氏 | 刘备->赵云->赵氏 |
| 13 | 关兴 | 刘备->赵云->关兴 |
| 14 | 张苞 | 刘备->赵云->张苞 |
| 15 | 赵统 | 刘备->赵云->赵统 |
| 16 | 周仓 | 刘备->赵云->周仓 |
| 17 | 马岱 | 刘备->赵云->马岱 |
| 18 | 法正 | 刘备->关羽->法正 |
+--------+----------+----------------------+案例二：社交网络关系
脚本，使用 CREATE_TABLE.sql 创建表结构，LOAD_DATA.sql 生成初始化数据。
sql
-- 用户表t_user
CREATE TABLE t_user(
user_id INTEGER PRIMARY KEY,
user_name VARCHAR(50) NOT NULL
);
-- 好友关系表t_friend
CREATE TABLE t_friend(
user_id INTEGER NOT NULL,
friend_id INTEGER NOT NULL,
PRIMARY KEY (user_id, friend_id)
);
-- 粉丝表t_follower
CREATE TABLE t_follower(
user_id INTEGER NOT NULL,
follower_id INTEGER NOT NULL,
PRIMARY KEY (user_id, follower_id)
);
-- 关注表t_followed
CREATE TABLE t_followed(
user_id INTEGER NOT NULL,
followed_id INTEGER NOT NULL,
PRIMARY KEY (user_id, followed_id)
);sql
-- 生成初始化数据
INSERT INTO t_user VALUES(1, '刘一');
INSERT INTO t_user VALUES(2, '陈二');
INSERT INTO t_user VALUES(3, '张三');
INSERT INTO t_user VALUES(4, '李四');
INSERT INTO t_user VALUES(5, '王五');
INSERT INTO t_user VALUES(6, '赵六');
INSERT INTO t_user VALUES(7, '孙七');
INSERT INTO t_user VALUES(8, '周八');
INSERT INTO t_user VALUES(9, '吴九');
INSERT INTO t_friend VALUES(1, 2);
INSERT INTO t_friend VALUES(2, 1);
INSERT INTO t_friend VALUES(1, 3);
INSERT INTO t_friend VALUES(3, 1);
INSERT INTO t_friend VALUES(1, 4);
INSERT INTO t_friend VALUES(4, 1);
INSERT INTO t_friend VALUES(1, 7);
INSERT INTO t_friend VALUES(7, 1);
INSERT INTO t_friend VALUES(1, 8);
INSERT INTO t_friend VALUES(8, 1);
INSERT INTO t_friend VALUES(2, 3);
INSERT INTO t_friend VALUES(3, 2);
INSERT INTO t_friend VALUES(2, 5);
INSERT INTO t_friend VALUES(5, 2);
INSERT INTO t_friend VALUES(3, 4);
INSERT INTO t_friend VALUES(4, 3);
INSERT INTO t_friend VALUES(4, 6);
INSERT INTO t_friend VALUES(6, 4);
INSERT INTO t_friend VALUES(5, 8);
INSERT INTO t_friend VALUES(8, 5);
INSERT INTO t_friend VALUES(7, 8);
INSERT INTO t_friend VALUES(8, 7);
INSERT INTO t_follower VALUES(1, 2);
INSERT INTO t_follower VALUES(1, 3);
INSERT INTO t_follower VALUES(1, 4);
INSERT INTO t_follower VALUES(1, 7);
INSERT INTO t_follower VALUES(2, 3);
INSERT INTO t_follower VALUES(3, 4);
INSERT INTO t_follower VALUES(4, 1);
INSERT INTO t_follower VALUES(5, 2);
INSERT INTO t_follower VALUES(5, 8);
INSERT INTO t_follower VALUES(6, 4);
INSERT INTO t_follower VALUES(7, 8);
INSERT INTO t_follower VALUES(8, 1);
INSERT INTO t_follower VALUES(8, 7);
INSERT INTO t_followed VALUES(1, 4);
INSERT INTO t_followed VALUES(1, 8);
INSERT INTO t_followed VALUES(2, 1);
INSERT INTO t_followed VALUES(2, 5);
INSERT INTO t_followed VALUES(3, 1);
INSERT INTO t_followed VALUES(3, 2);
INSERT INTO t_followed VALUES(4, 1);
INSERT INTO t_followed VALUES(4, 3);
INSERT INTO t_followed VALUES(4, 6);
INSERT INTO t_followed VALUES(7, 1);
INSERT INTO t_followed VALUES(7, 8);
INSERT INTO t_followed VALUES(8, 5);
INSERT INTO t_followed VALUES(8, 7);在本节中，分析一下社交软件是如何建立网络关系的。常见的社交网络关系主要分为两类：
- 好友关系。在微信、Facebook 等软件中，两个用户可以相互加为好友，这样用户就可以和朋友、同事、同学以及周围的人保持互动交流。
- 粉丝关注。在微博、知乎等软件中，用户可以通过 "关注" 成为其他人的粉丝，了解他们/她们的最新动态。关注可以是单向的，两个用户之间也可以互相关注。
数据结构
社交网络是一个复杂的非线行结构，通常使用图（Graph）这种数据结构进行表示。
对于好友这种关系，每个用户是一个顶点（Vertex），两个用户相互加为好友就会在两者之间建立一条边（Edge）。如下图所示，是一个简单的好友关系示意图：
显然，好友关系是一种无向图（Undirected Graph），不会存在 A 是 B 的好友而 B 不是 A 的好友的情况。另外，一个用户由多少好友，连接到该顶点的边就有多少条。这个也叫作顶点的度（Degree），如上图所示，"刘一" 的度为 5（微信中的好友数量）。
而粉丝关系需要使用有向图（Directed Graph）表示。因为关注是单向关联，A 关注了B，但是 B 不一定关注 A。如下图所示，是一个简单的粉丝关系示意图。
如果 A 关注了 B，就会存在一条从 A 到 B 的带箭头的边。上图中的 "刘一" 关注了 "周八"，同时 "刘一" 和 "李四" 相互关注。对于有向图而言，度又分为入度（In-Degree）和出度（Out-Degree）。入度表示有多少条边指向该顶点，出度表示有多少条边是以该顶点为起点的。"刘一" 的入度为4（微博的粉丝数），出度为2（微博关注的人数）。
好友关系分析
好友列表
微信中的通讯录就是用户的好友列表。如下 SQL 语句用于查找 "王五" 的好友列表：
sql
SELECT u.user_id '好友编号', u.user_name '好友姓名'
FROM t_friend f
JOIN t_user u ON f.friend_id = u.user_id AND f.user_id = 5;查询结果如下所示："王五" 有两个好友，分别是 "陈二" 和 "周八"。
sh
+----------+----------+
| 好友编号 | 好友姓名 |
+----------+----------+
| 2 | 陈二 |
| 8 | 周八 |
+----------+----------+查看共同好友
还可以通过好友关系表获取更多的关联信息。如下 SQL 语句用于查找 "张三" 和 "李四" 的共同好友：
sql
WITH f1 AS (SELECT friend_id
FROM t_friend
WHERE user_id = 3),
f2 AS (SELECT friend_id
FROM t_friend
WHERE user_id = 4)
SELECT u.user_id '好友编号', u.user_name '好友姓名'
FROM f1
JOIN f2 ON f1.friend_id = f2.friend_id
JOIN t_user u ON u.user_id = f1.friend_id;在查询中定义了两个 CTE，f1 表示 "张三" 的好友，f2 表示 "李四" 的好友，主查询语句通过连接这两个结果集返回了他们的共同好友。查询结果如下所示：
sh
+----------+----------+
| 好友编号 | 好友姓名 |
+----------+----------+
| 1 | 刘一 |
+----------+----------+可能认识的人
社交软件通常实现了推荐好友的功能：一方面，其可能读取了该用户的手机通讯录，并找到已经在系统中注册，但不属于该用户好友的用户进行推荐；另一方面，其会找出和该用户不是好友，但是有共同好友的用户，即好友的好友，进行推荐。如下查找可以推荐给 "陈二" 的用户：
sql
WITH friend(fid) AS (SELECT friend_id
FROM t_friend
WHERE user_id = 2),
fof AS (SELECT f.friend_id
FROM t_friend f
JOIN friend ON f.user_id = friend.fid AND f.friend_id != 2)
SELECT u.user_id '好友编号', u.user_name '好友姓名', COUNT(*) '共同好友数量'
FROM fof
JOIN t_user u ON fof.friend_id = u.user_id
WHERE fof.friend_id NOT IN (SELECT fid FROM friend)
GROUP BY u.user_id, u.user_name;在查询中定义了两个 CTE，friend 代表了 "陈二" 的好友，fof 代表了 "陈二" 的好友的好友（排除了 "陈二" 自己）。主查询语句通过 WHERE 条件排除了 fof 中已经是 "陈二" 好友的用户，并且统计了被推荐的用户和 "陈二" 的共同好友数量。查询结果如下所示：
sh
+----------+----------+--------------+
| 好友编号 | 好友姓名 | 共同好友数量 |
+----------+----------+--------------+
| 4 | 李四 | 2 |
| 7 | 孙七 | 1 |
| 8 | 周八 | 2 |
+----------+----------+--------------+基于查询结果，可以向 "陈二" 推荐3个可能认识的人，并且告诉他和这些用户之间分别有几位共同好友。
好友关系链
在社会学中存在一个六度关系理论（Six Degrees Of Separation），该理论指的是，地球上的任意两个人都可以通过六层以内的关系链联系起来。以 "赵六" 和 "孙七" 为例，查找他们之间的好友关系链：
sql
WITH RECURSIVE relation(uid, fid, hops, path) AS (
SELECT user_id, friend_id, 0, CONCAT(user_id, '->', friend_id)
FROM t_friend WHERE user_id = 6
UNION ALL
SELECT r.uid, f.friend_id, r.hops + 1, CONCAT(r.path, '->', f.friend_id)
FROM t_friend f
JOIN relation r ON r.fid = f.user_id
WHERE INSTR(r.path, f.friend_id) = 0 AND r.hops < 6
)
SELECT * FROM relation WHERE fid = 7;其中，relation 是一个递归 CTE。初始化查询语句用于查找 "赵六" 的好友，第一次递归返回了 "赵六" 好友的好友，然后依次类推。将关系层数限制为小于 6，path 字段中存储了使用 "->" 分隔的关系链，INSTR 函数可以用于防止形成 A -> B -> A 这样的环路。查询结果如下所示：
sh
+------+------+------+------------------------+
| uid | fid | hops | path |
+------+------+------+------------------------+
| 6 | 7 | 2 | 6->4->1->7 |
| 6 | 7 | 3 | 6->4->1->8->7 |
| 6 | 7 | 3 | 6->4->3->1->7 |
| 6 | 7 | 4 | 6->4->3->1->8->7 |
| 6 | 7 | 4 | 6->4->3->2->1->7 |
| 6 | 7 | 5 | 6->4->1->2->5->8->7 |
| 6 | 7 | 5 | 6->4->3->2->1->8->7 |
| 6 | 7 | 5 | 6->4->3->2->5->8->7 |
| 6 | 7 | 6 | 6->4->1->3->2->5->8->7 |
| 6 | 7 | 6 | 6->4->3->1->2->5->8->7 |
| 6 | 7 | 6 | 6->4->3->2->5->8->1->7 |
+------+------+------+------------------------+"赵六" 和 "孙七" 之间最近的关系是，通过 "李四" 和 "刘一" 两个人进行联系。
粉丝关系分析
其中，1 表示仅为本人所关注的人，2 表示仅为本人的粉丝，3 表示互粉。
我的关注
如下 SQL 语句用于查找 "刘一" 关注了哪些用户：
sql
SELECT u.user_name '我的关注'
FROM t_followed f
JOIN t_user u ON f.followed_id = u.user_id
WHERE f.user_id = 1;"刘一" 关注了 "李四" 和 "周八"。查询结果如下所示：
sh
+----------+
| 我的关注 |
+----------+
| 李四 |
| 周八 |
+----------+共同关注
如下 SQL 语句用于查找和 "刘一" 关注了相同用户的其他用户：
sql
WITH cf(user1, user2, followed) AS (SELECT d.user_id, r.follower_id, d.followed_id
FROM t_followed d
JOIN t_follower r ON r.user_id = d.followed_id AND r.follower_id != d.user_id
WHERE d.user_id = 1)
SELECT u1.user_name '用户一', u2.user_name '用户二', u3.user_name '共同关注'
FROM cf
JOIN t_user u1 ON u1.user_id = cf.user1
JOIN t_user u2 ON u2.user_id = cf.user2
JOIN t_user u3 ON u3.user_id = cf.followed;其中，cf 代表了和 "刘一" 拥有共同关注用户的其他用户。主查询语句通过多个连接语句返回这些用户的姓名。查询结果如下所示：
sh
+--------+--------+----------+
| 用户一 | 用户二 | 共同关注 |
+--------+--------+----------+
| 刘一 | 孙七 | 周八 |
+--------+--------+----------+"刘一" 和 "孙七" 共同关注了 "周八"。
我的粉丝
如下 SQL 语句用于查找哪些用户是 "刘一" 的粉丝：
sql
SELECT u.user_name '我的粉丝'
FROM t_follower r
JOIN t_user u ON r.follower_id = u.user_id
WHERE r.user_id = 1;查询结果如下所示：
sh
+----------+
| 我的粉丝 |
+----------+
| 陈二 |
| 张三 |
| 李四 |
| 孙七 |
+----------+"刘一" 拥有4个粉丝。
互为粉丝
最后，如下 SQL 语句用于查找哪些用户之间互为粉丝，或者互相关注：
sql
WITH df(user1, user2) AS (SELECT r.user_id, r.follower_id
FROM t_followed d
JOIN t_follower r
ON d.user_id = r.user_id AND d.followed_id = r.follower_id AND
r.user_id < r.follower_id)
SELECT u1.user_name '用户一', u2.user_name '用户二'
FROM df
JOIN t_user u1 ON u1.user_id = df.user1
JOIN t_user u2 ON u2.user_id = df.user2;其中, df 代表了相互关注的两个用户。t_follower(粉丝表) 与 t_followed(关注表) 两表 JOIN 的连接条件如何获得？以用户 "刘一" 举例，
- 先获取 "刘一" 有哪些粉丝，然后再获取 "刘一" 所关注的人，推出两表连接的时候要是同一个用户 "刘一"，所以 r.user_id = d.user_id；
- 因为一个表为粉丝表，一个表为关注表，都是针对同一个用户的，所以此时只需要再判断该用户所拥有的粉丝是否同时是该用户所关注的人，推出条件 r.follower_id = d.followed_id。
主查询语句通过连接查询返回了这些用户的姓名。查询结果如下所示：
sh
+--------+--------+
| 用户一 | 用户二 |
+--------+--------+
| 刘一 | 李四 |
| 孙七 | 周八 |
+--------+--------+"刘一" 和 "李四" 互为粉丝，"孙七" 和 "周八" 互为粉丝。
案例三：地铁换乘路线
脚本，使用 CREATE_TABLE.sql 创建表结构，LOAD_DATA.sql 生成初始化数据。
sql
-- 创建地铁线路表
CREATE TABLE bj_subway(
station_id INT NOT NULL PRIMARY KEY,
line_name VARCHAR(20) NOT NULL,
station_name VARCHAR(50) NOT NULL,
next_station VARCHAR(50) NOT NULL,
direction VARCHAR(50) NOT NULL
);sql
-- 初始化数据，目前只有1号线、2号线和8号线的数据
INSERT INTO bj_subway VALUES (1,'1号线','苹果园','古城','苹果园—四惠东');
INSERT INTO bj_subway VALUES (2,'1号线','古城','八角游乐园','苹果园—四惠东');
INSERT INTO bj_subway VALUES (3,'1号线','八角游乐园','八宝山','苹果园—四惠东');
INSERT INTO bj_subway VALUES (4,'1号线','八宝山','玉泉路','苹果园—四惠东');
INSERT INTO bj_subway VALUES (5,'1号线','玉泉路','五棵松','苹果园—四惠东');
INSERT INTO bj_subway VALUES (6,'1号线','五棵松','万寿路','苹果园—四惠东');
INSERT INTO bj_subway VALUES (7,'1号线','万寿路','公主坟','苹果园—四惠东');
INSERT INTO bj_subway VALUES (8,'1号线','公主坟','军事博物馆','苹果园—四惠东');
INSERT INTO bj_subway VALUES (9,'1号线','军事博物馆','木樨地','苹果园—四惠东');
INSERT INTO bj_subway VALUES (10,'1号线','木樨地','南礼士路','苹果园—四惠东');
INSERT INTO bj_subway VALUES (11,'1号线','南礼士路','复兴门','苹果园—四惠东');
INSERT INTO bj_subway VALUES (12,'1号线','复兴门','西单','苹果园—四惠东');
INSERT INTO bj_subway VALUES (13,'1号线','西单','天安门西','苹果园—四惠东');
INSERT INTO bj_subway VALUES (14,'1号线','天安门西','天安门东','苹果园—四惠东');
INSERT INTO bj_subway VALUES (15,'1号线','天安门东','王府井','苹果园—四惠东');
INSERT INTO bj_subway VALUES (16,'1号线','王府井','东单','苹果园—四惠东');
INSERT INTO bj_subway VALUES (17,'1号线','东单','建国门','苹果园—四惠东');
INSERT INTO bj_subway VALUES (18,'1号线','建国门','永安里','苹果园—四惠东');
INSERT INTO bj_subway VALUES (19,'1号线','永安里','国贸','苹果园—四惠东');
INSERT INTO bj_subway VALUES (20,'1号线','国贸','大望路','苹果园—四惠东');
INSERT INTO bj_subway VALUES (21,'1号线','大望路','四惠','苹果园—四惠东');
INSERT INTO bj_subway VALUES (22,'1号线','四惠','四惠东','苹果园—四惠东');
INSERT INTO bj_subway VALUES (23,'1号线','四惠东','四惠','四惠东—苹果园');
INSERT INTO bj_subway VALUES (24,'1号线','四惠','大望路','四惠东—苹果园');
INSERT INTO bj_subway VALUES (25,'1号线','大望路','国贸','四惠东—苹果园');
INSERT INTO bj_subway VALUES (26,'1号线','国贸','永安里','四惠东—苹果园');
INSERT INTO bj_subway VALUES (27,'1号线','永安里','建国门','四惠东—苹果园');
INSERT INTO bj_subway VALUES (28,'1号线','建国门','东单','四惠东—苹果园');
INSERT INTO bj_subway VALUES (29,'1号线','东单','王府井','四惠东—苹果园');
INSERT INTO bj_subway VALUES (30,'1号线','王府井','天安门东','四惠东—苹果园');
INSERT INTO bj_subway VALUES (31,'1号线','天安门东','天安门西','四惠东—苹果园');
INSERT INTO bj_subway VALUES (32,'1号线','天安门西','西单','四惠东—苹果园');
INSERT INTO bj_subway VALUES (33,'1号线','西单','复兴门','四惠东—苹果园');
INSERT INTO bj_subway VALUES (34,'1号线','复兴门','南礼士路','四惠东—苹果园');
INSERT INTO bj_subway VALUES (35,'1号线','南礼士路','木樨地','四惠东—苹果园');
INSERT INTO bj_subway VALUES (36,'1号线','木樨地','军事博物馆','四惠东—苹果园');
INSERT INTO bj_subway VALUES (37,'1号线','军事博物馆','公主坟','四惠东—苹果园');
INSERT INTO bj_subway VALUES (38,'1号线','公主坟','万寿路','四惠东—苹果园');
INSERT INTO bj_subway VALUES (39,'1号线','万寿路','五棵松','四惠东—苹果园');
INSERT INTO bj_subway VALUES (40,'1号线','五棵松','玉泉路','四惠东—苹果园');
INSERT INTO bj_subway VALUES (41,'1号线','玉泉路','八宝山','四惠东—苹果园');
INSERT INTO bj_subway VALUES (42,'1号线','八宝山','八角游乐园','四惠东—苹果园');
INSERT INTO bj_subway VALUES (43,'1号线','八角游乐园','古城','四惠东—苹果园');
INSERT INTO bj_subway VALUES (44,'1号线','古城','苹果园','四惠东—苹果园');
INSERT INTO bj_subway VALUES (45,'2号线','西直门','积水潭','外环');
INSERT INTO bj_subway VALUES (46,'2号线','积水潭','鼓楼大街','外环');
INSERT INTO bj_subway VALUES (47,'2号线','鼓楼大街','安定门','外环');
INSERT INTO bj_subway VALUES (48,'2号线','安定门','雍和宫','外环');
INSERT INTO bj_subway VALUES (49,'2号线','雍和宫','东直门','外环');
INSERT INTO bj_subway VALUES (50,'2号线','东直门','东四十条','外环');
INSERT INTO bj_subway VALUES (51,'2号线','东四十条','朝阳门','外环');
INSERT INTO bj_subway VALUES (52,'2号线','朝阳门','建国门','外环');
INSERT INTO bj_subway VALUES (53,'2号线','建国门','北京站','外环');
INSERT INTO bj_subway VALUES (54,'2号线','北京站','崇文门','外环');
INSERT INTO bj_subway VALUES (55,'2号线','崇文门','前门','外环');
INSERT INTO bj_subway VALUES (56,'2号线','前门','和平门','外环');
INSERT INTO bj_subway VALUES (57,'2号线','和平门','宣武门','外环');
INSERT INTO bj_subway VALUES (58,'2号线','宣武门','长椿街','外环');
INSERT INTO bj_subway VALUES (59,'2号线','长椿街','复兴门','外环');
INSERT INTO bj_subway VALUES (60,'2号线','复兴门','阜成门','外环');
INSERT INTO bj_subway VALUES (61,'2号线','阜成门','车公庄','外环');
INSERT INTO bj_subway VALUES (62,'2号线','车公庄','西直门','外环');
INSERT INTO bj_subway VALUES (63,'2号线','车公庄','阜成门','内环');
INSERT INTO bj_subway VALUES (64,'2号线','阜成门','复兴门','内环');
INSERT INTO bj_subway VALUES (65,'2号线','复兴门','长椿街','内环');
INSERT INTO bj_subway VALUES (66,'2号线','长椿街','宣武门','内环');
INSERT INTO bj_subway VALUES (67,'2号线','宣武门','和平门','内环');
INSERT INTO bj_subway VALUES (68,'2号线','和平门','前门','内环');
INSERT INTO bj_subway VALUES (69,'2号线','前门','崇文门','内环');
INSERT INTO bj_subway VALUES (70,'2号线','崇文门','北京站','内环');
INSERT INTO bj_subway VALUES (71,'2号线','北京站','建国门','内环');
INSERT INTO bj_subway VALUES (72,'2号线','建国门','朝阳门','内环');
INSERT INTO bj_subway VALUES (73,'2号线','朝阳门','东四十条','内环');
INSERT INTO bj_subway VALUES (74,'2号线','东四十条','东直门','内环');
INSERT INTO bj_subway VALUES (75,'2号线','东直门','雍和宫','内环');
INSERT INTO bj_subway VALUES (76,'2号线','雍和宫','安定门','内环');
INSERT INTO bj_subway VALUES (77,'2号线','安定门','鼓楼大街','内环');
INSERT INTO bj_subway VALUES (78,'2号线','鼓楼大街','积水潭','内环');
INSERT INTO bj_subway VALUES (79,'2号线','积水潭','西直门','内环');
INSERT INTO bj_subway VALUES (80,'2号线','西直门','车公庄','外环');
INSERT INTO bj_subway VALUES (81, '8号线', '朱辛庄', '育知路', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (82, '8号线', '育知路', '平西府', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (83, '8号线', '平西府', '回龙观东大街', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (84, '8号线', '回龙观东大街', '霍营', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (85, '8号线', '霍营', '育新', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (86, '8号线', '育新', '西小口', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (87, '8号线', '西小口', '永泰庄', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (88, '8号线', '永泰庄', '林萃桥', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (89, '8号线', '林萃桥', '森林公园南门', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (90, '8号线', '森林公园南门', '奥林匹克公园', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (91, '8号线', '奥林匹克公园', '奥体中心', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (92, '8号线', '奥体中心', '北土城', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (93, '8号线', '北土城', '安华桥', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (94, '8号线', '安华桥', '安德里北街', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (95, '8号线', '安德里北街', '鼓楼大街', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (96, '8号线', '鼓楼大街', '什刹海', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (97, '8号线', '什刹海', '南锣鼓巷', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (98, '8号线', '南锣鼓巷', '中国美术馆', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (99, '8号线', '中国美术馆', '金鱼胡同', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (100, '8号线', '金鱼胡同', '王府井', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (101, '8号线', '王府井', '前门', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (102, '8号线', '前门', '珠市口', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (103, '8号线', '珠市口', '天桥', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (104, '8号线', '天桥', '永定门外', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (105, '8号线', '永定门外', '木樨园', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (106, '8号线', '木樨园', '海户屯', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (107, '8号线', '海户屯', '大红门', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (108, '8号线', '大红门', '大红门南', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (109, '8号线', '大红门南', '和义', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (110, '8号线', '和义', '东高地', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (111, '8号线', '东高地', '火箭万源', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (112, '8号线', '火箭万源', '五福堂', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (113, '8号线', '五福堂', '德茂', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (114, '8号线', '德茂', '瀛海', '朱辛庄-瀛海');
INSERT INTO bj_subway VALUES (115, '8号线', '瀛海', '德茂', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (116, '8号线', '德茂', '五福堂', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (117, '8号线', '五福堂', '火箭万源', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (118, '8号线', '火箭万源', '东高地', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (119, '8号线', '东高地', '和义', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (120, '8号线', '和义', '大红门南', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (121, '8号线', '大红门南', '大红门', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (122, '8号线', '大红门', '海户屯', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (123, '8号线', '海户屯', '木樨园', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (124, '8号线', '木樨园', '永定门外', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (125, '8号线', '永定门外', '天桥', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (126, '8号线', '天桥', '珠市口', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (127, '8号线', '珠市口', '前门', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (128, '8号线', '前门', '王府井', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (129, '8号线', '王府井', '金鱼胡同', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (130, '8号线', '金鱼胡同', '中国美术馆', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (131, '8号线', '中国美术馆', '南锣鼓巷', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (132, '8号线', '南锣鼓巷', '什刹海', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (133, '8号线', '什刹海', '鼓楼大街', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (134, '8号线', '鼓楼大街', '安德里北街', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (135, '8号线', '安德里北街', '安华桥', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (136, '8号线', '安华桥', '北土城', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (137, '8号线', '北土城', '奥体中心', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (138, '8号线', '奥体中心', '奥林匹克公园', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (139, '8号线', '奥林匹克公园', '森林公园南门', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (140, '8号线', '森林公园南门', '林萃桥', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (141, '8号线', '林萃桥', '永泰庄', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (142, '8号线', '永泰庄', '西小口', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (143, '8号线', '西小口', '育新', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (144, '8号线', '育新', '霍营', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (145, '8号线', '霍营', '回龙观东大街', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (146, '8号线', '回龙观东大街', '平西府', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (147, '8号线', '平西府', '育知路', '瀛海-朱辛庄');
INSERT INTO bj_subway VALUES (148, '8号线', '育知路', '朱辛庄', '瀛海-朱辛庄');北京地铁，如下 SQL 语句用于查找“王府井”到“积水潭”的换乘路线：
sql
WITH RECURSIVE transfer ( start_station, stop_station, stops, paths ) AS (
SELECT
station_name,
next_station,
1,
CAST(
CONCAT( line_name, station_name, '->', line_name, next_station ) AS CHAR ( 1000 ))
FROM
bj_subway
WHERE
station_name = '王府井' UNION ALL
SELECT
t.start_station,
s.next_station,
stops + 1,
CONCAT( t.paths, '->', s.line_name, s.next_station )
FROM
transfer t
JOIN bj_subway s ON t.stop_station = s.station_name
AND INSTR( t.paths, s.next_station ) = 0
) SELECT
*
FROM
transfer
WHERE
stop_station = '积水潭';查询结果如下所示：
sh
+---------------+--------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| start_station | stop_station | stops | paths |
+---------------+--------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| 王府井 | 积水潭 | 6 | 8号线王府井->8号线金鱼胡同->8号线中国美术馆->8号线南锣鼓巷->8号线什刹海->8号线 鼓楼大街->2号线积水潭 |
| 王府井 | 积水潭 | 8 | 1号线王府井->1号线天安门东->1号线天安门西->1号线西单->1号线复兴门->2号线阜成门->2号线车公庄->2号线西直门->2号线积水潭 |
| 王府井 | 积水潭 | 9 | 8号线王府井->8号线前门->2号线和平门->2号线宣武门->2号线长椿街->2号线复兴门->2号线阜成门->2号线车公庄->2号线西直门->2号线积水潭 |
| 王府井 | 积水潭 | 9 | 1号线王府井->1号线东单->1号线建国门->2号线朝阳门->2号线东四十条->2号线东直门->2号线雍和宫->2号线安定门->2号线鼓楼大街->2号线积水潭 |
| 王府井 | 积水潭 | 11 | 8号线王府井->8号线前门->2号线崇文门->2号线北京站->2号线建国门->2号线朝阳门->2号线东四十条->2号线东直门->2号线雍和宫->2号线安定门->2号线鼓楼大街->2号线积水潭 |
| 王府井 | 积水潭 | 13 | 1号线王府井->1号线东单->1号线建国门->2号线北京站->2号线崇文门->2号线前门->2号线和平门->2号线宣武门->2号线长椿街->2号线复兴门->2号线阜成门->2号线车公庄->2号线西直门->2号线积水潭 |
| 王府井 | 积水潭 | 18 | 1号线王府井->1号线天安门东->1号线天安门西->1号线西单->1号线复兴门->2号线长椿街->2号线宣武门->2号线和平门->2号线前门->2号线崇文门->2号线北京站->2号线建国门->2号线朝阳门->2号线东四十条->2号线东直门->2 号线雍和宫->2号线安定门->2号线鼓楼大街->2号线积水潭 |
| 王府井 | 积水潭 | 22 | 8号线王府井->8号线金鱼胡同->8号线中国美术馆->8号线南锣鼓巷->8号线什刹海->8号线 鼓楼大街->2号线安定门->2号线雍和宫->2号线东直门->2号线东四十条->2号线朝阳门->2号线建国门->2号线北京站->2号线崇文门->2号 线前门->2号线和平门->2号线宣武门->2号线长椿街->2号线复兴门->2号线阜成门->2号线车公庄->2号线西直门->2号线积水潭 |
+---------------+--------------+-------+---------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+