修复SQL查询中ExpectedDueDate NULL值及重复数据问题

原查询语句 #

SELECT
  a.GtrReference, a.SourceWarehouse, a.TargetWarehouse, /*a.DateCreated,*/ 
  d.ExpectedDueDate, a.EntryType, a.ControlAccount, a.InitialValue,
  a.ValRecToDate, a.Operator, b.Description, c.Description,
  Convert(decimal(14,2), 
  (a.InitialValue - a.ValRecToDate)) as RemainingValue 
FROM [GtrMaster] a WITH (NOLOCK) 
LEFT JOIN [InvWhControl] b WITH (NOLOCK) 
  ON (a.SourceWarehouse = b.Warehouse) 
LEFT JOIN [InvWhControl] c WITH (NOLOCK) 
  ON (a.TargetWarehouse = c.Warehouse) 
LEFT JOIN [GtrDetail] d WITH (NOLOCK) 
  ON (a.GtrReference = d.GtrReference and a.InitialValue = d.InitialValue) 
WHERE ( a.EntryType = 'W' OR a.EntryType = 'S' ) 
  AND a.Complete <> 'Y' AND a.GtrReference >= '' 
ORDER BY a.GtrReference

当前查询结果 #

部分记录的ExpectedDueDate为NULL，仅202301-014能正常取值：

期望结果 #

每个GtrReference都能获取到对应的ExpectedDueDate，同时保持3条唯一记录：

问题原因 #

GtrDetail表中每个GtrReference对应多条记录，GtrMaster表的InitialValue是同一GtrReference下GtrDetail表所有InitialValue的总和。原查询中关联条件a.InitialValue = d.InitialValue导致大部分关联失败，从而出现NULL值。

解决方案 #

调整关联逻辑，去掉a.InitialValue = d.InitialValue的条件，同时通过子查询或窗口函数确保每个GtrReference只取一条ExpectedDueDate（若同一GtrReference下存在多个不同的ExpectedDueDate，可根据业务需求选择取最早、最晚或特定规则的日期）：

方案1：子查询分组取唯一日期 #

SELECT
  a.GtrReference, a.SourceWarehouse, a.TargetWarehouse,
  d.ExpectedDueDate, a.EntryType, a.ControlAccount, a.InitialValue,
  a.ValRecToDate, a.Operator, b.Description, c.Description,
  CONVERT(decimal(14,2), (a.InitialValue - a.ValRecToDate)) AS RemainingValue 
FROM [GtrMaster] a WITH (NOLOCK) 
LEFT JOIN [InvWhControl] b WITH (NOLOCK) 
  ON a.SourceWarehouse = b.Warehouse 
LEFT JOIN [InvWhControl] c WITH (NOLOCK) 
  ON a.TargetWarehouse = c.Warehouse 
LEFT JOIN (
    SELECT GtrReference, MAX(ExpectedDueDate) AS ExpectedDueDate -- 替换为MIN可取最早日期
    FROM [GtrDetail] WITH (NOLOCK)
    GROUP BY GtrReference
) d ON a.GtrReference = d.GtrReference
WHERE a.EntryType IN ('W', 'S') 
  AND a.Complete <> 'Y' 
  AND a.GtrReference >= '' 
ORDER BY a.GtrReference

方案2：窗口函数筛选唯一记录 #

SELECT
  a.GtrReference, a.SourceWarehouse, a.TargetWarehouse,
  d.ExpectedDueDate, a.EntryType, a.ControlAccount, a.InitialValue,
  a.ValRecToDate, a.Operator, b.Description, c.Description,
  CONVERT(decimal(14,2), (a.InitialValue - a.ValRecToDate)) AS RemainingValue 
FROM [GtrMaster] a WITH (NOLOCK) 
LEFT JOIN [InvWhControl] b WITH (NOLOCK) 
  ON a.SourceWarehouse = b.Warehouse 
LEFT JOIN [InvWhControl] c WITH (NOLOCK) 
  ON a.TargetWarehouse = c.Warehouse 
LEFT JOIN (
    SELECT 
      GtrReference, ExpectedDueDate,
      ROW_NUMBER() OVER (PARTITION BY GtrReference ORDER BY ExpectedDueDate DESC) AS rn -- 按日期倒序取第一条，可改ASC取最早
    FROM [GtrDetail] WITH (NOLOCK)
) d ON a.GtrReference = d.GtrReference AND d.rn = 1
WHERE a.EntryType IN ('W', 'S') 
  AND a.Complete <> 'Y' 
  AND a.GtrReference >= '' 
ORDER BY a.GtrReference

说明 #

• 若同一GtrReference下的ExpectedDueDate完全一致，两种方案均适用，子查询的性能表现更优；
• 去掉错误的InitialValue关联条件后，可确保每个GtrMaster记录都能关联到GtrDetail的数据，避免NULL值；
• 通过分组或窗口函数限制每个GtrReference仅返回一条记录，可避免结果集重复。

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