加入2个表，在另一个表上选择最高值

I am trying to Join 2 tables but only select values from table B where id is greatest and 2 columns match between table A & B.

我正在尝试加入2个表,但只选择表B中的值,其中id最大,表A和B之间匹配2列。

I have tried this but it only selects 1 row due to the highest value of table 2 but I need the entire table A to be displayed.

我试过这个但是由于表2的最高值它只选择了1行,但是我需要显示整个表A.

WITH CTE AS 
(
    SELECT DocNum, Status 
    FROM TableB 
    WHERE Id = (SELECT MAX(Id) FROM  TableB)
)
SELECT  TableA.DocNum, DocTitle, cte.Status
FROM TableA
INNER JOIN cte 
ON TableA.DocNum = cte.DocNum

example below:

Table A
    Id DocNum  DocTitle
    1  ST-0001 SomeTitle
    2  ST-0002 SomeTitle
    3  ST-0003 SomeTitle
    4  ST-0004 SomeTitle
    5  ST-0005 SomeTitle

Table B
    Id DocNum Status
    1  ST-0001 Incomplete
    2  ST-0001 Complete
    3  ST-0002 Complete
    4  ST-0003 Incomplete
    5  ST-0004 Incomplete
    6  ST-0004 Complete 
    7  ST-0005 Incomplete   

Result
    DocNum DocTitle Status
    ST-0001 SomeTitle Complete  
    ST-0002 SomeTitle Complete  
    ST-0003 SomeTitle Incomplete  
    ST-0004 SomeTitle Complete  
    ST-0005 SomeTitle Incomplete

1 个解决方案

#1

You can use a CTE with Row_number to get only the rows with the max id per docNum from tableB, and then join to that cte.

您可以使用带有Row_number的CTE来仅获取tableB中每个docNum的最大ID的行,然后加入到该cte。

First, Create and populate sample tables (Please save us this step in your future questions):

首先,创建并填充样本表(请在将来的问题中保存此步骤):

DECLARE @A As Table
(
    Id int,
    DocNum char(7),
    DocTitle varchar(20)
);

DECLARE @B As Table
(
    Id int,
    DocNum char(7),
    [Status] varchar(20)
);

INSERT INTO @A VALUES
(1, 'ST-0001', 'SomeTitle'),
(2, 'ST-0002', 'SomeTitle'),
(3, 'ST-0003', 'SomeTitle'),
(4, 'ST-0004', 'SomeTitle'),
(5, 'ST-0005', 'SomeTitle');

INSERT INTO @B VALUES
(1, 'ST-0001', 'Incomplete'),
(2, 'ST-0001', 'Complete'),
(3, 'ST-0002', 'Complete'),
(4, 'ST-0003', 'Incomplete'),
(5, 'ST-0004', 'Incomplete'),
(6, 'ST-0004', 'Complete'),
(7, 'ST-0005', 'Incomplete');

Then, create the cte with the row_number from tableB:

然后,使用tableB中的row_number创建cte:

;WITH CTE AS
(
    SELECT  Id, 
            DocNum, 
            [Status],
            ROW_NUMBER() OVER(PARTITION BY DocNum ORDER BY Id DESC) As Rn
    FROM @B
)

The query:

SELECT A.DocNum, A.DocTitle, CTE.[Status]
FROM @A A 
INNER JOIN CTE ON A.DocNum = CTE.DocNum
WHERE CTE.rn = 1

Result:

DocNum      DocTitle    Status
ST-0001     SomeTitle   Complete  
ST-0002     SomeTitle   Complete  
ST-0003     SomeTitle   Incomplete  
ST-0004     SomeTitle   Complete  
ST-0005     SomeTitle   Incomplete

You can see a live demo on rextester.

您可以在rextester上看到现场演示。

#1