返回一列中具有最大值的行，而另一列中的给定值则返回该行 [英] Return rows with the highest value on one column corresponding to a given value in another

问题描述

有一个名为 raw_contacts 的MySQL表，其结构如下:

There's a MySQL table named raw_contacts with the following structure:

ID (primary auto-increment, int)
PHONE (composite unique with NAME, varchar)
NAME (composite unique with PHONE, varchar)
FREQUENCY (int)
Composite unique key is named PHONENUM

我正在尝试编写查询以针对FONE的任何给定值返回FREQUENCY列中具有最高对应值的行.我确实找到了相关帖子，但是可接受的答案不适用于我的情况，因为我有两列的复合键来进行检查.

I am trying to write a query to return the row with the highest corresponding value in the FREQUENCY column for any given value for PHONE. I did find a related post but the accepted answer doesn't work for my case since I have a composite key of two columns to run the check against.

仅出于说明目的，请考虑以下示例:

Just to illustrate, consider the following sample:

1 1234 John 6
2 1234 Dave 2
3 2199 Bill 9
4 1878 Dani 3
5 1234 Cory 7
6 1234 Gore 5
7 3319 Cory 1

在上述示例中针对1234运行，由于Cory在FREQUENCY列中具有该编号的最高计数，因此查询应返回第5行.

Run against 1234 on the above sample, the query should return the 5th row since Cory has the highest count in the FREQUENCY column for that number.

这是我想出的，它的效果很好:

Here's what I've come up with and it works great:

select RC.ID, RC.PHONE, RC.FREQUENCY, RC.NAME
from `raw_contacts` RC
inner join(
    select PHONE, max(FREQUENCY) FREQUENCY
    from `raw_contacts`
    where PHONE="11111"
    group by PHONE
) RC2 on RC.PHONE = RC2.PHONE and RC.FREQUENCY = RC2.FREQUENCY
limit 1

此任务是否需要更多资源友好的查询，因为它需要在具有数百万条记录的表上以很高的频率运行?我正在寻找最优化的方法！

Is there any more resource-friendly query for this task since it needs to run at a very high frequency on a table with millions of records? I'm looking to optimize it to the bone!

P.S..如果排位赛超过一排，我只需要其中一排，没关系.

P.S. In case of more than one rows qualifying, I need only one of them, which one doesn't matter.

推荐答案

join在DB中是一项昂贵的操作，我认为您必须尽可能避免使用它！！！我建议使用以下查询并将其结果与您的查询进行比较.

join is a costly operation in DB and I think you must avoid using it as much as possible!!! I suggest to use the following query and compare its result with your one.

select * from `raw_contacts` RC1 where PHONE="11111" 
and FREQUENCY>=all (select FREQUENCY from `raw_contacts` RC2 where RC2.PHONE=RC1.PHONE) LIMIT 1

您还可以考虑使用不同类型的索引编制(在这里)以加快特殊查询和更频繁查询的速度

also you can consider different types of indexing (a good toturial is here) on your table to speedup special and more frequent queries

这篇关于返回一列中具有最大值的行，而另一列中的给定值则返回该行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！