SQL递归CTE语法错误求助：UNION ALL附近报错问题排查

直接语法错误原因 #

你遇到的ERROR: syntax error at or near "union"直接原因是递归分支的where子句位置错误：SQL语法中WHERE必须放在LIMIT之前，但你把where idx < 6写在了limit 1后面，违反了语法顺序。

但更关键的是，你的递归CTE逻辑完全错误，根本无法实现“重复随机行n次”的需求，以下是问题拆解和修正方案：

核心逻辑问题 #

递归CTE的递归分支（UNION ALL后面的部分）必须引用CTE自身，才能实现迭代生成重复行。而你现在的写法是两次独立查询原表，既无法关联迭代的idx，也不能基于已获取的随机行重复生成。

另外，原语句的limit 1没有加随机排序，每次取的可能是同一行，无法保证“随机获取”的需求。

修正方案 #

方案1：正确的递归CTE写法 #

WITH RECURSIVE cte1 AS (
    -- 初始步骤：随机获取目标行，初始化idx为1
    SELECT 
        1 AS idx, 
        company_id, 
        field_values
    FROM transaction_detail
    WHERE company_id = 12345
    ORDER BY RANDOM()  -- 确保随机取一行
    LIMIT 1

    UNION ALL

    -- 递归步骤：基于CTE已有的行，生成下一行，idx递增
    SELECT 
        idx + 1, 
        company_id, 
        field_values
    FROM cte1
    WHERE idx < 6  -- 控制重复次数：这里会生成idx=1到6，共6行（即重复5次）
)
SELECT * FROM cte1;

方案2：更简单的非递归写法（推荐初学者） #

用generate_series生成序列，和随机行做交叉连接，直接生成重复行，逻辑更直观：

SELECT 
    gs.idx,
    td.company_id,
    td.field_values
FROM (
    -- 先随机获取一行
    SELECT company_id, field_values
    FROM transaction_detail
    WHERE company_id = 12345
    ORDER BY RANDOM()
    LIMIT 1
) td
-- 交叉连接生成1到6的序列，对应6行重复数据
CROSS JOIN generate_series(1, 6) gs(idx);

关键说明 #

• ORDER BY RANDOM()：PostgreSQL中用这个实现随机取行，如果你用其他数据库（比如MySQL），换成ORDER BY RAND()即可。
• 重复次数控制：如果要重复n次，递归写法中把idx < 6改成idx < n+1；非递归写法中把generate_series(1,6)改成generate_series(1, n+1)（比如重复3次就生成1-4，共4行）。

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