按InvoiceNumber筛选对应最新SubmissionDateTime的SQL实现问题

你需要为每个InvoiceNumber筛选出对应最新SubmissionDateTime的行，但现有两个查询都存在问题：

• 第一个查询：子查询计算的是全表全局的最新提交时间，没有按InvoiceNumber分组统计，因此只能返回全表最新的单条记录，无法满足按发票号单独筛选的需求。
• 第二个查询：子查询的关联条件是S.SubmissionID = B.SubmissionID，这意味着每个提交记录自己的最大时间就是自身的SubmissionDateTime，相当于没有做任何有效筛选，所以结果和未添加筛选条件时完全一致。

方法1：窗口函数（推荐，简洁高效） #

利用ROW_NUMBER()窗口函数，按InvoiceNumber分组后，对每组的SubmissionDateTime做降序排序，取每组的第一行（即最新时间的记录）：

SELECT SubissionID, BookingID, InvoiceNumber, InvoiceDate, NumberofServices, SubmissionDateTime
FROM (
    SELECT 
        S.SubissionID,
        S.BookingID,
        SI.InvoiceNumber,
        SI.InvoiceDate,
        S.NumberofServices,
        S.SubmissionDateTime,
        -- 按发票号分组，提交时间倒序排列，每组第一条标记为1
        ROW_NUMBER() OVER (PARTITION BY SI.InvoiceNumber ORDER BY S.SubmissionDateTime DESC) AS rn
    FROM [dbo].[Submission] S
    JOIN [dbo].[SubmissionInvoice] SI ON S.SubmissionID = SI.SubmissionID
) AS temp
WHERE rn = 1;

方法2：分组子查询关联 #

先按InvoiceNumber分组计算每个发票号对应的最新提交时间，再通过关联原表获取对应完整记录：

SELECT 
    S.SubissionID,
    S.BookingID,
    SI.InvoiceNumber,
    SI.InvoiceDate,
    S.NumberofServices,
    S.SubmissionDateTime
FROM [dbo].[Submission] S
JOIN [dbo].[SubmissionInvoice] SI ON S.SubmissionID = SI.SubmissionID
JOIN (
    -- 先统计每个InvoiceNumber的最新提交时间
    SELECT 
        SI.InvoiceNumber,
        MAX(S.SubmissionDateTime) AS max_submit_time
    FROM [dbo].[Submission] S
    JOIN [dbo].[SubmissionInvoice] SI ON S.SubmissionID = SI.SubmissionID
    GROUP BY SI.InvoiceNumber
) AS latest ON SI.InvoiceNumber = latest.InvoiceNumber 
           AND S.SubmissionDateTime = latest.max_submit_time;

补充说明 #

如果同一个InvoiceNumber存在多条提交时间完全相同的最新记录，方法1会只取其中一条（按默认排序规则）；若需要保留所有同时间的最新记录，可将方法1中的ROW_NUMBER()替换为RANK()。

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