如何查询Subscription关联的plan_id匹配的最早Version记录并修复SQL错误

你提交的SQL存在明显的语法疏漏：第二行的子查询缺少左括号，导致SQL解析器遇到MIN关键字时无法识别为合法的子查询结构，直接抛出语法错误。同时你的SQL逻辑存在两处问题：

• 子查询未关联subscription_id，会匹配到其他订阅下同plan_id的version记录
• 外层已经关联versions表，和子查询的逻辑重复，会导致结果行数异常

你确实需要按订阅维度对符合条件的version进行分组筛选，核心逻辑是：先筛选出status=processed、且plan_id和对应订阅当前plan_id一致的version记录，再按订阅ID分组取每组authorized_at的最小值。

方案一：关联子查询（逻辑直观，适合数据量不大的场景） #

SELECT
    s.id AS subscription_id,
    (
        SELECT MIN(v.authorized_at)
        FROM versions v
        WHERE v.subscription_id = s.id
          AND v.plan_id = s.plan_id
          AND v.status = 'processed'
    ) AS current_plan_authorized_at
FROM subscriptions s

方案二：预分组关联（性能更优，适合数据量较大的场景） #

先对versions表做预分组计算，再和订阅表关联匹配，避免逐行执行子查询的性能开销：

SELECT
    s.id AS subscription_id,
    v_min.first_authorized_at
FROM subscriptions s
LEFT JOIN (
    SELECT
        subscription_id,
        plan_id,
        MIN(authorized_at) AS first_authorized_at
    FROM versions
    WHERE status = 'processed'
    GROUP BY subscription_id, plan_id
) v_min 
ON s.id = v_min.subscription_id 
AND s.plan_id = v_min.plan_id

如果你需要获取最早授权的完整Version记录而非仅日期，可以使用窗口函数实现：

SELECT * FROM (
    SELECT
        s.id AS subscription_id,
        v.*,
        ROW_NUMBER() OVER (PARTITION BY v.subscription_id ORDER BY v.authorized_at ASC) AS rn
    FROM subscriptions s
    INNER JOIN versions v 
        ON s.id = v.subscription_id
        AND v.plan_id = s.plan_id
        AND v.status = 'processed'
) t WHERE rn = 1

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