mysql分组，取每组前几条记录

SELECT  * FROM 表名 a
WHERE (SELECT count(*) FROM 表名 b WHERE b.日期=a.日期 AND b.id>a.id  )<5
 ORDER BY 日期 asc,id desc

类似数据：

对上面数据按照日期分组，每组取qty_ordered最大的前5条:（注意因为我这里条件特殊，所以取5条，写<10）

SELECT * from sales_bestsellers_aggregated_daily AS t1
where  
(SELECT COUNT(*) FROM sales_bestsellers_aggregated_daily AS t2 WHERE t2.period=t1.period AND qty_ordered>t1.qty_ordered) < 10
AND period >= '2016-03-01' AND period <= '2016-03-02' AND store_id=1
ORDER BY t1.period,t1.qty_ordered DESC;

结果如图：