mysql实现oracle中rank() over,dense_rank() over,row_number() over函数

mysql实现oracle中rank() over,dense_rank() over,row_number() over函数

row_number() over:返回的是排名，
rank() over:返回的相关等级会跳跃
dense_rank():返回的相关等级不会跳跃

mysql要达到oracle的查询效果

select id, name, rank() over(order by score desc) as r,
	DENSE_RANK() OVER(order by score desc) as dense_r,
	row_number() OVER(order by score desc) as row_r
from students;

1：建表

CREATE TABLE students(
	id INT(4)  AUTO_INCREMENT PRIMARY KEY,
	NAME VARCHAR(50) NOT NULL, 
	score INT(4) NOT NULL
	);

2：插入数据

INSERT INTO students(NAME,score) VALUES('curry', 100),
	('klay', 99),
	('KD', 100), 
	('green', 90), 
	('James', 99), 
	('AD', 96);

3：mysql实现oracle中的row_number() over
oracle中

SELECT  NAME,score, row_number() OVER(ORDER BY score DESC) AS row_r FROM students;

mysql中

SELECT NAME,score,@rk:=@rk+1 row_r FROM(SELECT NAME,score FROM students ORDER BY score DESC)a,(SELECT @rk:=0)b;

4：mysql实现oracle中的dense_rank() over
oracle中

SELECT  NAME,score, DENSE_RANK() OVER(ORDER BY score DESC) AS dense_r FROM students;

mysql中

SELECT NAME,@rk:=IF(@score=score,@rk,@rk+1) dense_r,@score:=score score FROM (SELECT NAME,score FROM students ORDER BY score DESC)a,(SELECT @rk:=0,@score:=NULL)b;

5：mysql实现oracle中的rank() over
oracle中

SELECT  NAME,score, rank() over(ORDER BY score DESC) AS r FROM students;

mysql中

SELECT NAME,@cur:=IF( @score = score, @cur,@rk ) r,@rk:=@rk + 1,@score:=score score FROM (SELECT NAME,score FROM students ORDER BY score DESC)a,(SELECT @rk:=1,@score:=NULL,@cur:=0)b; 

总结

ROW_NUMBER：

Row_number函数返回一个唯一的值，当碰到相同数据时，排名按照记录集中记录的顺序依次递增。

DENSE_RANK：

Dense_rank函数返回一个唯一的值，除非当碰到相同数据时，此时所有相同数据的排名都是一样的。

RANK over：

Rank函数返回一个唯一的值，除非遇到相同的数据时，此时所有相同数据的排名是一样的，同时会在最后一条相同记录和下一条不同记录的排名之间空出排名。

----拓展
oracle

使用rank() over的时候，空值是最大的，如果排序字段为null,可能造成null字段排在最前面，影响排序结果。可以这样：rank()
over(partition by course order by score desc nulls last)来规避这个问题。

mysql

order by isnull(score) asc,score desc
isnull满足返回1，否则返回0，原则首先把null的排序到最后，再根据字段排序