如何从MdnMaster表提取特定行信息（首个运单追踪号）

问题分析 #

现有查询返回空结果的核心原因：

• m.Customer = 'LAWSON' 被放在JOIN的ON子句中，左连接逻辑下会错误过滤主表中不符合该条件的记录，应移至WHERE子句。
• d.MLineShipDate 使用精确匹配，若字段包含时间部分会导致匹配失败，需改用日期范围判断。
• MAX()函数无法保证获取首个以Trk开头的NComment，需用窗口函数实现排序取第一条。
• JOIN条件的逻辑限制过严，导致部分有效记录被提前过滤。

修正后的查询代码 #

-- 计算目标日期（前一个工作日）
WITH TargetDate AS (
    SELECT 
        CASE 
            WHEN DATEPART(weekday, GETDATE() - 1) = 7 THEN DATEADD(DAY, -2, CAST(GETDATE() AS DATE)) -- 昨日是周六，取周五
            WHEN DATEPART(weekday, GETDATE() - 1) = 1 THEN DATEADD(DAY, -3, CAST(GETDATE() AS DATE)) -- 昨日是周日，取周五
            ELSE DATEADD(DAY, -1, CAST(GETDATE() AS DATE)) -- 其他情况取昨日
        END AS ShipDate
),
-- 获取每个DispatchNote的首个Trk开头的NComment
FirstTrkNote AS (
    SELECT 
        d.DispatchNote,
        d.NComment,
        ROW_NUMBER() OVER (PARTITION BY d.DispatchNote ORDER BY d.MLineShipDate, d.Id) AS rn -- 按发货日期+明细ID排序取第一条
    FROM MdnDetail d
    CROSS JOIN TargetDate td
    WHERE d.NComment LIKE 'Trk%'
      AND CAST(d.MLineShipDate AS DATE) = td.ShipDate
),
-- 获取每个DispatchNote的非空MStockCode
StockCodes AS (
    SELECT 
        d.DispatchNote,
        MAX(d.MStockCode) AS StockCode
    FROM MdnDetail d
    CROSS JOIN TargetDate td
    WHERE d.MStockCode IS NOT NULL
      AND CAST(d.MLineShipDate AS DATE) = td.ShipDate
    GROUP BY d.DispatchNote
)
SELECT 
    m.DispatchNote,
    sc.StockCode,
    ftn.NComment,
    m.CustomerPoNumber AS CustomerPO
FROM MdnMaster m
CROSS JOIN TargetDate td
LEFT JOIN StockCodes sc ON m.DispatchNote = sc.DispatchNote
LEFT JOIN FirstTrkNote ftn ON m.DispatchNote = ftn.DispatchNote AND ftn.rn = 1
WHERE m.Customer = 'LAWSON'
GROUP BY m.DispatchNote, sc.StockCode, ftn.NComment, m.CustomerPoNumber;

关键修正说明 #

1. 日期匹配优化：将日期转换为DATE类型消除时间部分干扰，用CTE统一计算目标日期，避免重复逻辑。
2. 首个追踪号提取：通过ROW_NUMBER()窗口函数按DispatchNote分区排序，确保取到第一条符合条件的追踪记录。
3. 条件位置调整：将客户筛选条件移至WHERE子句，保证左连接后主表符合条件的记录不会被错误过滤。
4. 逻辑拆分：将库存代码和追踪号的提取拆分为独立CTE，避免多条件JOIN时的逻辑冲突，提升可读性。

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