php - 求一条sql语句 （分组取前五名）

Question

mysql数据库字段如上图所示，我现在的需求是
就是按stage_id分组后，按照total由大到小递减取得前5条，
现在每一组有30条，总共有8组，
我不想用for循环，循环8次，每次取5条，大家有没有高效的sql语句，求解答

Answer 1

select * from table as a where (select count(distinct(total)) from table as b where a.stage_id = b.stage_id and b.total > a.total) < 5;

解释一下:where中的select是保证遍历所有记录，取每条记录与当前记录做比较，只有当table表中同一stage_id且不超过5个人的total比当前选择item的total高时，这个item就算是每组total排行的前5名。

Answer 2

虽然看起来很奇怪,但是我感觉这种方式效率高,易理解,易读.

select * from table where stage_id = 44 order by total limit 5
union all 
select * from table where stage_id = 45 order by total limit 5
union all 
select * from table where stage_id = 46 order by total limit 5
union all 
select * from table where stage_id = 47 order by total limit 5
union all 
select * from table where stage_id = 48 order by total limit 5
union all 
select * from table where stage_id = 49 order by total limit 5
union all 
select * from table where stage_id = 50 order by total limit 5
union all 
select * from table where stage_id = 51 order by total limit 5

Answer 3

select t1.* from test t1
WHERE (select COUNT(*) from test t2 where t2.stage_id= t1.stage_id and t2.total > t1.total) < 5
ORDER BY t1.stage_id,t1.total 

Answer 4

数据量很小的时候可以用子查询，数据量很大的时候最好是一条条的查

Answer 5

谢邀。对于 select top n by group 的问题，楼上的也给出了一些解决思路，我就不贴sql了。推荐两个链接给你看看。

1. how-to-select-the-firstleastmax-row-per-group-in-sql

2. get-top-n-records-for-each-group-of-grouped-results

Answer 6

数据库不擅长逻辑处理，这些还是由程序来处理较好，另外我也是小白，为何没看到group by 和order by