如何使用递归CTE编写SQL查询未知层级的汽车全部零部件清单

首先我们先明确递归CTE的核心结构，它由锚点查询和递归查询两部分组成，锚点负责定位递归的起点，递归部分负责循环关联获取下级子部件，直到没有匹配的子节点自动终止。
假设你的表名为car_components，对应列名分别为main_id、name、c_id、component，可以直接使用下面的查询语句：

WITH RECURSIVE component_list AS (
    -- 锚点查询：取最顶层（Car所属）的一级部件作为递归起点
    SELECT component, c_id
    FROM car_components
    WHERE main_id = 1 -- 对应Car的main ID
    
    UNION ALL
    
    -- 递归查询：关联原表获取当前部件的下级子部件
    SELECT cc.component, cc.c_id
    FROM car_components cc
    INNER JOIN component_list cl ON cc.main_id = cl.c_id
)
-- 最终提取所有零部件名称
SELECT component AS Components
FROM component_list;

逻辑说明 #

• 锚点查询首先会拿到Engine、Transmission、Body三个一级部件
• 递归查询每次会用上一轮查询得到的部件ID（c_id）匹配原表的main_id，比如第一轮递归用Engine的c_id=11匹配到它的三个子部件Crankshaft、Piston、Valve，用Transmission的c_id=12匹配到它的三个子部件，Body没有对应子部件就不会返回新数据
• 当递归查询没有新的匹配结果时，整个递归自动终止
  查询返回的结果和你期望的零部件清单完全一致。

内容的提问来源于stack exchange，提问作者MrJack0209