Mysql row_number() | rank() 实现

准备数据

id str remark
1	A	hadoop
2	B	hadoop
3	A	hadoop
4	C	hadoop
5	A	hive
6	C	hive
7	B	hive

row_number() 实现

select 
*
from 
(
SELECT
    @num := IF(@str = str, @num + 1, 1) num,
    id,
    @str := str str,
		remark
FROM
    tem t , (SELECT @str := '', @num := 0) t1
ORDER BY
    str, id
)t

输出结果

1	1	A	hadoop
2	3	A	hadoop
3	5	A	hive
1	2	B	hadoop
2	7	B	hive
1	4	C	hadoop
2	6	C	hive

rank() 实现

select 
    str,
    rn 
from 
(
select 
    str ,
    @num := if(@str = str ,@num , @rk) as rn ,
    @rk := @rk + 1 ,
    @str := str 
from tem t1 ,(select @str := '' ,@num :=0 , @rk := 1) t2 
order by str
) t1 

输出结果

A	1
A	1
A	1
B	4
B	4
C	6
C	6

说明：Mysql8之前的没有 row_number() 、rank() 等开窗函数，需要单独设置一个变量来记录变化，Mysql8之后可以使用 row_number() 、rank() 等开窗函数