关于SQL查询GROUP BY MAX(KEY)带其他关联字段结果集

原先用

select key,max(value) from tbl group by key
查询出的结果，但是我要再多加一个TEST字段
应该怎么写SQL？性能尽可能好一点的。感谢

看似简单，还真不太好解决，想了半天，可以这样(测试已通过)：

SELECT tb1.key,tb1.value,tb1.test FROM tb1 inner JOIN
(
    Select  key,Max(value) as newvalue
    FROM      tb1
    GROUP BY   key
)T

On T.key = tb1.key AND T.newvalue = tb1.value;

select * from
(
select row_number() over(partition by 'key' order by 'values' desc ) as rownum -- 排序并分组
, * -- 所需显示的字段
from 表
) as T
where T.rownum = 1
这样才是对的，取所有分组中指定值最大或者最小值的那一行的数据。

select key,test，max(value) from tbl group by key，test

select a.id,max(value),(select top 1 test from tbl b where b.id=a.id order by value desc) from tbl a group by id

select a.key,b.value,c.test from tbl a

inner join (select key,max(value) value from tbl group by key) b on b.key =a.key

inner join (select key,value,test from tbl) c on c.key=b.key and c.value=b.value

根据上面网友的语句结合起来，以下是可以显示其他字段，并且考虑排名相同的情况：

select
T.key, T.value,
LISTAGG(T.test, ',') WITHIN GROUP( ORDER BY T.key, T.value) --同名排名用','分隔
from
(
select rank() --跳序排序1、1、3...
over(partition by tb1.key --开窗聚合函数
order by tb1.value desc ) as rownum1,
tb1.key, tb1.value, tb1.test,
from tb1
order by tb1.key asc
) T
where T.rownum1 = 1
group by T.key, T.value;