SQL 与最新记录左连接

Question

我只想左连接一个仅包含最新记录的表。我有 Customer1 表:

+--------+----------+
| CustID | CustName |
+--------+----------+
|      1 | ABC123   |
|      2 | 456XYZ   |
|      3 | 5PQR3    |
|      4 | 789XYZ   |
|      5 | 789A     |
+--------+----------+

SalesInvoice 表:

+------------+--------+-----------+
|  InvDate   | CustID | InvNumber |
+------------+--------+-----------+
| 2020-03-01 |      1 | IV236     |
| 2020-04-07 |      1 | IV644     |
| 2020-06-13 |      2 | IV869     |
| 2020-03-29 |      3 | IV436     |
| 2020-02-06 |      3 | IV126     |
+------------+--------+-----------+

我想要这个所需的输出:

+--------+------------+-----------+
| CustID |  InvDate   | InvNumber |
+--------+------------+-----------+
|      1 | 2020-04-07 | IV644     |
|      2 | 2020-06-13 | IV869     |
|      3 | 2020-03-29 | IV436     |
|      4 |            |           |
|      5 |            |           |
+--------+------------+-----------+

为了快速简便，下面是示例代码。

    drop table if exists #Customer1
    create table #Customer1(CustID int, CustName varchar (100))
    insert into #Customer1 values
    (1,'ABC123'),
    (2,'456XYZ'),
    (3,'5PQR3'),
    (4,'789XYZ'),
    (5,'789A')
    
    
    drop table if exists #SalesInvoice
    create table #SalesInvoice(InvDate DATE, CustID INT, InvNumber varchar (100))
    insert into #SalesInvoice values
    ('2020-03-01',1,'IV236'),
    ('2020-04-07',1,'IV644'),
    ('2020-06-13',2,'IV869'), 
    ('2020-03-29',3,'IV436'),
    ('2020-02-06',3,'IV126')

Answer 1

在这种情况下，我喜欢使用TOP 1 WITH TIES:

SELECT TOP 1 WITH TIES c.CustID, i.InvDate, i.InvNumber
FROM #Customer1 c
LEFT JOIN #Invoices i ON c.CustID = i.CustID
ORDER BY ROW_NUMBER() OVER (PARTITION BY c.CustID ORDER BY i.InvDate DESC);

Demo

这里的第一个技巧是按行号排序，为每个客户分配一个序列，序列按发票日期降序排列。然后，此方法仅保留每个客户的最新发票记录。