如何在不将所有列添加到GroupBy子句的情况下返回MAX()值

Question

假设我有这四个表

Serial Make Model
A      AA   123
B      AB   222
C      AA   124

B

HeaderID  OpenDate   Serial
001       1/1/2021   A
002       1/1/2021   B
003       1/2/2021   C

C

HeaderID  SegmentID JobCode
001       001       1A
001       002       1B
002       003       2A

D

SegmentID  Cost    Tax    Date  
001        $25.00  $2.00  1/1/2021
002        $10.00  $1.00  1/2/2021
003        $15.00  $1.00  1/3/2021

我的代码如下：

SELECT B.HeaderID, A.Serial, A.Make, A.Model, B.OpenDate, C.JobCode, D.Cost + D.Tax AS TotalCost
FROM B
INNER JOIN 
A ON A.Serial = B.Serial
INNER JOIN C
ON C.HeaderID = B.HeaderID
INNER JOIN D
ON D.SegmentID = C.SegmentID

这给我的结果是：

HeaderID Serial Make Model OpenDate  JobCode Total Cost
001      A      AA   123   1/1/2021  1A      $27.00 
001      A      AA   123   1/1/2021  1B      $11.00
002      B      AB   222   1/1/2021  2A      $16.00
003      C      AA   124   1/2/2021  NULL    NULL

我现在被要求从D表中为每个HeaderID输入最大日期。所以我的新结果应该是：

HeaderID Serial Make Model OpenDate  JobCode Cost    Date
001      A      AA   123   1/1/2021  1A      $27.00  1/2/2021
001      A      AA   123   1/1/2021  1B      $11.00  1/2/2021
002      B      AB   222   1/1/2021  2A      $16.00  1/3/2021
003      C      AA   124   1/2/2021  NULL    NULL    NULL

有没有一种简单的方法可以简单地将其添加到我现有的查询中，而不必从头开始？提前谢谢？

Answer 1

使用这样的CTE来获取MAX日期，然后LEFT将其连接回原始查询应该可以工作。

;WITH MX
AS (
SELECT HeaderID, MAX([Date]) AS MaxDate
FROM C 
INNER JOIN D
ON D.SegmentID = C.SegmentID
GROUP BY HeaderID
)
SELECT B.HeaderID, A.Serial, A.Make, A.Model, B.OpenDate, C.JobCode, D.Cost + D.Tax AS TotalCost, MX.MaxDate
FROM B
INNER JOIN A 
ON A.Serial = B.Serial
INNER JOIN C
ON C.HeaderID = B.HeaderID
INNER JOIN D
ON D.SegmentID = C.SegmentID
LEFT JOIN MX
ON D.HeaderID = MX.HeaderID