有这么一道面试题,说有学生表(student),其中字段包括id(主键)、name(姓名)、score(分数),按照成绩进行排名,其中成绩一样的,名次要并列。
首先说一下,排序有两种,一种是最直白好理解的并列排序,另一种常常应用在实际工作开发场景中。直接上图
第一种:
第二种:
也许有人会很奇怪,第二种乍一看,感觉不太合理。但往往在实际开发中,第二种最常用。
第一种很好理解,不多解释了,第二种举例说明一下就明白了。
例如:某大学计算机专业研究生计划招生30人,招生按排名择优录取。总共60人报考,其中不巧的是,前30名并列第一。那么只录取30个并列第一的,还是30并列第一的+ 2 到 30名?很显然,只录取30个并列第一的。这就是第二种的意义(感觉栗子举得一般,凑合看吧哈哈)。
下面说一下,如何查询。
表结结构及测试数据如下(也可以自己随便创建一个雷同的):
SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;
-- ----------------------------
-- Table structure for student
-- ----------------------------
DROP TABLE IF EXISTS `student`;
CREATE TABLE `student` (
`id` varchar(10) NOT NULL,
`name` varchar(32) DEFAULT NULL,
`score` int(4) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- ----------------------------
-- Records of student
-- ----------------------------
BEGIN;
INSERT INTO `student` VALUES ('1001', '李雷', 100);
INSERT INTO `student` VALUES ('1002', '韩梅梅', 62);
INSERT INTO `student` VALUES ('1003', '王涛', 78);
INSERT INTO `student` VALUES ('1004', '周深', 98);
INSERT INTO `student` VALUES ('1005', '王广志', 78);
INSERT INTO `student` VALUES ('1006', '刘长春', 58);
INSERT INTO `student` VALUES ('1007', '戴玉强', 62);
INSERT INTO `student` VALUES ('1008', '谢海龙', 100);
COMMIT;
SET FOREIGN_KEY_CHECKS = 1;
执行完,大概长这样:
第一种排名:
SELECT
s.*,
( SELECT COUNT( DISTINCT a.score ) FROM student a WHERE a.score >= s.score ) AS rank
FROM
student s
ORDER BY
s.score DESC;
第二种排名:
SELECT
s.*,
( SELECT COUNT( a.id) + 1 FROM student a WHERE a.score > s.score ) AS rank
FROM
student s
ORDER BY
s.score DESC;
本人比较讨厌使用数据库自带函数或者rownum之类的东西,所以一般能不用就不用。本文纯属记录一下。