MariaDB递归子查询报错：未知列v.contingent_id的解决方法

问题分析 #

你遇到的Unknown column 'v.contingent_id' in 'where clause'报错，根源是MySQL中递归CTE的初始查询块无法直接引用外层查询的列——递归CTE的作用域独立于外层查询，不能直接访问外层变量。

解决方案 #

调整SQL结构，把vendors表融入递归CTE的初始节点，一次性遍历所有供应商对应的配额链，再聚合计算每个供应商的配额可用状态：

WITH RECURSIVE rec_contingents AS (
    -- 初始节点：关联所有供应商的初始配额
    SELECT 
        v.id AS vendor_id,
        c.id AS contingent_id,
        IFNULL(c.daily_remain, 1) > 0 AS row_contigent,
        c.parent_id
    FROM vendors v
    JOIN contingents c ON v.contingent_id = c.id
    UNION ALL
    -- 递归遍历父配额链
    SELECT 
        rc.vendor_id,
        pc.id AS contingent_id,
        IFNULL(pc.daily_remain, 1) > 0 AS row_contigent,
        pc.parent_id
    FROM rec_contingents rc
    JOIN contingents pc ON rc.parent_id = pc.id
)
-- 聚合判断每个供应商的配额链是否全部可用
SELECT 
    vendor_id,
    SUM(row_contigent) = COUNT(row_contigent) AS contingent_left
FROM rec_contingents
GROUP BY vendor_id;

逻辑说明 #

1. 初始查询：直接关联vendors和contingents，把每个供应商对应的初始配额节点纳入递归，同时保留供应商ID，确保后续递归节点能归属到对应供应商。
2. 递归遍历：沿着parent_id向上遍历所有父配额，每个节点都携带对应的供应商ID，完整覆盖整个配额链。
3. 聚合计算：通过GROUP BY vendor_id对每个供应商的所有配额节点进行聚合：
   • 若所有配额节点的row_contigent都是1（可用），则SUM(row_contigent)等于COUNT(row_contigent)，结果为1
   • 只要有一个配额节点用尽（row_contigent为0），SUM就会小于COUNT，结果为0

验证结果 #

用你提供的示例数据执行上述SQL，会得到预期结果：

+-----------+-----------------+
| vendor_id | contingent_left |
+-----------+-----------------+
|     1     |        1        |
|     2     |        0        |
+-----------+-----------------+

内容的提问来源于stack exchange，提问作者Dennis van de Hoef - Xiotin