PostgreSQL递归CTE迁移至SQL Server的数组适配问题

核心难点解决思路 #

PostgreSQL原生数组类型、数组追加、ANY元素判断、数组切片语法在SQL Server中无直接等价实现，采用分隔符拼接字符串模拟数组的方案即可1:1还原原有逻辑，无需自定义数据类型，兼容SQL Server 2017及以上主流版本。

适配后完整可运行代码 #

首先是测试表与预置数据（原PG脚本仅需对字符字段做TRIM处理即可兼容SQL Server，避免CHAR类型定长补空格导致匹配失败）：

CREATE TABLE flight (
  src CHAR(3)
, dest CHAR(3)
, stt DATETIME
, endt DATETIME);

INSERT INTO flight VALUES 
('MSP', 'SLC', '2022-10-02 11:45:00', '2022-10-02 14:10:00'),
('SLC', 'LAX', '2022-10-02 15:20:00', '2022-10-02 17:45:00'),
('MSP', 'LAX', '2022-10-02 12:15:00', '2022-10-02 15:05:00');

递归CTE及结果查询代码：

WITH flight_paths (src, flights, path, dest, stt, endt) AS (
-- 锚定段：初始化所有直飞航班作为路径起点
SELECT
  TRIM(src) AS src
  , CAST(CONCAT(TRIM(src), '-', TRIM(dest)) AS VARCHAR(MAX)) AS flights
  , CAST(TRIM(src) AS VARCHAR(MAX)) AS path
  , TRIM(dest) AS dest 
  , stt
  , endt
FROM flight
UNION ALL
-- 递归段：拼接满足中转条件的联程航段
SELECT
  fp.src
  , CONCAT(fp.flights, ' > ', TRIM(f.src), '-', TRIM(f.dest)) AS flights
  , CONCAT(fp.path, ',', TRIM(f.src)) AS path
  , TRIM(f.dest) AS dest
  , fp.stt
  , f.endt
FROM flight f
INNER JOIN flight_paths fp 
  ON TRIM(f.src) = fp.dest 
WHERE 
  -- 替换原ANY判断：禁止路径环路，不重复经过已经到过的机场
  ',' + fp.path + ',' NOT LIKE '%,' + TRIM(f.src) + ',%'
  -- 替换原ANY判断：已经到达LAX的路径不再继续拼接后续航段
  AND ',' + fp.path + ',' NOT LIKE '%,LAX,%'
  -- 保留原中转时间要求：下一段航班起飞时间晚于上一段落地时间
  AND f.stt > fp.endt
) 
SELECT 
  flights, 
  stt, 
  endt,
  -- 替换原数组切片path[2:]：跳过路径第一个元素（出发地MSP），剩余为经停点
  STUFF((
    SELECT ',' + value 
    FROM STRING_SPLIT(path, ',') 
    OFFSET 1 ROWS
    FOR XML PATH(''), TYPE
  ).value('.', 'VARCHAR(MAX)'), 1, 1, '') AS stopovers
FROM flight_paths
WHERE src = 'MSP' AND dest = 'LAX'
-- 放开SQL Server默认递归深度限制，支持最多32767段联程（实际民航场景不会超过10段）
OPTION (MAXRECURSION 32767);

语法对应关系 #

• PG数组初始化ARRAY[val]：使用VARCHAR(MAX)类型的分隔符字符串替代，初始化时直接拼接首个元素
• PG数组追加arr || new_val：使用CONCAT()函数在字符串末尾补充分隔符后拼接新元素
• PG数组存在判断val = ANY(arr)：将字符串前后补充分隔符后做LIKE匹配，避免短字符串误匹配长字符串的问题（例如判断'SL'时误命中'SLC'）
• PG数组切片path[2:]：拆分字符串后跳过首元素（出发地），将剩余元素重新拼接为经停点列表

运行结果 #

代码执行结果与原PostgreSQL逻辑完全一致，返回2条MSP到LAX的有效路径：

• 直飞路径：MSP-LAX，无经停点，起飞时间2022-10-02 12:15:00，落地时间2022-10-02 15:05:00
• 联程路径：MSP-SLC > SLC-LAX，经停点SLC，起飞时间2022-10-02 11:45:00，落地时间2022-10-02 17:45:00

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