row_number() over partition by 分组聚合

最新推荐文章于 2024-10-25 15:27:57 发布

原创最新推荐文章于 2024-10-25 15:27:57 发布 · 5.4k 阅读

3 ·

CC 4.0 BY-SA版权

文章标签：

#数据库 #sql #oracle

数据库专栏收录该内容

48 篇文章

订阅专栏

本文介绍如何使用SQL的row_number()函数进行分组排序，并通过实际案例演示如何在不同数据库中实现这一功能，包括MySQL、Oracle及SQL Server。此外，还展示了如何限制返回的行数。

row_number() over partition by 分组聚合

分组聚合，就是先分组再排序，可以的话顺手标个排名；如果不想分组也可以排名；如果不想分组同时再去重排名也可以

Oracle和SQL server的关键字是over partition by
mysql的无关键字row_number() over (partition by col1 order by
col2),表示根据col1分组，在分组内部根据col2排序

-- 建表
USE db_03;
DROP TABLE IF EXISTS employee;
create table employee (empid int ,deptid int ,salary decimal(10,2));
insert into employee values(1,10,5500.00);
insert into employee values(2,10,4500.00);
insert into employee values(3,20,1900.00);
insert into employee values(4,20,4800.00);
insert into employee values(5,40,6500.00);
insert into employee values(6,40,14500.00);
insert into employee values(7,40,44500.00);
insert into employee values(8,50,6500.00);
insert into employee values(9,50,7500.00);

SELECT * FROM employee;

SELECT *, Row_Number() OVER (partition by deptid ORDER BY salary desc) rank FROM employee

在这里插入图片描述
mysql

SET @row=0;
SET @groupid='';
select a.deptid,a.salary
from
(
select deptid,salary,case when @groupid=deptid then @row:=@row+1 else @row:=1 end rownum,@groupid:=deptid from employee
order by deptid,salary desc
)a
where a.rownum<=2;

在这里插入图片描述

SELECT t.stuid,  
        t.stuname,  
        t.score,  
        t.classid  
FROM stugrade t  
where t.score = (SELECT max(tmp.score) from stugrade tmp where tmp.classid=t.classid)