mysql 获取排序后分组获取第一条数据(分组获取某个值最大的记录)
方式0(mysql8支持,利用窗口函数,优先推荐)
SELECT * FROM ( SELECT id, student_id, score, clazz_id, ROW_NUMBER() OVER( PARTITION BY clazz_id ORDER BY clazz_id ASC,score DESC) as row_num FROM student_score ORDER BY clazz_id,row_num ) temp where row_num <= 3
方式1,时间最大,sql简单
SELECT * FROM ( SELECT * FROM TABLE t WHERE 1 HAVING 1 ORDER BY t.create_time DESC ) a GROUP BY user_id ORDER BY create_time DESC;
方式2(推荐),分数最高,同样的下方的也支持按照某一个条件分组取每个组前N条,把count <= 1 改成对应的条数就行了
SELECT * FROM ( SELECT id, student_id, score, clazz_id , @rownum:=IF(@clazz_id=clazz_id,@rownum+1,1) count, @clazz_id := clazz_id FROM student_score, (select @rownum := 0,@clazz_id:=0) r ORDER BY clazz_id,score DESC -- 此处把相同班级的拍在一起用于计算行编号,再把分数降序排序 ) temp WHERE count <= 1 -- 分数降序的,直接取出最前1条
分享一个淘宝、京东、拼多多、饿了么、美团、抖音等等买东西后真实返钱小技巧,
打车、外卖领券,充值话费95折好像也可以
使用教程用微信扫下方二维码查看详细说明
![](https://img2023.cnblogs.com/blog/1127940/202306/1127940-20230614113052106-1557464839.jpg)