SQL inner join on a column based on max value from another column [duplicate]

This question already has an answer here:

这个问题在这里已有答案:

Retrieving the last record in each group - MySQL 21 answers

检索每个组中的最后一条记录 - MySQL 21的答案

I have two tables, one "master" is a master list of names and the second "scenario" is a list of multiple scenarios for each name from the master list. I want my INNER JOIN query to fetch the master list of ID with the column status from "scenario" table but only the most recent status based on scenarioID. Here's the code that I've tried and tables with desired output

我有两个表,一个“master”是一个主列表名称,第二个“scenario”是主列表中每个名称的多个场景列表。我希望我的INNER JOIN查询从“scenario”表中获取具有列状态的ID的主列表,但仅获取基于scenarioID的最新状态。这是我尝试的代码和具有所需输出的表

SELECT DISTINCT a.[user], a.ID, a.Name, b.status
from master a
INNER JOIN scenario b ON a.ID = b.ID
WHERE
    b.scenarioID = (
           SELECT max(scenarioID) FROM scenario c2 WHERE c2.ID=c.ID)

Master

ID    user    Name
425   John    Skyline
426   John    Violin
427   Joe     Pura

Scenario

ID    ScenarioID    status
425   1             active
425   2             active
425   3             done
426   1             active
426   2             active
427   1             done

Desired output

ID    user    Name    status
425   John    Skyline done
426   John    Violin  active
427   Joe     Pura    done

3 个解决方案

#1

Here's a slightly different formulation that uses a CTE, which I generally find easier to read than a subquery (though of course, your mileage may vary).

这是一个略有不同的使用CTE的配方,我通常发现它比子查询更容易阅读(当然,你的里程可能会有所不同)。

declare @Master table
(
    ID bigint,
    [user] varchar(16),
    Name varchar(16)
);

declare @Scenario table
(
    ID bigint,
    ScenarioID bigint,
    [status] varchar(16)
);

insert @Master values
    (425, 'John', 'Skyline'),
    (426, 'John', 'Violin'),
    (427, 'Joe', 'Pura');
insert @Scenario values
    (425, 1, 'active'),
    (425, 2, 'active'),
    (425, 3, 'done'),
    (426, 1, 'active'),
    (426, 2, 'active'),
    (427, 1, 'done');

with ReversedScenarioCTE as
(
    select
        ID,
        [status],
        rowNumber = row_number() over (partition by ID order by ScenarioID desc)
    from
        @Scenario
)
select
    M.ID,
    M.[user],
    M.Name,
    S.[status]
from
    @Master M
    inner join ReversedScenarioCTE S on
        M.ID = S.ID and
        S.rowNumber = 1;

#2

You can do this with a CROSS APPLY looking up the most recent for each value:

您可以使用CROSS APPLY为每个值查找最新值:

Select  M.ID, M.[User], M.Name, X.Status
From    [Master]    M
Cross Apply
(
    Select  Top 1 S.Status
    From    Scenario    S
    Where   S.ID = M.ID
    Order By S.ScenarioID Desc
) X

Another way you could do it is with a ROW_NUMBER() PARTITIONED on the ID and ORDERED by the ScenarioID DESC:

另一种方法是使用ID上的ROW_NUMBER()PARTITIONED和ScenarioID DESC的ORDERED:

;With OrderedStatuses As
(
    Select  M.Id, M.[User], M.Name, S.Status,
            Row_Number() Over (Partition By S.Id Order By S.ScenarioID Desc) RN
    From    [Master]    M
    Join    Scenario    S   On  S.Id = M.Id
)
Select  Id, [User], Name, Status
From    OrderedStatuses
Where   RN = 1

#3

If you have SQL Server 2008 or later you can use the ROW_NUMBER() function to achieve what you want. It will avoid querying the same table twice or performing joins.

如果您有SQL Server 2008或更高版本,则可以使用ROW_NUMBER()函数来实现所需。它将避免两次查询同一个表或执行连接。

SELECT *
FROM    (

            SELECT   a.[user]
                    ,a.ID
                    ,a.Name
                    ,b.status 
                    ,ROW_NUMBER() OVER (PARTITION BY a.ID ORDER BY b.scenarioID DESC) AS VersionRank
            from [master] a INNER JOIN scenario b ON a.ID = b.ID 
        ) Result
WHERE   Result.VersionRank = 1

#1