求助：如何按Job Reference分组获取最新交易日期的SQL查询

你的子查询存在几个关键问题：

• MAX('TransactionDate') 中的单引号错误，数据库会把它当成字符串常量而非字段，应改为 MAX(AT.TransactionDate)
• 关联条件 AT.DeliveryAddressID = J.JobID 逻辑错误，DeliveryAddressID是CustomerAddress的ID，和JobID不属于同一关联维度
• 分组维度错误，你需要按JobReference分组，而非DeliveryAddressID

下面提供两种最优解决方案：

方案1：子查询预聚合关联 #

先聚合每个JobReference对应的最新交易日期，再关联回原查询筛选匹配记录：

SELECT
    C.CustomerCode,
    C.name AS Customer,
    B1.Name AS 'Customer Home Branch',
    B.Name AS 'Trx Branch',
    At.DocumentNumber AS 'Document Number',
    At.TransactionDate AS 'Document Date',
    At.PaymentDueDate AS 'Due Date',
    CASE 
        WHEN At.TransactionType = 1 THEN 'Invoice' 
        WHEN at.transactiontype = 2 THEN 'CreditNote' 
        WHEN at.transactiontype = 3 THEN 'FC' 
        WHEN AT.transactiontype = 4 THEN 'Payment' 
        WHEN At.TransactionType = 8 THEN 'Adj' 
        ELSE '' 
    END AS Type, 
    CA.AddressCode,
    J.JobReference,
    At.OriginalAmount AS 'Original Amount',
    At.AmountOutstanding AS 'Outstanding Amount',
    DATEDIFF(DAY, AT.PaymentDueDate, GETDATE()) AS 'Days Past Due',
    DATEDIFF(DAY, At.TransactionDate, GETDATE()) AS 'Days From Shipment',
    C.customerID,
    AT.AccountsTransactionID
FROM
    AccountsTransaction AS AT WITH (NOLOCK)
LEFT JOIN
    Customer AS C WITH(NOLOCK) ON C.customerid = AT.CustomerID
LEFT JOIN
    Branch AS B WITH(NOLOCK) ON B.branchid = AT.BranchID
LEFT JOIN
    Branch AS B1 WITH(NOLOCK) ON B1.branchid = C.HomeBranchID
LEFT JOIN
    CustomerAddress AS CA WITH(NOLOCK) ON CA.CustomerAddressID = AT.DeliveryAddressID
LEFT JOIN
    Job AS J WITH(NOLOCK) ON j.jobID = CA.JobID
INNER JOIN (
    SELECT
        J.JobReference,
        MAX(AT.TransactionDate) AS LatestTransactionDate
    FROM
        AccountsTransaction AS AT
    LEFT JOIN CustomerAddress AS CA ON CA.CustomerAddressID = AT.DeliveryAddressID
    LEFT JOIN Job AS J ON J.JobID = CA.JobID
    WHERE J.JobReference IS NOT NULL
    GROUP BY J.JobReference
) AS JobLatest 
    ON J.JobReference = JobLatest.JobReference 
    AND AT.TransactionDate = JobLatest.LatestTransactionDate

方案2：窗口函数（推荐，逻辑更清晰） #

使用ROW_NUMBER()窗口函数，按JobReference分组后给交易记录按日期倒序排名，取排名为1的最新记录：

WITH RankedTransactions AS (
    SELECT
        C.CustomerCode,
        C.name AS Customer,
        B1.Name AS 'Customer Home Branch',
        B.Name AS 'Trx Branch',
        At.DocumentNumber AS 'Document Number',
        At.TransactionDate AS 'Document Date',
        At.PaymentDueDate AS 'Due Date',
        CASE 
            WHEN At.TransactionType = 1 THEN 'Invoice' 
            WHEN at.transactiontype = 2 THEN 'CreditNote' 
            WHEN at.transactiontype = 3 THEN 'FC' 
            WHEN AT.transactiontype = 4 THEN 'Payment' 
            WHEN At.TransactionType = 8 THEN 'Adj' 
            ELSE '' 
        END AS Type, 
        CA.AddressCode,
        J.JobReference,
        At.OriginalAmount AS 'Original Amount',
        At.AmountOutstanding AS 'Outstanding Amount',
        DATEDIFF(DAY, AT.PaymentDueDate, GETDATE()) AS 'Days Past Due',
        DATEDIFF(DAY, At.TransactionDate, GETDATE()) AS 'Days From Shipment',
        C.customerID,
        AT.AccountsTransactionID,
        ROW_NUMBER() OVER (PARTITION BY J.JobReference ORDER BY AT.TransactionDate DESC) AS TrxRank
    FROM
        AccountsTransaction AS AT WITH (NOLOCK)
    LEFT JOIN
        Customer AS C WITH(NOLOCK) ON C.customerid = AT.CustomerID
    LEFT JOIN
        Branch AS B WITH(NOLOCK) ON B.branchid = AT.BranchID
    LEFT JOIN
        Branch AS B1 WITH(NOLOCK) ON B1.branchid = C.HomeBranchID
    LEFT JOIN
        CustomerAddress AS CA WITH(NOLOCK) ON CA.CustomerAddressID = AT.DeliveryAddressID
    LEFT JOIN
        Job AS J WITH(NOLOCK) ON j.jobID = CA.JobID
    WHERE J.JobReference IS NOT NULL
)
SELECT *
FROM RankedTransactions
WHERE TrxRank = 1

方案差异说明 #

• 方案1：如果同一Job在同一时间有多条交易记录，会返回所有符合条件的记录
• 方案2：默认只返回同一Job下最新日期的一条记录；若需保留同日期的所有最新记录，可将ROW_NUMBER()替换为RANK()

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