如何嵌套多CASE WHEN表达式？新老客户分类SQL逻辑疑问

首先，你原来的SQL存在语法错误：WHERE子句里不能直接使用聚合函数（比如MAX()），WHERE是分组聚合前的行级筛选逻辑，聚合函数只能用在HAVING、SELECT或ORDER BY子句中。

回到你的需求：给2021年有交易的客户标记「新客」/「回头客」，新客的判断逻辑是对的（当前交易月份=客户最早交易月份），而回头客的判断（当前交易月份与上一次交易的最大月份间隔≥12个月）完全应该放在CASE WHEN里，而非WHERE——因为WHERE会直接过滤掉不符合条件的行，但你需要的是给所有2021年的交易记录标记类型，不是筛选掉记录。

修正后的SQL示例（以MySQL为例，不同数据库日期函数略有差异） #

SELECT 
    T.CONTACT_KEY,
    A.TXN_MTH,
    CASE
        -- 新客：当前交易月份等于该客户最早的交易月份
        WHEN A.TXN_MTH = MIN(X.FISCAL_MTH_IDNT) THEN 'NEW'
        -- 回头客：计算上一次交易的最大月份，与当前月份间隔≥12个月
        WHEN PERIOD_DIFF(
            CAST(A.TXN_MTH AS UNSIGNED),
            CAST(MAX(CASE WHEN X.FISCAL_MTH_IDNT < A.TXN_MTH THEN X.FISCAL_MTH_IDNT END) AS UNSIGNED)
        ) >= 12 THEN 'RETURNING'
        -- 其他情况（如间隔不足12个月的老客）可按需标记，这里设为普通老客
        ELSE 'EXISTING'
    END AS CUST_TYPE
FROM B_TRANSACTION T
INNER JOIN B_TIME X
    ON T.TRANSACTION_DT_KEY = X.DATE_KEY
INNER JOIN A
    ON A.CONTACT_KEY = T.CONTACT_KEY AND A.BU_KEY = T.BU_KEY 
-- 筛选2021年的交易记录
WHERE X.FISCAL_MTH_IDNT BETWEEN '202101' AND '202112'
GROUP BY
    T.CONTACT_KEY,
    A.TXN_MTH;

关键细节说明 #

1. 日期间隔计算：用PERIOD_DIFF(当前月份, 上一次月份)可以直接计算YYYYMM格式月份的间隔数（比如PERIOD_DIFF(202103,201912)=15），比手动转日期再计算更简洁。如果是其他数据库，Oracle可用MONTHS_BETWEEN()，SQL Server用DATEDIFF(MONTH, ...)，注意对应格式转换即可。
2. 聚合函数的正确用法：MAX(CASE WHEN X.FISCAL_MTH_IDNT < A.TXN_MTH THEN X.FISCAL_MTH_IDNT END)用来获取当前交易月份之前，该客户的最后一次交易月份，将这个聚合计算放在CASE WHEN里是合法的。
3. 无需嵌套三层CASE：你的需求只需要区分新客、回头客，最多补充普通老客的分支，两层CASE足够，不需要嵌套三层。

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