mysql分组取前n条记录_mysql分组取前n条记录

要求：统计统计每天排名前三的手机系统版本

单天统计：select log_date,type,total from dolphin_device_rel where log_date='2013-06-18' and biz_type='os' order by total desc;

方法三、四 0.1s，方法一、四需要1.2s，效果非常显著

方法一：

select log_date,type,total from (select log_date,type,total from dolphin_device_rel where log_date>='2013-06-16' and log_date<='2013-06-18' and biz_type='os') a where 3>(select count(*) from (select log_date,type,total from dolphin_device_rel where log_date>='2013-06-16' and log_date<='2013-06-18' and biz_type='os') b where a.log_date=b.log_date and a.total

取每一条记录，判断统一日期，大于这个总数的是否小于等于三人

方法二：

select log_date,type,total from (

select if(@log_date=log_date,@rank:=@rank+1,@rank:=0) as rank ,@log_date:=log_date,log_date,type,total

from (

SELECT @rank:=0,@log_date:=NUll,log_date,type,total

FROM dolphin_device_rel

WHERE log_date>='2013-06-10' and log_date<='2013-06-18' and biz_type='os' ORDER BY log_date desc,total desc) a) t

where t.rank<3;

每个日期，产生一个行号，只取前三条记录

方法三：询问dba后，其提供优化的语句

SELECT log_date,

type,

total

FROM   (SELECT b.log_date,

b.type,

b.total,

IF(@log_date = b.log_date, @rank := @rank + 1, @rank := 0) AS

rank,

@log_date := b.log_date

FROM   (SELECT log_date,

type,

total

FROM   dolphin_device_rel

WHERE  biz_type='os'

AND log_date>='2013-06-10'

AND log_date<='2013-06-18'

ORDER  BY log_date DESC,

total DESC) b,

(SELECT @rownum := 0,

@log_date := NULL,

@rank := 0) a) result

WHERE  rank <3;

方法四:

select a.log_date,a.type,a.total from (select log_date,type,total from dolphin_device_rel where log_date>='2013-06-16' and log_date<='2013-06-18' and biz_type='os') a left join (select id,log_date,type,total from dolphin_device_rel where log_date>='2013-06-16' and log_date<='2013-06-18' and biz_type='os') b on a.log_date=b.log_date and a.total