求一条sql语句 (分组取前五名)

clipboard.png

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

阅读 9k
6 个回答
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名。

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

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
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 

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

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

撰写回答
你尚未登录,登录后可以
  • 和开发者交流问题的细节
  • 关注并接收问题和回答的更新提醒
  • 参与内容的编辑和改进,让解决方法与时俱进
1 篇内容引用
推荐问题
宣传栏