mysql 排序 排名字段

表结构

CREATE TABLE `Students` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `Roll_No` varchar(20) NOT NULL,
  `LastName` varchar(20) NOT NULL,
  `Address` varchar(120) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8mb4

insert 测试数据

INSERT INTO `test`.`Students`(`id`, `Roll_No`, `LastName`, `Address`) VALUES (1, '12', 'ze', '无可奈何花落去dsf');
INSERT INTO `test`.`Students`(`id`, `Roll_No`, `LastName`, `Address`) VALUES (2, '30', 'grade', '花样百出435正是旧]');
INSERT INTO `test`.`Students`(`id`, `Roll_No`, `LastName`, `Address`) VALUES (3, '15', 'zzedbn', '3234234');

查询方法一

SELECT   

(SELECT COUNT(1) FROM Students WHERE s.ROLL_NO <= ROLL_NO) AS Rank,   

s.LastName, s.Roll_no AS Roll   

FROM Students s   

ORDER BY s.Roll_no desc  

查询方法二

SELECT @counter:=@counter+1 AS Rank,LastName,Roll_no as Roll FROM Students,(SELECT @counter:=0) AS t   
ORDER BY Roll_no ASC;  

查询结果

id Roll_No LastName Address
1 12 gray 无可奈何花落去dsf
2 30 great 花样百出435正是旧
3 15 Jame 3234234

发表评论

电子邮件地址不会被公开。 必填项已用*标注