MySQL分组聚类partition关键字的替代方法

最新推荐文章于 2026-08-09 21:52:35 发布

转载最新推荐文章于 2026-08-09 21:52:35 发布 · 1.8k 阅读

0 ·

本内容遵循CC 4.0 BY-SA版权协议

原文链接：https://www.cnblogs.com/starzy/p/11146156.html

收录于

MySQL

本文详细介绍了SQL中分组聚合与排名的功能，包括ROW_NUMBER()函数的使用方法，如何在不同数据库环境下（如Oracle、SQL Server和MySQL）实现分组、排序和排名，以及在实际场景中如何应用这些技巧解决具体问题。

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

ROW_NUMBER() OVER(
    [PARTITION BY column_1, column_2,…]
    [ORDER BY column_3,column_4,…]
)

Oracle和SQL server的关键字是over partition by

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

Oracle和sqlserver

最终效果：

Image

例子：

复制代码
– 建表

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

结果：
如果不要分组，就仅仅order by 的话
需求：给username加上唯一标示id

背景：需要一个纬度表，里面有仅仅username的唯一标示，因为hive中不存在自增id

select distinct 
    price,
    row_number() over (order by price)
from
    products
order by 
    price;

复制代码
price | row_number
---------±-----------
300.00 | 1
300.00 | 2
400.00 | 3
500.00 | 4
600.00 | 5
600.00 | 6
700.00 | 7
800.00 | 8
800.00 | 9
900.00 | 10
1100.00 | 11
复制代码
需求同上，如果需要去重的话（distinct）

with prices as (
    select distinct
        price
    from 
        products
)
select price,row_numer()over(order by price) from prices;

price | row_number
---------±-----------
300.00 | 1
400.00 | 2
500.00 | 3
600.00 | 4
700.00 | 5
800.00 | 6
900.00 | 7
1100.00 | 8
mysql因为不能使用这个关键字，所以配合其他关键字使用
预期效果

select deptid,salary
from employee a
where 2 > (
select count(1)
from employee b
where a.salary<b.salary and a.deptid=b.deptid
)
order by a.deptid,a.salary desc;

但是有弊端，如果最大值有多个，那么就会出现多个最大值，so，要动态的

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;

mysql还有其他写法，通过求出极值再进行关联

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)

标签

#sql #mysql