获取每个 ID 的最大值的简单查询

2022-01-31 00:00:00 max sql sql-server greatest-n-per-group

好的，我有一张这样的桌子:

OK I have a table like this:

ID     Signal    Station    OwnerID
111     -120      Home       1
111     -130      Car        1
111     -135      Work       2
222     -98       Home       2
222     -95       Work       1
222     -103      Work       2

这是同一天.我只需要查询返回每个 ID 的最大信号:

This is all for the same day. I just need the Query to return the max signal for each ID:

ID    Signal    Station    OwnerID
111   -120      Home        1
222   -95       Work        1

我尝试使用 MAX() 并且聚合弄乱了每个记录的 Station 和 OwnerID 都不同.我需要加入吗?

I tried using MAX() and the aggregation messes up with the Station and OwnerID being different for each record. Do I need to do a JOIN?

推荐答案

像这样?将您的表与自身连接起来，并排除找到更高信号的行.

Something like this? Join your table with itself, and exclude the rows for which a higher signal was found.

select cur.id, cur.signal, cur.station, cur.ownerid
from yourtable cur
where not exists (
    select * 
    from yourtable high 
    where high.id = cur.id 
    and high.signal > cur.signal
)

这将为每个最高信号列出一行，因此每个 id 可能有多行.

This would list one row for each highest signal, so there might be multiple rows per id.

相关文章