将销售订单表关联至折扣开关表：查询订单对应折扣状态

现有两张SQL表：

• 折扣开关状态表：记录特定客户群体的折扣激活状态（TRUE/FALSE）、客户行业（customer vertical）、类型（type）及状态变更时间
• 订单表：记录订单编号（ORDER_CODE）、类型（type）、客户行业（customer vertical）、订单时间

需实现：查询每个订单到达时对应的最新折扣开关状态，无对应参考记录的返回NA。

折扣状态变更表 #

订单表（含预期结果） #

SQL解决方案 #

方法1：窗口函数筛选关联（通用兼容多数数据库） #

WITH ranked_discounts AS (
    SELECT 
        customer_vertical,
        type,
        time AS toggle_time,
        Discount_Toggle_ON_OFF,
        -- 按客户行业+类型分组，按时间倒序排名，最新状态排第1
        ROW_NUMBER() OVER (
            PARTITION BY customer_vertical, type 
            ORDER BY time DESC
        ) AS rn
    FROM discount_toggle
)
SELECT 
    o.ORDER_CODE,
    o.type,
    o.customer_vertical,
    o.time AS order_time,
    -- 无匹配时返回NA，否则转换状态为字符串
    COALESCE(CAST(rd.Discount_Toggle_ON_OFF AS VARCHAR), 'NA') AS was_discount_on
FROM orders o
LEFT JOIN ranked_discounts rd 
    ON o.customer_vertical = rd.customer_vertical
    AND o.type = rd.type
    AND rd.toggle_time <= o.time
    AND rd.rn = 1;

方法2：LATERAL JOIN（适用于PostgreSQL、SQL Server等） #

SELECT 
    o.ORDER_CODE,
    o.type,
    o.customer_vertical,
    o.time AS order_time,
    COALESCE(CAST(dt.Discount_Toggle_ON_OFF AS VARCHAR), 'NA') AS was_discount_on
FROM orders o
-- 关联子查询，直接取当前订单对应的最新折扣状态
LEFT JOIN LATERAL (
    SELECT Discount_Toggle_ON_OFF
    FROM discount_toggle dt
    WHERE dt.customer_vertical = o.customer_vertical
      AND dt.type = o.type
      AND dt.time <= o.time
    ORDER BY dt.time DESC
    LIMIT 1
) dt ON true;

逻辑说明 #

两种方法核心都是：针对每个订单，匹配同客户行业、同类型且变更时间早于/等于订单时间的最新折扣状态；无匹配记录时用COALESCE将NULL转换为'NA'。

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