以前遇到的一道面试题----学校数据库操作

题目

1. 给学校设计一个数据库，库里存着每个学生在每个课程的成绩（均是百分制），可以回想下你大学的时候。
2. 写一个查询，找出所有平均分在前10%的学生，并且按照他们的成绩从高到低排名。注意：每个学生参加的课程的数量可能不同，平均分指的是学生成绩总分/参加课程的数量

答案

1. 数据库设计

SET FOREIGN_KEY_CHECKS=0;

-- ----------------------------
-- Table structure for schoolinfo
-- ----------------------------
DROP TABLE IF EXISTS `schoolinfo`;
CREATE TABLE `schoolinfo` (
  `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学校编号',
  `SchoolCode` varchar(32) DEFAULT NULL COMMENT '学校编码',
  `SchoolName` varchar(64) DEFAULT NULL COMMENT '学校名称',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for studentinfo
-- ----------------------------
DROP TABLE IF EXISTS `studentinfo`;
CREATE TABLE `studentinfo` (
  `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学生编号',
  `StudentCode` varchar(32) DEFAULT NULL COMMENT '学号',
  `StudentName` varchar(64) DEFAULT NULL COMMENT '学生名称',
  `SchoolId` int(11) DEFAULT '0' COMMENT '学校编号',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for studentscoreinfo
-- ----------------------------
DROP TABLE IF EXISTS `studentscoreinfo`;
CREATE TABLE `studentscoreinfo` (
  `SubjectId` int(11) NOT NULL DEFAULT '0' COMMENT '学科编号',
  `StudentId` int(11) NOT NULL DEFAULT '0' COMMENT '学生编号',
  `StudentScore` decimal(11,2) NOT NULL DEFAULT '0.00' COMMENT '分数',
  PRIMARY KEY (`SubjectId`,`StudentId`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Table structure for subjectinfo
-- ----------------------------
DROP TABLE IF EXISTS `subjectinfo`;
CREATE TABLE `subjectinfo` (
  `Id` int(11) NOT NULL AUTO_INCREMENT COMMENT '学科编号',
  `SubjectCode` varchar(32) DEFAULT NULL COMMENT '学科编码',
  `SubjectName` varchar(64) DEFAULT NULL COMMENT '学科名称',
  PRIMARY KEY (`Id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

2. 查询语句

SET @rank = 0 ,@rowtotal := NULL ,@rowtotal1 = NULL ,@sart := 0;

SELECT
	*
FROM
	(
		SELECT
			a.StudentId,
			a.AverageScore,
			CASE
		WHEN @rowtotal = a.AverageScore THEN
			@rank
		WHEN @rowtotal := a.AverageScore THEN
			@rank := @rank + 1 + @sart
		WHEN @rowtotal = 0 THEN
			@rank := @rank + 1 +@sart
		END AS rank_no,
		CASE
	WHEN @rowtotal1 = a.AverageScore THEN
		@sart := @sart + 1
	WHEN @rowtotal1 := a.AverageScore THEN
		@sart := 0
	WHEN @rowtotal1 = 0 THEN
		@sart := 0
	END AS sart
	FROM
		(
			SELECT
				StudentId,
				AVG(StudentScore) AverageScore
			FROM
				studentscoreinfo
			GROUP BY
				StudentId
			ORDER BY
				AverageScore DESC
		) a
	) base
WHERE
	base.rank_no <= (@rank * 0.1)

注

1. 数据库MySQL
2. MySQL因为没有PERCENT关键字 所以需要自己排序取值