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

SELECT  * FROM 表名 aWHERE (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 t1where  (SELECT COUNT(*) FROM sales_bestsellers_aggregated_daily AS t2 WHERE t2.period=t1.period AND qty_ordered>t1.qty_ordered) < 10AND period >= '2016-03-01' AND period <= '2016-03-02' AND store_id=1ORDER BY t1.period,t1.qty_ordered DESC;

结果如图：