如何通过单条查询获取指定UserIdNew对应的全部历史旧ID？

要解决这个从指定新ID追溯所有旧ID的问题，最直接高效的方法是使用递归CTE（公共表表达式）——这是SQL中处理层级/链式数据的标准方案，几乎所有现代关系型数据库（MySQL 8+、PostgreSQL、SQL Server等）都支持它。

假设你的表名为user_id_history，字段为UserIdNew（新ID）和UserIdOld（旧ID），下面分两种场景给出具体实现：

场景1：返回所有旧ID的单独行 #

这个查询会把每个旧ID作为单独的结果行返回，方便后续灵活处理：

WITH RECURSIVE id_history AS (
    -- 锚点查询：先找到目标新ID对应的直接旧ID
    SELECT UserIdOld
    FROM user_id_history
    WHERE UserIdNew = 15 -- 替换成你要查询的目标新ID
    UNION ALL
    -- 递归查询：沿着旧ID继续向上追溯上一级的旧ID
    SELECT uh.UserIdOld
    FROM user_id_history uh
    JOIN id_history ih ON uh.UserIdNew = ih.UserIdOld
)
SELECT UserIdOld AS old_user_id
FROM id_history
ORDER BY UserIdOld; -- 可选：按旧ID从小到大排序

执行后会返回：

old_user_id
-----------
1
5
10

场景2：合并成逗号分隔的字符串 #

如果需要把所有旧ID合并成一个紧凑的字符串（比如示例中的1,5,10），可以结合字符串聚合函数，不同数据库的语法略有差异：

MySQL/MariaDB版本 #

WITH RECURSIVE id_history AS (
    SELECT UserIdOld
    FROM user_id_history
    WHERE UserIdNew = 15
    UNION ALL
    SELECT uh.UserIdOld
    FROM user_id_history uh
    JOIN id_history ih ON uh.UserIdNew = ih.UserIdOld
)
SELECT GROUP_CONCAT(UserIdOld ORDER BY UserIdOld) AS all_old_ids
FROM id_history;

PostgreSQL版本 #

WITH RECURSIVE id_history AS (
    SELECT UserIdOld
    FROM user_id_history
    WHERE UserIdNew = 15
    UNION ALL
    SELECT uh.UserIdOld
    FROM user_id_history uh
    JOIN id_history ih ON uh.UserIdNew = ih.UserIdOld
)
SELECT STRING_AGG(UserIdOld::TEXT, ',' ORDER BY UserIdOld) AS all_old_ids
FROM id_history;

SQL Server 2017+版本 #

WITH RECURSIVE id_history AS (
    SELECT UserIdOld
    FROM user_id_history
    WHERE UserIdNew = 15
    UNION ALL
    SELECT uh.UserIdOld
    FROM user_id_history uh
    JOIN id_history ih ON uh.UserIdNew = ih.UserIdOld
)
SELECT STRING_AGG(CAST(UserIdOld AS VARCHAR(10)), ',' ORDER BY UserIdOld) AS all_old_ids
FROM id_history;

注意事项 #

• 循环引用处理：如果你的表中存在ID循环（比如1→5→1），递归会无限执行。可以在数据库中设置递归深度限制（比如SQL Server的MAXRECURSION选项），或者在业务逻辑上提前避免这种循环。
• 兼容性：递归CTE需要数据库支持SQL:1999标准，MySQL 8.0之前的版本不支持，如果你用的是旧版MySQL，可以考虑用存储过程或者应用层代码实现追溯逻辑。

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