从行中获取最大值并加入另一个表 [英] Getting max value from rows and joining to another table

问题描述

对不起，如果这是愚蠢的，我真的是一个试图解决这个问题的新手.

Sorry if this is being stupid, I am really a newbie trying to nail this.

Table A:
ID  Rank Name
1   100  Name1
1    45  Name2
2    60  Name3
2    42  Name4
2    88 Name5

Table B:
ID FileName
1  fn1
2  fn2

我想要的是

1 fn1 name1
2 fn2 name5

这就是我的查询的样子，但是当我进行连接时，它给了我多行结果(而不是最大值)

This is what my query looks like, but it gives me multiple rows of results (instead of max) when i do the join

select B.Id B.FileName,A.Name
FRom B
JOIN ( 
select A.Id, MAX(A.Rank)as ExpertRank 
from A 
group by A.Id
) as NewA on A.Id = B.ID 
join B on A.Rank = NewA.Rank

子查询工作正常，我在执行连接时遇到问题.

Sub-query works fine, I get the problem on doing th join.

我该如何解决这个问题?

How do I fix this?

谢谢.

我有 sql server 2008 R2

I have sql server 2008 R2

最后一个是我错过的.

select B.Id B.FileName,A.Name 
FRom B 
JOIN (  
select A.Id, MAX(A.Rank)as ExpertRank  
from A  
group by A.Id 
) as NewA on A.Id = B.ID  
join B on A.Rank = NewA.Rank 
and A.Id = newA.Id

推荐答案

您写的内容在 from 子句中缺少 A，因此不完全清楚您哪里出错了，但这应该可行

What you wrote was missing A in the from clause so its not entirely clear where you went wrong but this should work

select 
       B.Id, 
       B.FileName,
       A.Name
FRom B
     INNER JOIN A
     ON A.id = B.id
    INNER JOIN ( 
          select A.Id, MAX(A.Rank)as ExpertRank 
          from A 
          group by A.Id
     ) as NewA 
    ON a.Id = NewA.ID 
       AND a.Rank = NewA.ExpertRank

在此处

或者你可以使用 rownumber 代替

Alternatively you could use rownumber instead

WITH CTE AS 
(
   SELECT ID, 
          RANK,
          Name,
          ROW_NUMBER() OVER (PARTITION BY ID ORDER BY RANK DESC) rn
   FROM A
)
SELECT b.Id b.FileName,cte.Name
FROM
   b
   INNER JOIN cte 
   ON b.id = cte.id
      and cte.rn = 1

在这里

这篇关于从行中获取最大值并加入另一个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！