%%sql 
with recursive X(id,name,depth) as (
        select airport_id,airport_name,0 from airport_list
        where airport_name='Dillingham, AK'
    union
        select end_node_id,airport_name,depth+1 from airport_link,airport_list,X
        where start_node_id=id
        and end_node_id=airport_id
        and depth<=3)

select min(depth)
from X
where name='Gainesville, FL'

这段 SQL 代码使用 WITH RECURSIVE 查询从 "Dillingham, AK" 机场到达 "Gainesville, FL" 机场所需的最少转机次数。下面逐行详细解释：

整体逻辑

查询从 Dillingham, AK 出发，通过任意次数的航班连接，到达 Gainesville, FL 所需的最小转机次数。

注意：这里 depth 表示转机次数，不是航班次数：

• depth=0：起点机场（Dillingham, AK）

• depth=1：直飞到达（0次转机）

• depth=2：一次转机

• depth=3：两次转机

• depth=4：三次转机
  ...依此类推

逐行解释

第1行：定义递归公共表表达式（CTE）

with recursive X(id,name,depth) as (

• with recursive：定义递归公共表表达式

• X：CTE的名称

• (id, name, depth)：定义CTE的三个列：

  • id：机场ID    name：机场名称     depth：递归深度（这里表示转机次数）

第2-4行：初始查询（起点）

    select airport_id,airport_name,0 from airport_list
    where airport_name='Dillingham, AK'

• 初始行：找到起点机场

• airport_id,airport_name,0：

  • airport_id：机场ID

  • airport_name：机场名称

  • 0：初始深度为0（起点）

• where airport_name='Dillingham, AK'：限定起点为"Dillingham, AK"机场

初始结果示例：

id	name	depth
11336	Dillingham, AK	0

第5行：UNION ALL 连接递归部分

    union

• 连接初始查询和递归查询的结果

• 注意：这里使用的是 union（不是 union all），会自动去重

第6-9行：递归查询（扩展航班）

        select end_node_id,airport_name,depth+1 
        from airport_link,airport_list,X
        where start_node_id=id
        and end_node_id=airport_id
        and depth<=3

这是递归的核心部分：

1. 选择列：

   select end_node_id,airport_name,depth+1

   • end_node_id：航班的目的机场ID

   • airport_name：目的机场名称

   • depth+1：深度加1（转机次数增加）

2. FROM 子句：

   from airport_link,airport_list,X

   • 连接三个表：

     • airport_link：航班连接表 

     • airport_list：机场信息表

     • X：递归CTE自身（当前已找到的机场）

3. WHERE 条件：

   where start_node_id=id
     and end_node_id=airport_id
     and depth<=3

   • start_node_id=id：航班起点 = 当前CTE中的机场ID----从当前已找到的机场出发

   • end_node_id=airport_id：航班终点 = 机场表的ID----获取目的机场的信息

   • and depth<=3：关键的限制条件：只递归到深度3（即最多3次转机）----防止无限递归，也作为查询的深度限制

第10-11行：主查询 - 查找最小转机次数

select min(depth)
from X
where name='Gainesville, FL'

1. FROM X：从递归CTE的结果中查询

2. WHERE name='Gainesville, FL'：只选择目的地为"Gainesville, FL"的行

3. SELECT min(depth)：找到最小的深度值

   • 由于 depth 表示转机次数，min(depth) 就是最少转机次数

---

执行过程示例

假设航班网络如下：

Dillingham, AK (depth=0)
  ↓ 直飞
Anchorage, AK (depth=1)
  ↓ 转机1
Seattle, WA (depth=2) 
  ↓ 转机2
Atlanta, GA (depth=3)
  ↓ 转机3
Gainesville, FL (depth=4)

递归执行步骤：

第1轮（初始）：

id=11336, name='Dillingham, AK', depth=0

第2轮：

-- 从Dillingham, AK出发的所有航班
id=10299, name='Anchorage, AK', depth=1
id=11630, name='Fairbanks, AK', depth=1
...

第3轮：

-- 从Anchorage, AK出发的所有航班
id=12892, name='Los Angeles, CA', depth=2
id=14747, name='Seattle, WA', depth=2
...
-- 从Fairbanks, AK出发的所有航班
id=10299, name='Anchorage, AK', depth=2  -- 可能重复
...

第4轮：

-- 从Seattle, WA出发的所有航班
id=10397, name='Atlanta, GA', depth=3
...

第5轮：

-- 从Atlanta, GA出发的所有航班
id=11953, name='Gainesville, FL', depth=4
...

最终结果：

min(depth) = 4

---

重要细节解析

1. 转机次数 vs 航班次数

• depth=1：0次转机（直飞）

• depth=2：1次转机（两个航班）

• depth=3：2次转机（三个航班）

• depth=4：3次转机（四个航班）

所以查询结果 min(depth)=4 表示最少需要3次转机。

2. 为什么使用 union 而不是 union all

• union：自动去重，避免重复机场

• 如果同一个机场可以通过不同路径到达，只保留一次

• 减少不必要的递归扩展

3. 终止条件

递归终止的条件有两个：

1. 显式条件：depth<=3（最多递归到深度3）

2. 隐式条件：当没有新的机场可以添加时，递归自然停止

4. 效率问题

• depth<=3 限制了递归深度，提高效率

• 但对于大型网络，可能还是会有很多中间结果

---

潜在问题和改进

问题1：包含起点自身

当前的 CTE 包含了起点机场（depth=0），但在主查询中通过 where name='Gainesville, FL' 过滤掉了。如果起点就是目的地，会返回 depth=0。

问题2：循环路径

代码没有防止循环路径（A→B→A→B...）的机制，但因为使用 union 去重和 depth<=3 限制，影响有限。

改进版本：

with recursive flight_paths(id, name, depth, visited) as (
    -- 初始查询：起点
    select airport_id, airport_name, 0, array[airport_id]
    from airport_list
    where airport_name = 'Dillingham, AK'
    
    union
    
    -- 递归查询：扩展航班
    select al_end.airport_id, al_end.airport_name, fp.depth + 1, fp.visited || al_end.airport_id
    from flight_paths fp
    join airport_link al on al.start_node_id = fp.id
    join airport_list al_end on al.end_node_id = al_end.airport_id
    where fp.depth <= 10  -- 适当放宽限制
      and al_end.airport_id != all(fp.visited)  -- 避免重复访问
)
select min(depth) as min_transfers
from flight_paths
where name = 'Gainesville, FL'
  and depth > 0;  -- 排除起点自身（如果是目的地）

改进点：

1. 路径追踪：visited 数组记录已访问的机场

2. 防止循环：al_end.airport_id != all(fp.visited)

3. 排除起点：and depth > 0

4. 适当深度限制：depth <= 10

---

总结

这段代码的核心功能是：使用递归查询找到从 Dillingham, AK 到 Gainesville, FL 的最少转机次数。

关键理解：

1. depth 字段表示递归深度，等于航班次数，而转机次数 = depth - 1

2. 递归通过不断连接 airport_link 表来扩展航班网络

3. depth<=3 限制最多查询到 depth=3（即2次转机）

4. 使用 min(depth) 找到最短路径

结果解读：

• 如果返回 4：需要3次转机（4个航班）

• 如果返回 NULL：无法在指定深度内到达

• 如果返回 1：直飞可达（0次转机）