在 sql server 2008 中查詢最後費用日期 (Query for Last Fees Date in sql server 2008)

問題描述

在 sql server 2008 中查詢最後費用日期 (Query for Last Fees Date in sql server 2008)

我是 sql server 的初學者。我有兩個表，TestshriMaster 和 Testshrifees。我需要一份記錄，其中包含學生最後支付的費用詳情。我寫了一個查詢，但有什麼簡單的方法可以做到這一點。表中的幫助讚賞.3 用戶是 "nixon","shri","nixon"。

參考解法

方法 1:

SQL Server supports Standard SQL's Windowed Aggregate Functions, in your case you need a ROW_NUMBER:

select ... 
from TestshriMaster as sm
 (
   select *, 
      ROW_NUMBER()                         ‑‑ apply a ranking 
      OVER (PARTITION BY studentid         ‑‑ for each student
            ORDER BY feesdate DESC ) AS rn ‑‑ based on descending dates
   from Testshrifees
 ) as sf
on sm.studentid = sf.stundentid
where sf.rn = 1                            ‑‑ return only the latest row

(by nichu09、dnoeth)

參考文件

1. Query for Last Fees Date in sql server 2008 (CC BY‑SA 2.5/3.0/4.0)