mysql 使用rownum排序并获得排名

1.执行如下语句进行创建表并添加数据

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`date` varchar(255) DEFAULT NULL,
`score` int(11) DEFAULT NULL,
PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8mb4;

set @date = (select DATE_FORMAT(NOW(), '%Y%m%d') from dual);

INSERT INTO `user` (`date`, `score`) VALUES (@date, 90);
INSERT INTO `user` (`date`, `score`) VALUES (@date, 91);
INSERT INTO `user` (`date`, `score`) VALUES (@date, 92);
INSERT INTO `user` (`date`, `score`) VALUES (@date, 93);
INSERT INTO `user` (`date`, `score`) VALUES (@date, 94);
INSERT INTO `user` (`date`, `score`) VALUES (@date, 95);
INSERT INTO `user` (`date`, `score`) VALUES (@date, 96);
INSERT INTO `user` (`date`, `score`) VALUES (@date, 97);

select * from user;

2.排序

select @rownum:=@rownum+1 AS rownum,id,date
from
user ,(SELECT @rownum:=0) r
ORDER BY score;