获取指定客户中交易码3后出现交易码12的最早交易记录

你的原始SQL存在两个关键问题：

• 会返回所有晚于该客户任意交易码3的交易码12记录，而非需求中的最早那一条
• 没有同时返回对应的最早交易码3记录

要实现需求，我们可以通过分步定位关键日期，再匹配对应交易记录的方式处理：

方法一：CTE+联合查询（兼容多数现代SQL引擎） #

WITH customer_key_dates AS (
    -- 第一步：锁定每个目标客户的关键日期
    SELECT
        customer_number,
        -- 该客户最早的交易码3日期
        MIN(CASE WHEN transaction_code = 3 THEN transaction_date END) AS earliest_t3_date,
        -- 该客户最早的、晚于交易码3的交易码12日期
        MIN(CASE 
            WHEN transaction_code = 12 
            AND transaction_date > (SELECT MIN(transaction_date) FROM trans_table t2 WHERE t2.customer_number = t1.customer_number AND t2.transaction_code = 3)
            THEN transaction_date 
        END) AS earliest_t12_after_t3_date
    FROM trans_table t1
    WHERE customer_number IN (1,2,3,4)
    GROUP BY customer_number
    -- 只保留同时存在符合条件的交易码3和交易码12的客户
    HAVING earliest_t3_date IS NOT NULL AND earliest_t12_after_t3_date IS NOT NULL
)
-- 第二步：取出对应的交易记录
SELECT t.*
FROM trans_table t
JOIN customer_key_dates ckd ON t.customer_number = ckd.customer_number
WHERE (t.transaction_code = 3 AND t.transaction_date = ckd.earliest_t3_date)
   OR (t.transaction_code = 12 AND t.transaction_date = ckd.earliest_t12_after_t3_date)
ORDER BY t.customer_number, t.transaction_date;

方法二：子查询替代CTE（适配不支持CTE的SQL环境） #

-- 先获取每个客户的最早交易码3日期
SELECT t.*
FROM trans_table t
WHERE (
    -- 匹配最早的交易码3记录
    (t.transaction_code = 3 AND t.transaction_date = (
        SELECT MIN(transaction_date) 
        FROM trans_table 
        WHERE customer_number = t.customer_number AND transaction_code = 3
    ))
    OR 
    -- 匹配最早的、晚于交易码3的交易码12记录
    (t.transaction_code = 12 AND t.transaction_date = (
        SELECT MIN(transaction_date) 
        FROM trans_table t12
        WHERE t12.customer_number = t.customer_number 
        AND t12.transaction_code = 12
        AND t12.transaction_date > (
            SELECT MIN(transaction_date) 
            FROM trans_table t3
            WHERE t3.customer_number = t.customer_number AND t3.transaction_code = 3
        )
    ))
)
AND t.customer_number IN (1,2,3,4)
-- 过滤掉没有符合条件交易的客户
AND EXISTS (
    SELECT 1 FROM trans_table t3 
    WHERE t3.customer_number = t.customer_number AND t3.transaction_code = 3
)
AND EXISTS (
    SELECT 1 FROM trans_table t12 
    WHERE t12.customer_number = t.customer_number 
    AND t12.transaction_code = 12
    AND t12.transaction_date > (
        SELECT MIN(transaction_date) FROM trans_table t3 WHERE t3.customer_number = t.customer_number AND t3.transaction_code = 3
    )
)
ORDER BY t.customer_number, t.transaction_date;

关键说明 #

1. 先通过分组/子查询锁定每个客户的最早交易码3日期，确保拿到的是该客户第一笔交易码3记录
2. 基于这个日期，精准筛选出该客户最早的、发生在之后的交易码12日期，避免返回所有符合日期条件的交易码12
3. 最后通过联合/条件判断，把对应的交易码3和交易码12记录一起返回，按客户和交易日期排序便于查看

额外提示 #

如果同一客户在同一日期有多笔相同交易码的记录，可添加ROW_NUMBER()窗口函数（按交易ID或其他唯一字段排序）来只保留一条记录。

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