如何用SQL查询单个学生的成绩的排名

新建表，并添加数据如下： /* Navicat MySQL Data Transfer Source Server : localhost Source Server Version : 50621 Source Host : localhost:3306 Source Database : test Target Server Type : MYSQL Target Server Version : 50621 File Encoding : 65001 Date: 2016-04-23 11:46:02 */ SET FOREIGN_KEY_CHECKS=0; -- ---------------------------- -- Table structure for `score` -- ---------------------------- DROP TABLE IF EXISTS `score`; CREATE TABLE `score` ( `id` int(10) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(255) CHARACTER SET utf8 DEFAULT NULL, `score` double DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1; -- ---------------------------- -- Records of score -- ---------------------------- INSERT INTO `score` VALUES ('1', '少华90', '90'); INSERT INTO `score` VALUES ('2', 'shaohua80', '80'); INSERT INTO `score` VALUES ('3', 'monlone98', '98'); INSERT INTO `score` VALUES ('4', 'lee99', '98'); /*方法一*/ SELECT B.NAME, B.SCORE, (SELECT COUNT(id) + 1 FROM score A WHERE A.SCORE > B.SCORE) PM FROM score B ORDER BY PM ; /*方法二，其实也是方法一，只是变成了变量*/ SELECT id, score, rank FROM (SELECT tmp.id, tmp.score, @rank := @rank + 1 AS rank FROM (SELECT id, score FROM score ORDER BY score desc ) tmp, (SELECT @rank := 0) s ) RESULT;

如何用SQL查询单个学生的成绩的排名

微信号：腾讯云折扣返现

技术讨论QQ群： 863102326

作者: catherine

搜索

标签