根据另一列选择最大日期-解网

问：

我有一个数据集，它具有不同的 submissionid 和 submissiondatetimes 与 invoicenumbers 相关联。我尝试为每个发票号仅选择具有最新提交日期时间的行。示例数据如下，期望的结果是仅返回第 2 行

我尝试过的示例查询如下：

SELECT TOP 1000 
    S.SubissionID,
    BookingID,
    InvoiceNumber,
    InvoiceDate,
    NumberofServices,
    SubmissionDateTime
FROM
    [dbo].[Submission] S
JOIN 
    [dbo].[SubmissionInvoice] SI ON S.SubmissionID = SI.SubmissionID
INNER JOIN 
    (SELECT MAX(SubmissionDateTime maxdate 
     FROM [dbo].[Submission]) m ON m.maxdate = s.SubmissionDateTime

这仅返回具有最新 SubmissionDateTime 的记录，而不考虑 invoicenumber 或 submissionid。

 SELECT TOP 1000 
     S.SubissionID,
     BookingID,
     InvoiceNumber,
     InvoiceDate,
     NumberofServices,
     SubmissionDateTime
 FROM 
     [dbo].[Submission] S
 JOIN 
     [dbo].[SubmissionInvoice] SI ON S.SubmissionID = SI.SubmissionID
                                  AND SubmissionDateTime = (SELECT MAX (SubmissionDateTime) 
                                                            FROM [dbo].[Submission] AS B
                                                            WHERE S.SubmissionID = B.SubmissionID)

这个最大日期过滤似乎没有做任何事情，结果看起来就像我根本没有包含查询的那部分一样。

SQL 最大 maxdate

SELECT SubissionID
       ,BookingID
       ,InvoiceNumber
       ,InvoiceDate
       ,NumberofServices
       ,SubmissionDateTime
FROM (
SELECT S.SubissionID
    ,BookingID
    ,InvoiceNumber
    ,InvoiceDate
    ,NumberofServices
    ,SubmissionDateTime
    ,row_number() over(partition by InvoiceNumber order by SubmissionDateTime DESC) AS RN
FROM [dbo].[Submission] S
JOIN [dbo].[SubmissionInvoice] SI 
  on S.SubmissionID = SI.SubmissionID) t
WHERE RN = 1

上一个：找到最小值和最大值及其位置

下一个：Julia 在满足另一个列表条件的列表中查找最大元素的索引

根据另一列选择最大日期

Select Max Date based on another column

评论