Mysql实现row_number(partition by)窗口函数

最新推荐文章于 2025-10-28 17:40:45 发布

原创最新推荐文章于 2025-10-28 17:40:45 发布 · 2k 阅读

4 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #sql #row_number

Mysql 专栏收录该内容

3 篇文章

订阅专栏

本文详细介绍了如何在MySQL中使用变量方法实现row_number()功能，包括全局行号和按用户分组的分区行号，通过具体SQL语句展示了不同场景下行号的生成过程。

1、实现row_number()

select user_nm
  , login_time
  , @row_num := @row_num + 1 as rank_num
from (
    select 'zhang' as user_nm,	'2019-01-03 12:30:20' as login_time
    union all
    select 'zhang' as user_nm,	'2019-01-02 12:30:20' as login_time
    union all
    select 'zhang' as user_nm,	'2019-01-02 14:30:20' as login_time
    union all
    select 'li' as user_nm,	'2019-01-04 12:30:20' as login_time
    union all
    select 'li' as user_nm,	'2019-01-05 12:30:20' as login_time
    union all
    select 'li' as user_nm,	'2019-01-06 12:30:20' as login_time
    union all
    select 'li' as user_id,	'2019-01-07 12:30:20' as login_time
    union all
    select 'wang' as user_nm,	'2019-01-07 12:30:20' as login_time
    union all
    select 'wang' as user_nm,	'2019-01-04 14:30:20' as login_time
    union all
    select 'wang' as user_nm,	'2019-01-05 12:30:20' as login_time
    union all
    select 'wang' as user_nm,	'2019-01-06 12:30:20' as login_time
    order by user_nm, login_time
) a, (select @row_num := 0) as b;

执行结果：

2、实现row_number(partition by)

select user_nm
  , login_time
  , @row_num := case when @group_nm = user_nm then @row_num := @row_num + 1 else 1 end as rank_num
  , @group_nm := user_nm as user_nm2
from (
    select 'zhang' as user_nm,	'2019-01-03 12:30:20' as login_time
    union all
    select 'zhang' as user_nm,	'2019-01-02 12:30:20' as login_time
    union all
    select 'zhang' as user_nm,	'2019-01-02 14:30:20' as login_time
    union all
    select 'li' as user_nm,	'2019-01-04 12:30:20' as login_time
    union all
    select 'li' as user_nm,	'2019-01-05 12:30:20' as login_time
    union all
    select 'li' as user_nm,	'2019-01-06 12:30:20' as login_time
    union all
    select 'li' as user_id,	'2019-01-07 12:30:20' as login_time
    union all
    select 'wang' as user_nm,	'2019-01-07 12:30:20' as login_time
    union all
    select 'wang' as user_nm,	'2019-01-04 14:30:20' as login_time
    union all
    select 'wang' as user_nm,	'2019-01-05 12:30:20' as login_time
    union all
    select 'wang' as user_nm,	'2019-01-06 12:30:20' as login_time
    order by user_nm, login_time
) a, (select @row_num := 0, @group_nm := '') as b;

执行结果：