基于含liveFrom/liveUntil列的两张表构建价格折扣时间线的SQL求助

现有两张表tblPrice和tblDiscount，均包含liveFrom、liveUntil列及对应业务数值（tblPrice存储retailPrice，tblDiscount存储discount）。需求是生成有序的价格折扣时间线列表，展示价格或折扣变动时的价格变化情况，最终结果需体现各有效时间段内的价格与折扣组合。

tblPrice表数据 #

tblDiscount表数据 #

预期结果 #

（注：预期结果第5行折扣率疑似笔误，原数据为0.3100，此处修正为0.31）

注意事项 #

• 价格并非连续生效（如tblPrice的行1和行2价格相同，但存在无有效价格的断档期）；
• 需以价格生效时段为核心，折扣生效但价格未生效时，仍以价格的生效时间为准；
• 价格生效但无对应折扣时，折扣率视为0。

当前尝试的查询语句 #

SELECT
    P.[priceID],
    P.[retailPrice],
    P.[liveFrom],
    P.[liveUntil],
    D.[logID],
    D.[discount],
    D.[liveFrom],
    D.[liveUntil],
    ------------
    CASE
        WHEN SDV.[liveFrom] <= P.[liveFrom] AND (SDV.[liveUntil] >= P.[liveFrom] OR SDV.[liveUntil] IS NULL) THEN
            SDV.[liveFrom]
        WHEN SDV.[liveFrom] >= P.[liveFrom] AND (SDV.[liveUntil] <= P.[liveUntil] OR P.[liveUntil] IS NULL) THEN
            P.[liveFrom]
        ELSE
            '1900-01-01 00:00:00'
    END AS [EFFECTIVE_FROM],
    CASE
        WHEN ISNULL(P.[liveUntil],@date) < ISNULL(SDV.[liveUntil],@date) THEN
            P.[liveUntil]
        ELSE
            SDV.[liveUntil]
    END AS [EFFECTIVE_UNTIL]
FROM 
    tblPrice P
    INNER JOIN tblDiscount D ON P.[supplierDiscountID] = D.[supplierDiscountID]
        AND ((
                D.[liveFrom] <= P.[liveFrom] 
                AND (D.[liveUntil] >= P.[liveFrom] OR D.[liveUntil] IS NULL) 
            ) OR
            (
                D.[liveFrom] >= P.[liveFrom]
                AND (D.[liveFrom] <= P.[liveUntil] OR P.[liveUntil] IS NULL)
            ))
ORDER BY
    P.[liveFrom],
    D.[liveFrom];

解决方案思路与实现 #

要实现时间线的合并，核心是提取所有关键时间点（两张表的liveFrom和非空liveUntil），以价格的有效时段为基础拆分区间，再匹配每个子时段的折扣率：

1. 提取关键时间点：收集两张表中所有生效起始、截止时间，去重排序得到分割点；
2. 拆分价格有效时段：将每个价格的生效区间按关键时间点拆分为连续子时段，过滤无效的空区间；
3. 匹配对应折扣：为每个价格子时段找到当前生效的折扣，无折扣则取0；
4. 整理输出：按时间排序，格式化日期后得到最终结果。

以下是SQL Server环境下的实现代码：

-- 提取所有关键时间点
WITH AllTimePoints AS (
    SELECT liveFrom AS TimePoint FROM tblPrice
    UNION
    SELECT liveUntil AS TimePoint FROM tblPrice WHERE liveUntil IS NOT NULL
    UNION
    SELECT liveFrom AS TimePoint FROM tblDiscount
    UNION
    SELECT liveUntil AS TimePoint FROM tblDiscount WHERE liveUntil IS NOT NULL
),
-- 生成价格的拆分时段
PriceTimeSegments AS (
    SELECT 
        p.retailPrice,
        GREATEST(p.liveFrom, LAG(atp.TimePoint) OVER (ORDER BY atp.TimePoint)) AS SegmentFrom,
        LEAST(p.liveUntil, atp.TimePoint) AS SegmentUntil
    FROM tblPrice p
    CROSS JOIN AllTimePoints atp
    WHERE atp.TimePoint >= p.liveFrom 
      AND (p.liveUntil IS NULL OR atp.TimePoint <= p.liveUntil)
    UNION ALL
    -- 处理价格永久生效的最后一个时段
    SELECT 
        p.retailPrice,
        MAX(atp.TimePoint) AS SegmentFrom,
        NULL AS SegmentUntil
    FROM tblPrice p
    CROSS JOIN AllTimePoints atp
    WHERE p.liveUntil IS NULL
    GROUP BY p.retailPrice, p.liveFrom
    HAVING MAX(atp.TimePoint) >= p.liveFrom
),
-- 过滤无效时段（起始>=结束的情况）
ValidPriceSegments AS (
    SELECT 
        retailPrice,
        SegmentFrom,
        SegmentUntil
    FROM PriceTimeSegments
    WHERE SegmentFrom < ISNULL(SegmentUntil, '9999-12-31')
),
-- 匹配每个时段的折扣
FinalResult AS (
    SELECT 
        vps.retailPrice,
        ISNULL(d.discount, 0) AS discount,
        vps.SegmentFrom AS liveFrom,
        vps.SegmentUntil AS liveUntil
    FROM ValidPriceSegments vps
    LEFT JOIN tblDiscount d
        ON d.liveFrom <= vps.SegmentFrom 
        AND (d.liveUntil IS NULL OR d.liveUntil >= vps.SegmentFrom)
        AND (vps.SegmentUntil IS NULL OR d.liveFrom <= vps.SegmentUntil)
    -- 取当前时段内生效的最新折扣
    WHERE NOT EXISTS (
        SELECT 1 FROM tblDiscount d2
        WHERE d2.liveFrom <= vps.SegmentFrom
          AND (d2.liveUntil IS NULL OR d2.liveUntil >= vps.SegmentFrom)
          AND (vps.SegmentUntil IS NULL OR d2.liveFrom <= vps.SegmentUntil)
          AND d2.liveFrom > d.liveFrom
    )
)
-- 最终排序并格式化输出
SELECT 
    ROW_NUMBER() OVER (ORDER BY liveFrom) AS 行号,
    retailPrice,
    discount,
    CONVERT(VARCHAR(16), liveFrom, 103) AS liveFrom,
    CASE WHEN liveUntil IS NULL THEN NULL 
         ELSE CONVERT(VARCHAR(16), liveUntil, 103) 
    END AS liveUntil
FROM FinalResult
ORDER BY liveFrom;

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