SQL Server如何关联两张表查询每个Item对应最新日期的记录

现有两张表tableA和tableB，建表语句及测试数据如下：

tableA 建表及测试数据 #

create table tableA
(ID int, DocumentDate datetime2, DocumentName varchar(3), ItemID int);

insert into tableA (ID, DocumentDate, DocumentName, ItemID)
values (1, '2019-08-01 12:00:00', 'A-1', 1),
(2, '2020-05-12 13:00:00', 'B-2', 1),
(3, '2021-07-01 14:00:00', 'C-3', 1),
(4, '2020-01-01 12:00:00', 'D-4', 2),
(5, '2021-02-01 13:00:00', 'E-5', 2),
(6, '2021-07-02 14:00:00', 'F-6', 2);

tableB 建表及测试数据 #

create table tableB
(ID int, ItemCode varchar(3));

insert into tableB (ID, ItemCode)
values (1, 'AAA'),
(2, 'BBB');

现有查询逻辑 #

目前已经编写的SQL Server查询语句如下：

select 
    A.ID, 
    A.DocumentDate, 
    A.DocumentName, 
    B.ItemCode 
from tableA A
left join tableB B on B.ID = A.ItemID

需求 #

需要筛选出每个ItemCode对应的DocumentDate最新的记录，最终结果需要返回ItemCode为AAA的第3条记录、ItemCode为BBB的第6条记录。

可以使用SQL Server自带的窗口函数ROW_NUMBER()实现分组排序后取最新值，完整查询语句如下：

WITH RankedRecords AS (
    SELECT 
        A.ID, 
        A.DocumentDate, 
        A.DocumentName, 
        B.ItemCode,
        ROW_NUMBER() OVER (PARTITION BY B.ItemCode ORDER BY A.DocumentDate DESC) AS rank_num
    FROM tableA A
    LEFT JOIN tableB B ON B.ID = A.ItemID
)
SELECT ID, DocumentDate, DocumentName, ItemCode
FROM RankedRecords
WHERE rank_num = 1

逻辑说明 #

• 先用CTE（公共表表达式）给每条记录打排名标记，PARTITION BY B.ItemCode 实现按物料编码分组，每个分组内部单独排名
• ORDER BY A.DocumentDate DESC 规定分组内按单据日期倒序排列，最新的日期排名为1
• 最终只筛选排名为1的记录，就是每个ItemCode对应的最新单据记录

如果存在同一个ItemCode下有多条完全相同的DocumentDate记录，且需要全部返回的话，把ROW_NUMBER()替换为RANK()即可。

内容的提问来源于stack exchange，提问作者Irwan Rahman S