SQL左联接，最新记录

debugcn 发表于 Dev

用户名

我只想加入一个具有最新记录的表。我有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')

蒂姆·比格莱森（Tim Biegeleisen）

我喜欢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);