基于OrderHeader日期关联生产与Bought表数据的SQL查询问题

嘿，我看你遇到了SQL查询里关联两张表并合并结果的问题，咱们来一步步解决它。首先梳理下你的核心需求：

• 先从OrderHeader表中获取orhsysid = 1对应的所有生产日期
• 针对这些日期，分别完成两个统计：
  1. 统计每个日期的生产数据金额总和
  2. 找到每个生产日期之前最近的Bought日期，并统计该日期的Bought金额总和
• 最终将两部分统计结果合并成一张表输出

完整SQL实现 #

我用CTE（公共表表达式）来拆分逻辑，让代码更清晰易读：

-- 1. 第一步：获取目标生产日期集合
WITH TargetDates AS (
    SELECT Lotdate AS OrderDate
    FROM OrderHeader
    WHERE orhsysid = 1
),
-- 2. 操作一：统计每个目标日期的生产金额总和
OrderTotals AS (
    SELECT 
        Lotdate AS OrderDate,
        SUM(Amount) AS OrderTotalAmount
    FROM OrderHeader
    WHERE Lotdate IN (SELECT OrderDate FROM TargetDates)
    GROUP BY Lotdate
),
-- 3. 为每个生产日期匹配最近的历史Bought日期
BoughtClosestDates AS (
    SELECT 
        td.OrderDate,
        MAX(b.date) AS ClosestBoughtDate
    FROM TargetDates td
    LEFT JOIN Bought b ON b.date <= td.OrderDate
    GROUP BY td.OrderDate
),
-- 4. 预统计Bought表每个日期的金额总和（避免重复计算）
BoughtTotals AS (
    SELECT 
        date AS BoughtDate,
        SUM(Amount) AS BoughtTotalAmount
    FROM Bought
    GROUP BY date
)
-- 5. 最终合并所有结果
SELECT 
    bcd.ClosestBoughtDate AS [Bought.date],
    COALESCE(bt.BoughtTotalAmount, 0) AS [Bought.SUM(AMOUNT)], -- 用COALESCE处理无匹配的情况
    ot.OrderDate AS [Orderheader.date],
    ot.OrderTotalAmount AS [Orderheader.SUM(AMOUNT)]
FROM OrderTotals ot
LEFT JOIN BoughtClosestDates bcd ON ot.OrderDate = bcd.OrderDate
LEFT JOIN BoughtTotals bt ON bcd.ClosestBoughtDate = bt.BoughtDate
ORDER BY ot.OrderDate;

代码说明 #

1. TargetDates：先把需要处理的生产日期提取出来，作为后续所有操作的基础
2. OrderTotals：直接筛选目标日期，统计每个日期的生产金额总和，对应你的操作一
3. BoughtClosestDates：通过LEFT JOIN和MAX(b.date)找到每个生产日期之前最近的Bought日期（如果没有更早的Bought日期，会返回NULL）
4. BoughtTotals：预先统计Bought表每个日期的金额总和，避免在关联时重复计算，提升查询效率
5. 最后一步关联所有CTE，用COALESCE把无匹配的Bought金额转为0（如果不需要可以去掉），按生产日期排序输出

注意事项 #

• 确保两张表的日期字段名和你实际库中的一致（比如OrderHeader的日期是Lotdate，Bought的日期是date，如果你的字段名不同，记得替换）
• 如果需要严格匹配“之前的日期”（不包含当天），把b.date <= td.OrderDate改成b.date < td.OrderDate即可

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