-- 创建机构表
CREATE TABLE company (
  company_id INT PRIMARY KEY,
  company_name VARCHAR(100),
  company_former_name VARCHAR(100),
	tel VARCHAR(100)
);
-- 初始化机构数据
INSERT INTO company (company_id, company_name, company_former_name, tel) VALUES
(1, '机构A', NULL, '13700011234'),
(2, '机构B', '机构A', '13800011234'),
(3, '机构C', '机构B', '13800011234'),
(4, '机构D', '机构D', '13800011234'),
(101, '机构X', NULL, '13700011234'),
(102, '机构Y', '机构X', '13800019876'),
(103, '机构Z', '机构Y', '13800019876'),
(1000, '机构O', NULL, '182111111234');
-- 基本需求：给定一个机构（名称），查询该机构使用过的所有名称
-- eg. 给定"机构B"返回："机构A"、"机构B"、"机构C"、"机构D"

CONNECT BY（ORACLE）

• 递归查询SQL

• Oracle、达梦等数据库支持

• 基本语法：

SELECT 
[COLUMNS] 
FROM TABLE_NAME
[START WITH condition]
[NOCYCLE]
[PRIOR] COLUMN = [PRIOR] COLUMN
-- NO CYCLE: 防止循环
-- PRIOR: 优先顺序，简单理解就说明是查找子项还是查找父项

SELECT * FROM company
START WITH company_name = '机构B' NOCYCLE PRIOR company_name =   company_former_name
UNION
SELECT * FROM company
START WITH company_name = '机构B' NOCYCLE company_name =  PRIOR company_former_name;

WITH RECURSIVE (MySQL8.0)

• CTE的递归方式

• MySQL

CTE 是 Common Table Expressions 的缩写，是一种临时命名的结果集，它可以在 SQL 查询中被引用。CTE 可以提高查询的可读性和可维护性，特别是在复杂的查询中。CTE 在 MySQL 中使用 WITH 关键字定义，然后可以在查询中引用它们。

当你在MySQL中使用CTE时，你可以按照以下语法格式：

WITH cte_name AS (
    -- 在这里定义你的CTE查询
    SELECT column1, column2
    FROM your_table
    WHERE condition
)
-- 在这里引用你的CTE
SELECT *
FROM cte_name;

WITH RECURSIVE temp1 AS (
	-- 基础数据：机构B的记录
	SELECT *
	FROM company
	WHERE company_name='机构B'
	-- 机构B及之后的
	UNION ALL
	SELECT c.*
	FROM company c
	INNER JOIN temp1 ON temp1.company_name = c.company_former_name
),
temp2 AS (
	-- 基础数据：无曾用名的记录
	SELECT *
	FROM company
	WHERE  company_name='机构B'
	-- 机构B及之前的
	UNION ALL
	SELECT c.*
	FROM company c
	INNER JOIN temp2 ON temp2.company_former_name = c.company_name
)
SELECT * FROM temp1
UNION
SELECT * FROM temp2
ORDER BY company_id;

注意

1. 递归用不好的话容易导致无限递归，从而造成内存溢出或者IO超载

   1. 在使用CONNECT BY的时候如果不加NOCYCLE 有的数据可能会导致递归循环异常

   2. 在使用WITH RECURSIVE的时候，在两个temp中Union ALL 的字段不一致的话，直接把服务器搞死了）

2. 数据量大时影响性能