SQL - 获得最大结果

Question

假设下面有一个名为“test”的表:

name    value
n1        1
n2        2
n3        3

现在，我想获取具有最大值的名称，我有以下一些解决方案:

解决方案一:

SELECT TOP 1 name 
FROM test 
ORDER BY value DESC

方案二:

SELECT name 
FROM test 
WHERE value = (SELECT MAX(value) FROM test);

现在，我希望使用连接操作来查找结果，比如

SELECT name 
FROM test 
INNER JOIN test ON...

有人可以帮忙解释一下它是如何工作的吗？

Answer 1

如果你正在寻找 JOIN 那么

SELECT T.name, T.value
    FROM test T
INNER JOIN 
(   SELECT T1.name, T1.value ,
        RANK() OVER (PARTITION BY T1.name ORDER BY T1.value) N
    FROM test T1
    WHERE T1.value IN (SELECT MAX(t2.value) FROM test T2)
)T3 ON T3.N = 1 AND T.name = T3.name

FIDDLE DEMO

或

select  name, value
from
(
  select  name, value,
    row_number() over(order by value desc) rn
  from test
) src
where rn = 1

FIDDLE DEMO