mysql语句记录——排名

成绩表 score
studen_id （学生id）
math （数学分数）
english （英语分数）
java （java分数）

以下sql查询出studen_id为3的同学 数学分超过了几个同学

SELECT
	s1.student_id,
	s1.math,
	(
		SELECT
			count(student_id)
		FROM
			score
		WHERE
			math < s1.math
	) AS Rank
FROM
	score s1
where student_id = 3
ORDER BY
	math DESC

运行结果如下：

将上面的sql语句稍微改变一下就可以求出以数学分的排名：

SELECT
	s1.student_id,
	s1.math,
	(
		SELECT
			count(student_id)
		FROM
			score
		WHERE
			math >= s1.math
	) AS Rank
FROM
	score s1
ORDER BY
	math DESC

运行结果如下：

将score表新增一个class(班级)字段，数据如下：

查询以班级分类java分数的排名，语句如下：

SELECT
 student_id,java,class,rank
FROM (
  SELECT
   *,
  IF (
   @p = class,
   CASE
     WHEN @s = java THEN
       @r
     WHEN @s != java THEN
       @r :=@r + 1
     END,
   @r := 1
  ) AS rank,
  @p := class,
  @s := java
 FROM
  score,
  (SELECT @p := NULL ,@s := NULL ,@r := 0) r
 ORDER BY class, java DESC
) s;

运行结果如下：

附上创建score表结构和测试数据的sql：

SET FOREIGN_KEY_CHECKS=0;
-- ----------------------------
-- Table structure for score
-- ----------------------------
DROP TABLE IF EXISTS `score`;
CREATE TABLE `score` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `student_id` int(11) DEFAULT NULL,
  `math` int(3) DEFAULT '0',
  `english` int(3) DEFAULT '0',
  `java` int(3) DEFAULT '0',
  `class` varchar(1) DEFAULT NULL COMMENT '班级',
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=10 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of score
-- ----------------------------
INSERT INTO `score` VALUES ('1', '1', '87', '56', '98', '1');
INSERT INTO `score` VALUES ('2', '2', '66', '88', '77', '3');
INSERT INTO `score` VALUES ('3', '3', '70', '76', '68', '2');
INSERT INTO `score` VALUES ('4', '4', '96', '86', '94', '1');
INSERT INTO `score` VALUES ('5', '5', '57', '90', '69', '3');
INSERT INTO `score` VALUES ('6', '6', '76', '79', '96', '2');
INSERT INTO `score` VALUES ('7', '7', '68', '95', '89', '2');
INSERT INTO `score` VALUES ('8', '8', '91', '94', '90', '3');
INSERT INTO `score` VALUES ('9', '9', '87', '77', '67', '1');