如何用SQL实现客户订单关联后单客户唯一记录的多条件筛选？

核心思路 #

先完成两张表的关联，再按规则逐步筛选：

1. 先排除orders.tran_id = orders.alt_id的记录；
2. 对剩余记录，按cust_id_num分组，给每组内的记录按tran_ts从新到旧排序；
3. 取每组排序后的第一条记录，自然满足单条直接保留、多条留最新的要求。

基础实现代码 #

WITH joined_data AS (
    -- 完成两表关联
    SELECT 
        c.*,
        o.tran_id,
        o.alt_id,
        o.tran_ts
    FROM customers c
    JOIN orders o ON c.cust_id_num = o.cust_id_num
), filtered_ranked AS (
    -- 过滤掉tran_id与alt_id相同的记录，同时给剩余记录按时间排序
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY cust_id_num 
            ORDER BY tran_ts DESC
        ) AS rn
    FROM joined_data
    WHERE tran_id != alt_id
)
-- 取每个客户的第一条记录
SELECT * EXCEPT(rn)
FROM filtered_ranked
WHERE rn = 1;

特殊场景适配代码 #

如果需要保留「某客户所有关联记录都满足tran_id=alt_id」的情况（即规则1仅在有多条记录时才触发），可以调整排序优先级，优先保留tran_id≠alt_id的记录，再按时间排序：

WITH joined_data AS (
    SELECT 
        c.*,
        o.tran_id,
        o.alt_id,
        o.tran_ts
    FROM customers c
    JOIN orders o ON c.cust_id_num = o.cust_id_num
), ranked_data AS (
    SELECT 
        *,
        ROW_NUMBER() OVER (
            PARTITION BY cust_id_num 
            ORDER BY 
                CASE WHEN tran_id != alt_id THEN 1 ELSE 2 END, -- 优先保留非等值记录
                tran_ts DESC
        ) AS rn
    FROM joined_data
)
SELECT * EXCEPT(rn)
FROM ranked_data
WHERE rn = 1;

这个版本的逻辑：

• 同一客户下，tran_id≠alt_id的记录排在前面；
• 若这类记录有多条，取时间最新的；
• 若所有记录都是tran_id=alt_id，则取其中时间最新的那条。

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