查询最小值对应的非group by字段
测速数据库
同一个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;