SQL Server联合两张表查询1年以上非促销时段销售数据的方法

实现思路 #

1. 先清洗Promotions表数据：将promotion_end_date中的Nil值替换为NULL，同时将所有字符串格式的日期统一转换为DATE类型，处于ACTIVE状态（promotion_end_date为NULL）的促销结束日期暂设为极大值'9999-12-31'避免后续逻辑判断异常。
2. 使用窗口函数LEAD()按商品维度（item_number）分组，按促销开始时间升序排序，获取每个促销对应的下一次促销的开始时间，即可得到该商品两个相邻促销之间的无促销时间区间：(当前促销结束日期, 下一次促销开始日期)
3. 将Sales表的销售记录与上述无促销区间关联，匹配到销售日期落在区间内的记录即为所求结果。

完整查询代码 #

WITH ProcessedPromotions AS (
    -- 清洗促销表数据，转换日期格式、处理ACTIVE状态
    SELECT
        item_number,
        CONVERT(DATE, promotion_start_date, 110) AS promotion_start_date,
        CASE
            WHEN promotion_end_date = 'Nil' OR promotion_end_date IS NULL THEN CONVERT(DATE, '9999-12-31')
            ELSE CONVERT(DATE, promotion_end_date, 110)
        END AS promotion_end_date
    FROM Promotions
),
PromotionGaps AS (
    -- 计算每个商品相邻促销之间的无促销区间
    SELECT
        item_number,
        promotion_end_date AS gap_start,
        LEAD(promotion_start_date, 1, CONVERT(DATE, '9999-12-31')) OVER (
            PARTITION BY item_number
            ORDER BY promotion_start_date ASC
        ) AS gap_end
    FROM ProcessedPromotions
),
ProcessedSales AS (
    -- 清洗销售表日期格式
    SELECT
        order_number,
        item_number,
        CONVERT(DATE, sale_date, 110) AS sale_date
    FROM Sales
)
-- 匹配落在无促销区间的销售记录，格式化输出日期和示例保持一致
SELECT s.order_number, s.item_number, FORMAT(s.sale_date, 'MM-dd-yyyy') AS sale_date
FROM ProcessedSales s
INNER JOIN PromotionGaps g
    ON s.item_number = g.item_number
    AND s.sale_date > g.gap_start
    AND s.sale_date < g.gap_end
ORDER BY s.order_number;

关键逻辑说明 #

• 日期转换使用格式代码110对应MM-DD-YYYY的字符串格式，适配示例中的日期写法，若实际业务中日期格式不同可调整对应格式代码。
• 无促销区间的判断用sale_date > gap_start AND sale_date < gap_end，符合需求中“两次促销之间”的判定，排除了促销当天的订单。
• 对于只有单次促销的商品（如示例中的ABC0002、ABC0005），LEAD函数默认取极大值作为gap_end，即可匹配该次促销结束后到下一次促销开始前的所有无促销订单。

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