本文介紹了最新記錄MS SQL的處理方法，對(duì)大家解決問(wèn)題具有一定的參考價(jià)值，需要的朋友們下面隨著小編來(lái)一起學(xué)習(xí)吧！
問(wèn)題描述
只需要最近的記錄
當(dāng)前數(shù)據(jù)：
RequestID RequestCreateDate VehID DeviceNum ProgramStatus InvID
1 08/12/2018 13:00:00:212 110 20178 Submitted A1
2 08/11/2018 11:12:33:322 110 20178 Pending A1
3 09/08/2018 4:14:28:132 110 Null Cancelled A1
4 11/11/2019 10:12:00:123 188 21343 Open B3
5 12/02/2019 06:15:00:321 188 21343 Submitted B3
請(qǐng)求結(jié)果：
RequestID RequestCreateDate VehID DeviceNum ProgramStatus InvID
3 09/08/2018 4:14:28:132 110 Null Cancelled A1
5 12/02/2019 06:15:00:321 188 21343 Submitted B3
InvID來(lái)自我要加入的表B。
以下是我當(dāng)前正在嘗試的查詢，但存在重復(fù)記錄：
Select
max(t1.RequestID) ReqID,
max(t1.RequestCreateDate) NewDate,
t1.VehID,
t1.DeviceNum,
t1.ProgramStatus,
t2.InvID
FROM table1 t1
LEFT JOIN table2 t2 ON t1.VehID = t2.VehID
GROUP BY t1.VehID, t1.DeviceNum, t1.ProgramStatus, t2.InvID
我只需要每個(gè)Vehid的最新記錄。謝謝
推薦答案
On選項(xiàng)是使用子查詢進(jìn)行篩選：
select t1.*, t2.invid
from table1
left join table2 t2 on t1.vehid = t1.vehid
where t1.requestCreateDate = (
select max(t11.requestCreateDate)
from table1 t11
where t11.vehid = t1.vehid
)
為提高性能，請(qǐng)考慮table1(vehid, requestCreateDate)上的索引。
您還可以使用row_number()：
select *
from (
select t1.*, t2.invid, row_number() over(partition by vehid order by requestCreateDate desc) rn
from table1
left join table2 t2 on t1.vehid = t1.vehid
) t
where rn = 1
這篇關(guān)于最新記錄MS SQL的文章就介紹到這了，希望我們推薦的答案對(duì)大家有所幫助，
