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

最新推荐文章于 2024-10-16 14:55:49 发布

chenzhh25

最新推荐文章于 2024-10-16 14:55:49 发布

阅读量1.9k

点赞数 2

分类专栏： Mysql 文章标签： mysql sql row_number

本文链接：https://blog.csdn.net/chenzhh25/article/details/103728053

版权

Mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

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;

执行结果：