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;