mysql中类似rownumber函数_在MySql中实现类似row_number，dense_rank，rank函数的排序方法...

MySQL中没有Rank排名函数，当需要查询排名时，只能使用MySQL数据库中的基本查询语句来查询普通排名。要在mysql中声明一个变量，必须在变量名之前使用@符号。FROM子句中的(@curRank := 0)部分允许我们进行变量初始化，而不需要单独的SET命令。当然，也可以使用SET

CREATE TABLE `players` (

`pid` int(2) NOT NULL AUTO_INCREMENT,

`name` varchar(50) NOT NULL,

`age` int(2) NOT NULL,

PRIMARY KEY (`pid`),

UNIQUE KEY `name` (`name`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `players` (`pid`, `name`, `age`) VALUES

(1, 'Samual', 25),

(2, 'Vino', 20),

(3, 'John', 20),

(4, 'Andy', 22),

(5, 'Brian', 21),

(6, 'Dew', 24),

(7, 'Kris', 25),

(8, 'William', 26),

(9, 'George', 23),

(10, 'Peter', 19),

(11, 'Tom', 20),

(12, 'Andre', 20);

1、实现Rank普通排名函数，即row_number()：

SELECT pid, name, age, @curRank := @curRank + 1 AS rank

FROM players p, (

SELECT @curRank := 0

) q

ORDER BY age

SET @curRank := 0;

SELECT pid, name, age, @curRank := @curRank + 1 AS rank

FROM players

ORDER BY age

PID

NAME

AGE

RANK

10

Peter

19

1

12

Andre

20

2

2

Vino

20

3

3

John

20

4

11

Tom

20

5

5

Brian

21

6

4

Andy

22

7

9

George

23

8

6

Dew

24

9

7

Kris

25

10

1

Samual

25

11

8

William

26

12

2、实现Rank普通并列排名函数，即dense_rank():

SELECT pid, name, age,

CASE

WHEN @prevRank = age THEN @curRank

WHEN @prevRank := age THEN @curRank := @curRank + 1

END AS rank

FROM players p,

(SELECT @curRank :=0, @prevRank := NULL) r

ORDER BY age

PID

NAME

AGE

RANK

10

Peter

19

1

12

Andre

20

2

2

Vino

20

2

3

John

20

2

11

Tom

20

2

5

Brian

21

3

4

Andy

22

4

9

George

23

5

6

Dew

24

6

7

Kris

25

7

1

Samual

25

7

8

William

26

8

3,实现Rank高级并列排名函数,即rank()函数：

SELECT pid, name, age, rank

FROM

(SELECT pid, name, age,

@curRank := IF(@prevRank = age, @curRank, @incRank) AS rank,

@incRank := @incRank + 1,

@prevRank := age

FROM players p, (

SELECT @curRank :=0, @prevRank := NULL, @incRank := 1

) r

ORDER BY age

) s

PID

NAME

AGE

RANK

10

Peter

19

1

12

Andre

20

2

2

Vino

20

2

3

John

20

2

11

Tom

20

2

5

Brian

21

6

4

Andy

22

7

9

George

23

8

6

Dew

24

9

7

Kris

25

10

1

Samual

25

10

8

William

26

12

来源简书：https://www.jianshu.com/p/bb1b72a1623e