SQL Server - 如何根据两个表中的日期显示最近的记录 [英] SQL Server - How to display most recent records based on dates in two tables

问题描述

我有两张桌子.我想根据最近的日期列出记录.例如:从下表中，我想使用 select 语句显示 ID 2 和 ID 4.根据第二个表中的日期，ID 2 和 4 是最新的.请帮我查询.谢谢.

I have 2 tables. I Want to list the records based on the recent date. For ex: from the following tables, I want to display ID 2 and ID 4 using a select statement. ID 2 and 4 are the most recent based on the dates from the second table. Please help me with the query. Thank you.

ID EXID PID REASON
1  1    1    XYZ
2  2    1    ABX
3  3    2    NNN
4  4    2    AAA

EXID EXDATE
1    1/1/2011
2    4/1/2011
3    3/1/2011
4    5/1/2011

推荐答案

好的，这应该可以.如果您有任何问题，请告诉我.

Here you go, this ought to do it. Let me know if you have any questions.

SELECT
    TBL.ID,
    TBL.EXDATE
FROM
(
    SELECT
        T1.ID,
        T2.EXDATE,
        ROW_NUMBER() OVER(PARTITION BY T1.PID ORDER BY T2.EXDATE DESC) AS 'RN'
    FROM
        Table1 T1
    INNER JOIN Table2 T2
        ON T1.EXID = T2.EXID
) TBL
WHERE
    TBL.RN = 1

这篇关于SQL Server - 如何根据两个表中的日期显示最近的记录的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！