MySQL实现分组排序(8.0版本以下ROW_NUMBER() OVER())
drop table if exists heyf_t10; create table heyf_t10 (empid int ,deptid int ,salary decimal(10,2) ); insert into heyf_t10 values (1,10,5500.00), (2,10,4500.00), (3,20,1900.00), (4,20,4800.00), (5,40,6500.00), (6,40,14500.00), (7,40,44500.00), (8,50,6500.00), (9,50,7500.00); 2. 确定需求: 根据部门来分组,显示各员工在部门里按薪水排名名次. 显示结果预期如下: +-------+--------+----------+------+ | empid | deptid | salary | rank | +-------+--------+----------+------+ | 1 | 10 | 5500.00 | 1 | | 2 | 10 | 4500.00 | 2 | | 4 | 20 | 4800.00 | 1 | | 3 | 20 | 1900.00 | 2 | | 7 | 40 | 44500.00 | 1 | | 6 | 40 | 14500.00 | 2 | | 5 | 40 | 6500.00 | 3 | | 9 | 50 | 7500.00 | 1 | | 8 | 50 | 6500.00 | 2 | +-------+--------+----------+------+ 3. SQL 实现 select empid,deptid,salary,rank from ( select heyf_tmp.empid,heyf_tmp.deptid,heyf_tmp.salary,@rownum:=@rownum+1 , if(@pdept=heyf_tmp.deptid,@rank:=@rank+1,@rank:=1) as rank, @pdept:=heyf_tmp.deptid from ( select empid,deptid,salary from heyf_t10 order by deptid asc ,salary desc ) heyf_tmp ,(select @rownum :=0 , @pdept := null ,@rank:=0) a ) result ; 4. 结果演示 mysql> select empid,deptid,salary,rank from ( -> select heyf_tmp.empid,heyf_tmp.deptid,heyf_tmp.salary,@rownum:=@rownum+1 , -> if(@pdept=heyf_tmp.deptid,@rank:=@rank+1,@rank:=1) as rank, -> @pdept:=heyf_tmp.deptid -> from ( -> select empid,deptid,salary from heyf_t10 order by deptid asc ,salary desc -> ) heyf_tmp ,(select @rownum :=0 , @pdept := null ,@rank:=0) a ) result -> ; +-------+--------+----------+------+ | empid | deptid | salary | rank | +-------+--------+----------+------+ | 1 | 10 | 5500.00 | 1 | | 2 | 10 | 4500.00 | 2 | | 4 | 20 | 4800.00 | 1 | | 3 | 20 | 1900.00 | 2 | | 7 | 40 | 44500.00 | 1 | | 6 | 40 | 14500.00 | 2 | | 5 | 40 | 6500.00 | 3 | | 9 | 50 | 7500.00 | 1 | | 8 | 50 | 6500.00 | 2 | +-------+--------+----------+------+ 9 rows in set (0.00 sec) MySql中取出每个分组中的前N条记录 select a1.* from article a1 inner join (select a.type,a.date from article a left join article b on a.type=b.type and a.date<=b.date group by a.type,a.date having count(b.date)<=2 )b1 on a1.type=b1.type and a1.date=b1.date order by a1.type,a1.date desc
【推荐】国内首个AI IDE,深度理解中文开发场景,立即下载体验Trae
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· 记一次.NET内存居高不下排查解决与启示
· 探究高空视频全景AR技术的实现原理
· 理解Rust引用及其生命周期标识(上)
· 浏览器原生「磁吸」效果!Anchor Positioning 锚点定位神器解析
· 没有源码,如何修改代码逻辑?
· 全程不用写代码,我用AI程序员写了一个飞机大战
· DeepSeek 开源周回顾「GitHub 热点速览」
· MongoDB 8.0这个新功能碉堡了,比商业数据库还牛
· 记一次.NET内存居高不下排查解决与启示
· 白话解读 Dapr 1.15:你的「微服务管家」又秀新绝活了