mysql 最小值对应的其他属性_查询最小值对应的非group by字段

最新推荐文章于 2023-04-12 21:25:07 发布

帅小伙-路飞

最新推荐文章于 2023-04-12 21:25:07 发布

阅读量662

点赞数

文章标签： mysql 最小值对应的其他属性

本文链接：https://blog.csdn.net/weixin_28814457/article/details/113283456

版权

测速数据库

同一个ip有多条记录，要查询每个ip里最小时延对应的isp信息。

方法一：将ip和min(时延)的查询结果建新表t，再联合原表查询

1、create table t(ip int, wAverDelay smallint, primary key (ip));

2、insert into t select ip, min(wAverDelay) as wAverDelay from hummer_ip_speed_addip_20151108_Hash55 group by ip;

1、2步也可以用：create table t select ip, min(wAverDelay) as wAverDelay from hummer_ip_speed_addip_20151108_Hash55 group by ip;

3、select isp2, count(*) from (select isp2 from hummer_ip_speed_addip_20151108_Hash55, t where hummer_ip_speed_addip_20151108_Hash55.isMultiIP=1 and hummer_ip_speed_addip_20151108_Hash55.ip=t.ip and hummer_ip_speed_addip_20151108_Hash55.wAverDelay=t.wAverDelay)tmp group by tmp.isp2;

方法二：先排序，再取每个ip对应的第一条记录。效率低一点

select isp2, count(*) from (select isp2 from (select * from hummer_ip_speed_addip_20151108_Hash55 where isMultiIP=1 order by ip, wAverDelay)A group by A.ip)B group by B.isp2;