选择每个项目的最新日期

Question

我有两张桌子，假设是tableA和tableB。

表A

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);

这是B表

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

和结果should be like this

我想为AAA选择第三个，为BBB选择第六个，它有最新的日期。谢谢。

Answer 1

您可以使用apply

select b.*, a.*
from tableB b outer apply
     (select top (1) a.*
      from tableA a
      where a.itemId = b.Id
      order by a.documentdate desc
     ) a;

使用tableA(item, documentdate)上的索引，这通常会有非常好的性能

Answer 2

看起来很简单：

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

ROW_NUMBER