mysql 中with的用法（3）

有表（tb）,数据如下：

请用SQL,生成如下的样式：

一、建表

CREATE TABLE `tb` (
  `id` varchar(3) DEFAULT NULL,
  `pid` varchar(3) DEFAULT NULL,
  `name` varchar(64) DEFAULT NULL
) 
1
2
3
4
5
6

INSERT INTO tb (id, pid, name) VALUES('002', '0', '浙江省');
INSERT INTO tb (id, pid, name) VALUES('001', '0', '广东省');
INSERT INTO tb (id, pid, name) VALUES('003', '002', '衢州市');
INSERT INTO tb (id, pid, name) VALUES('004', '002', '杭州市');
INSERT INTO tb (id, pid, name) VALUES('005', '002', '湖州市');
INSERT INTO tb (id, pid, name) VALUES('006', '002', '嘉兴市');
INSERT INTO tb (id, pid, name) VALUES('007', '002', '宁波市');
INSERT INTO tb (id, pid, name) VALUES('008', '002', '绍兴市');
INSERT INTO tb (id, pid, name) VALUES('009', '002', '台州市');
INSERT INTO tb (id, pid, name) VALUES('010', '002', '温州市');
INSERT INTO tb (id, pid, name) VALUES('011', '002', '丽水市');
INSERT INTO tb (id, pid, name) VALUES('012', '002', '金华市');
INSERT INTO tb (id, pid, name) VALUES('013', '002', '舟山市');
INSERT INTO tb (id, pid, name) VALUES('014', '004', '上城区');
INSERT INTO tb (id, pid, name) VALUES('015', '004', '下城区');
INSERT INTO tb (id, pid, name) VALUES('016', '004', '拱墅区');
INSERT INTO tb (id, pid, name) VALUES('017', '004', '余杭区');
INSERT INTO tb (id, pid, name) VALUES('018', '011', '金东区');
INSERT INTO tb (id, pid, name) VALUES('019', '001', '广州市');
INSERT INTO tb (id, pid, name) VALUES('020', '001', '深圳市')
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

二、解决SQL：

第一种写法：

-- 创建一个递归公共表达式（CTE）来获取多层级行政区域数据
WITH RECURSIVE location_cte AS (
  -- 初始选择基础条件，即pid为0的记录，表示省级行政区域
  SELECT 
    id, 
    pid, 
    name,
    0 AS level  -- 初始层级为0
  FROM tb
  WHERE pid = 0
  
  -- 递归部分：联接递归表达式自身以获取下一级行政区域
  UNION ALL
  
  SELECT 
    tb.id, 
    tb.pid, 
    tb.name,
    location_cte.level + 1  -- 层级加1
  FROM tb
  JOIN location_cte ON tb.pid = location_cte.id
)

-- 最终查询：基于递归CTE获取所需的数据
SELECT 
  t1.name AS shen,  -- 选择省级行政区域的名称
  t2.name AS shi,   -- 选择市级行政区域的名称
  t3.name AS qu     -- 选择区级行政区域的名称
FROM location_cte t1  -- 使用递归CTE作为基础表t1，表示省级行政区域
LEFT JOIN location_cte t2 ON t1.id = t2.pid AND t2.pid <> 0  -- t1和t2自连接，获取市级行政区域数据
LEFT JOIN location_cte t3 ON t2.id = t3.pid  -- t2和t3自连接，获取区级行政区域数据
WHERE t1.pid = 0  -- 筛选条件：选择省级行政区域
ORDER BY t1.id, t2.id, t3.id;  -- 根据ID值排序结果

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
26
27
28
29
30
31
32
33
34

此SQL首先使用递归CTE从表中获取多层级行政区域数据，
然后基于这个CTE进行查询。在注释中，我解释了初始选择条件、递归部分和最终查询以及每个部分的作用。
1
2

第二种写法：

SELECT  -- 开始选择需要返回的字段  
t1.name AS shen,  -- 选择t1表中的name字段，并将其命名为shen  
t2.name AS shi,  -- 选择t2表中的name字段，并将其命名为shi  
t3.name AS qu  -- 选择t3表中的name字段，并将其命名为qu  
FROM tb t1  -- 从名为tb的表中选择数据，并将该表别名为t1  
LEFT JOIN tb t2 ON t1.id = t2.pid AND t2.pid <> 0  -- 使用左连接将t1表与t2表连接在一起，连接条件是t1.id等于t2.pid且t2.pid不等于0  
LEFT JOIN tb t3 ON t2.id = t3.pid  -- 使用左连接将t2表与t3表连接在一起，连接条件是t2.id等于t3.pid  
WHERE t1.pid = 0  -- 过滤条件，只选择pid字段等于0的记录  
ORDER BY t1.id, t2.id, t3.id;  -- 对返回的结果进行排序，排序顺序是先按照t1.id排序，然后按照t2.id排序，最后按照t3.id排序
1
2
3
4
5
6
7
8
9

展示效果：