对不起,如果这是愚蠢的,我真的是一个新手试图指出这一点.
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
这是我的查询的样子,但是当我进行加入时,它会给我多行结果(而不是max)
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
子查询工作正常,我得到加入的问题.
我该如何解决？
谢谢.
我有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,所以它不完全清楚你在哪里错了,但这应该是正常的
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
看到它工作here
或者,您可以使用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
看到它工作here
