SQL 从行中获取最大值并加入另一个表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9473718/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Getting max value from rows and joining to another table
提问by user393148
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
What I want is
我想要的是
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.
子查询工作正常，我在做 th join 时遇到问题。
How do I fix this?
我该如何解决？
Thanks.
谢谢。
I have sql server 2008 R2
我有 sql server 2008 R2
Last one is what I missed.
最后一个是我错过的。
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
回答by Conrad Frix
What you wrote was missing A in the from clause so its not entirely clear where you went wrong but this should work
你写的东西在 from 子句中缺少 A 所以它不完全清楚你哪里出错了，但这应该有效
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
See it working here
看到它在这里工作
Alternatively you could use rownumber instead
或者，您可以改用 rownumber
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
See it working here
看到它在这里工作
回答by Marcus Adams
Here's an answer with JOINs instead of MAX():
这是使用 JOIN 而不是 MAX() 的答案：
SELECT DISTINCT b.id, b.filename, a1.name
FROM a a1
JOIN b
ON b.id = a1.id
LEFT JOIN a a2
ON a2.id = a1.id
AND a2.rank > a1.rank
WHERE a2.id IS NULL
If there are no duplicate ranks for the same id, then you don't need the DISTINCT.
如果同一个 id 没有重复的等级，那么你不需要DISTINCT.
