如何獲得某個日期之前的最新余額（t-sql）

我遇到了一個問題，我試圖在 2020 年 4 月 30 日之前獲得最新的余額。

表Customer具有以下列：

CustomerId, CustomerName, CustomerCity, CustomerCurrentBalance. 

表Customer_Transaction_Entry具有以下列：

TransactionNumber, CustomerId, Country, Created, Amount, Details, Balance

到目前為止，這是我的查詢：

select 
    dbo.Customer_Transaction_Entry.CustomerId,  
    dbo.Customer_Transaction_Entry.Country, 
    dbo.Customer_Transaction_Entry.Balance
from 
    dbo.Customer_Transaction_Entry
join
    dbo.Customer on Customer.CustomerId = Customer_Transaction_Entry.CustomerId
where 
    Customer_Transaction_Entry.Created < '2020-04-30'
order by 
    CustomerId

這里的問題是我在此日期之前獲得了所有交易。但我需要在此日期之前的最后一個，因為它是最新的，我必須將其顯示為按 BalanceDate 分組的客戶的當前余額。

以下是 dbo.Customer_Transaction_Entry 的示例資料：

TransactionNo CustomerId Country Created Amount Details Balance
10001   1   Country1    2020-01-01  80.000000   Purchase    80.000000
10002   1   Country1    2020-02-06  20.000000   Payment 60.000000
10003   1   Country1    2020-02-06  120.000000  Purchase    180.000000
10004   1   Country1    2020-02-23  20.000000   Payment 160.000000
10005   1   Country1    2020-04-06  20.000000   Payment 140.000000
10006   1   Country1    2020-05-06  120.000000  Purchase    260.000000
10007   1   Country1    2020-06-23  20.000000   Payment 240.000000
10008   4   Country1    2020-01-01  80.000000   Purchase    80.000000
10009   4   Country1    2020-02-06  20.000000   Payment 60.000000
10010   4   Country1    2020-02-06  120.000000  Purchase    180.000000
10011   4   Country1    2020-02-23  20.000000   Payment 160.000000
10012   4   Country1    2020-04-06  20.000000   Payment 140.000000
10013   4   Country1    2020-06-23  20.000000   Payment 248.000000
10014   21  Country2    2020-01-01  80.000000   Purchase    80.000000
10015   21  Country2    2020-02-06  20.000000   Payment 60.000000
10016   21  Country2    2020-02-06  120.000000  Purchase    180.000000
10017   21  Country2    2020-02-23  20.000000   Payment 160.000000
10018   21  Country2    2020-05-09  20.000000   Payment 140.000000
10019   21  Country2    2020-05-09  142.000000  Purchase    282.000000
10020   21  Country2    2020-07-23  20.000000   Payment 262.000000
10023   4   Country1    2020-04-06  128.000000  Purchase    268.000000

對于較小的 dbo.Customer：

CustomerId CustomerName CustomerCity CustomerCurrentBalance
1      CustomerName1    CustomerCity    NULL
2      CustomerName2    CustomerCity    NULL
3       CustomerName3   CustomerCity    NULL
4       CustomerName4   CustomerCity    NULL
6       CustomerName6   CustomerCity    NULL
13      CustomerName13  CustomerCity    NULL
21     CustomerName21   CustomerCity    NULL
22     CustomerName22   CustomerCity    NULL
23      CustomerName23  CustomerCity    NULL

想要的結果應該是：

BalanceDate CustomerId Country   Balance

2020-04-30   1         Country1  140
2020-04-30   4         Country1  268
2020-04-30   21        Country2  160

uj5u.com熱心網友回復：

您可以使用 row_number()視窗函式查找每個客戶的最新行：

with t as (
  select t.created as BalanceDate, t.CustomerId, t.Country, t.Balance,
    Row_Number() over(partition by t.CustomerId order by t.TransactionNo desc) rn
  from dbo.Customer c
  join dbo.Customer_Transaction_Entry t on t.CustomerId = c.CustomerId
  where t.Created < '20200430'
)
select BalanceDate, CustomerId, Country, Balance
from t
where rn=1

另請注意如何使用別名使查詢更緊湊且更易于閱讀。

標籤：sql-server 查询语句 短信

上一篇：級聯從SQL中的多個表中洗掉行

下一篇：在臨時表中插入多行后，會在臨時表的末尾插入一個新行