mysql使用rank()函数排序

一、创建表

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);

二、mysql使用rank()函数排序

SELECT NAME,age,RANK() OVER(ORDER BY age ASC) AS 'rank' FROM players; 

三、使用row_number()函数排序

SELECT NAME,age,ROW_NUMBER() OVER(ORDER BY age ASC) AS 'rank' FROM players; 

四、使用mysql基本语法做排序

SELECT temp.pid,temp.name,temp.age,
@j:=@j+1 AS number,
@k:=(CASE WHEN @pre_age=temp.age THEN @k ELSE @j END) AS 'rank',
@h:=(CASE WHEN @pre_age=temp.age THEN @h ELSE (CASE WHEN @h=0 THEN @j ELSE @h+1 END) END) AS 'rank2(本行可删除)',
@pre_age:=temp.age AS pre_age
FROM (SELECT * FROM players ORDER BY age ASC) temp,
(SELECT @k:=0,@j:=0, @h:=0, @pre_age:=0) initNum

出现3种样式的排序