SQL如何引用子查询结果获取付费订阅用户的试用开始日期及解决字段未定义错误

你在子查询内部定义的表别名se1作用域仅限该子查询内部，外部CASE语句无法直接访问se1的字段，这是触发报错的核心原因。

方案1：窗口函数实现（适配MySQL 8.0+、PostgreSQL等支持窗口函数的现代数据库） #

逻辑更简洁，执行效率更高：

SELECT 
    user_id,
    subscription_type,
    `timestamp`,
    CASE 
        WHEN subscription_type = 'PAYING' 
        THEN MAX(CASE WHEN subscription_type = 'TRIAL' THEN `timestamp` END) OVER (
            PARTITION BY user_id 
            ORDER BY STR_TO_DATE(`timestamp`, '%d/%m/%Y') 
            ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
        )
        ELSE NULL 
    END AS Trial_Start_date
FROM subscription_event
ORDER BY user_id, STR_TO_DATE(`timestamp`, '%d/%m/%Y');

方案2：兼容低版本数据库的实现（适配不支持窗口函数的场景） #

通过左连接+合并查询实现：

-- 处理付费类型记录，关联匹配对应的试用开始日期
SELECT 
    se.user_id,
    se.subscription_type,
    se.`timestamp`,
    MAX(se1.`timestamp`) AS Trial_Start_date
FROM subscription_event se
LEFT JOIN subscription_event se1 
    ON se.user_id = se1.user_id 
    AND se1.subscription_type = 'TRIAL' 
    AND STR_TO_DATE(se1.`timestamp`, '%d/%m/%Y') < STR_TO_DATE(se.`timestamp`, '%d/%m/%Y')
WHERE se.subscription_type = 'PAYING'
GROUP BY se.user_id, se.subscription_type, se.`timestamp`

UNION ALL

-- 试用类型记录直接返回，新增字段为空
SELECT 
    user_id,
    subscription_type,
    `timestamp`,
    NULL AS Trial_Start_date
FROM subscription_event
WHERE subscription_type = 'TRIAL'

ORDER BY user_id, STR_TO_DATE(`timestamp`, '%d/%m/%Y');

• 如果需求是取用户首次试用日期，无论转付费时间，可删除方案1窗口函数中的ORDER BY STR_TO_DATE(timestamp, '%d/%m/%Y') ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING部分，仅保留PARTITION BY user_id即可
• 所有时间比较前先通过STR_TO_DATE转换为日期类型，避免字符串按字符顺序比较出现逻辑错误

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