简单查询以获取每个ID的最大值 [英] Simple Query to Grab Max Value for each ID
本文介绍了简单查询以获取每个ID的最大值的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
好的,我有一个像这样的表:
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.
这篇关于简单查询以获取每个ID的最大值的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文