时间戳

首页 > 解决方案 > 如何从一组具有相同日期时间的记录中选择最近的记录？

sql - 如何从一组具有相同日期时间的记录中选择最近的记录？

问题描述

所以我有一些我想在图表中显示的记录。目前，如果某些记录具有相同的日期时间，那么它们会在图表中分组，这正是我想要的，但图表显示了这些记录的总和，而不是最后一条记录，但我只是不确定如何实现这一点。

SUM(TAB.Amount) OVER(PARTITION BY TAB.AccountID order by TAB.AccountID, TransactionValueDate, StatementNumber, SequenceNumber, TAB.RecordID) AS [IntraDayBalance]

这是我用来获取这些记录的代码，这些记录在表中完美运行，但我不能在图表中使用它，就像我说它对记录求和一样。那么是否有办法从该列中获取具有相同日期时间的最后一条记录。

不知道如何格式化，但这是一个例子。假设我有 3 条记录（532255.20、2199722.65 和 1995722.65）具有完全相同的日期时间（16/04/2019 06:41:04）。目前在这个日期时间点，该图向我显示了这些记录的总和（4727700.50），但我真正希望它显示的是最后一条记录（1995722.65）。

我当时要问的是我将如何显示最后一条记录而不是总和。请记住，我无法更改上面显示的代码行，因为它非常适合它的原始用途，但不适用于图形，因此图形需要另一行，我只是不知道如何自己做。任何帮助将非常感激！

编辑：谢谢你的帮助。我现在感觉自己像个白痴，但我无法将这些语句中的任何一个合并到我的代码中。我对 T-SQL 还是很陌生，并且仍在学习如何正确格式化代码以及事情的发展方向，但这是我的 SELECT 语句，有关如何将这些语句中的任何一个合并到此代码中的任何提示都会非常有帮助！

        ,TAB.AccountID
        ,TAB.Amount
        ,AC.ShortName
        ,FX.FXRate
        ,FX.IsDivide
        ,CASE 
            WHEN FXRate IS NOT NULL AND FXRate <> 0 THEN
                CASE
                    WHEN FX.IsDivide = 0 THEN
                        ROUND(TAB.Amount / FXRate,2)
                    ELSE
                        ROUND(TAB.Amount * FXRate,2)
                END
            ELSE
                0
        END AS BalanceUSD
        ,ITS.LimitAmount
        ,ITS.FloorAmount
        ,CR.SwiftCode AS Ccy
        ,TAB.RecordType
        ,AC.AccountNumber
        ,AC.BankFileIdentifier AS Accountidentifier
        ,DV.ShortName AS Division
        ,CP.ShortName AS Counterparty
        ,TAB.Amount
        ,SUM(TAB.Amount) OVER(PARTITION BY TAB.AccountID  order by TAB.AccountID, TransactionValueDate, StatementNumber, SequenceNumber, TAB.RecordID) AS [IntraDayBalance]
        ,CAST(29218 + TransactionValueDate AS DATETIME) AS TransactionValueDate
        ,CAST(29218 + TransactionEntryDate AS DATETIME) AS TransactionEntryDate
        ,CASE
            WHEN TransactionFlow = 0 THEN
                TransactionAmount
            ELSE
                -TransactionAmount
        END AS TransactionAmount
        ,CASE 
            WHEN ITS.TransactionFlow = 0 THEN
                ITS.TransactionAmount
            ELSE
                0
        END AS Inflow
        ,CASE 
            WHEN ITS.TransactionFlow = 1 THEN
                ITS.TransactionAmount
            ELSE
                0
        END AS Outflow
        ,TransactionFlow
        ,TransactionCode
        ,CustomerReference
        ,BankReference
        ,Right(RTRIM('0000000000' + CAST(StatementNumber AS CHAR (20))), 10) AS StatementNumber
        ,RTRIM(CAST(ITS.SequenceNumber AS CHAR (20))) AS SequenceNumber
        ,CAST(29218 + StatementDate AS DATETIME) AS StatementDate
        --,DATEADD(SECOND,ImportedByTime,CAST(29218 + StatementDate AS DATETIME))  AS StatementDateTime
        ,CASE
            WHEN TAB.RecordType = 'B' THEN
                @StartDate
            ELSE
                DATEADD(SECOND,ImportedByTime,CAST(29218 + StatementDate AS DATETIME))  
        END AS StatementDateTime
        ,InformationToAccountOwner
        ,Information1
        ,Information2
        ,Information3
        ,Information4
        ,Information5
        ,Information6
        ,ImportedBy
        ,CAST(29218 + ImportedByDate AS DATETIME) AS ImportedByDate
        ,CASE
            WHEN TAB.RecordType = 'B' THEN
                @StartDate
            ELSE
                DATEADD(SECOND,ImportedByTime,CAST(29218 + ImportedByDate AS DATETIME))  
        END AS ImportDateTime
        ,@StartDate AS StartDate
        ,@EndDate AS EndDate
        ,ITS.DebitAmount
        ,ITS.CreditAmount
        ,ITS.FloorAmount
        ,ITS.LimitAmount
        ,ITS.RecordID


FROM CTE_TransactionsAndBalances AS TAB

LEFT OUTER JOIN
    InterimTransactionStatement AS ITS ON TAB.RecordID = ITS.RecordID

LEFT OUTER JOIN
    Accounts AS AC ON TAB.AccountID = AC.ACRecordID

LEFT OUTER JOIN
    Currencies AS CR ON AC.CurrencyID = CR.CRRecordID

LEFT OUTER JOIN
    Divisions AS DV ON AC.DivisionID = DV.DVRecordID

LEFT OUTER JOIN
    Counterparties AS CP ON AC.CounterpartyID = CP.CPRecordID

LEFT OUTER JOIN
    @tbl_FXRates AS FX ON AC.CurrencyID = FX.FromCcyID
    AND FXRateDateN >= FX.StartDateN
    AND FXRateDateN <= FX.EndDateN
    AND FX.ToCcyID = @ReportingCcyID

--WHERE AC.ACRecordID = 94
 order by TAB.AccountID, TransactionValueDate, StatementNumber, SequenceNumber, TAB.RecordID ```

标签： sqlsql-serversql-server-2016

解决方案

使用子查询和row_numberr()

select * from ( select
SUM(TAB.Amount) OVER(PARTITION BY TAB.AccountID  order by TAB.AccountID, TransactionValueDate, StatementNumber, SequenceNumber, TAB.RecordID) AS [IntraDayBalance],
row_number() over(partition by TAB.AccountID order by TransactionValueDate desc) rn
from table
) a where a.rn=1

推荐阅读