最近面试问到了，其实很简单，只是很久没有这样查询过了，一下子给整不会了。。。
数据库：
附上SQL：
SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for course-- ----------------------------DROP TABLE IF EXISTS `course`;CREATE TABLE `course` (`student_name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`subject` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,`grade` int(11) NULL DEFAULT NULL) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact;-- ------------------------------ Records of course-- ----------------------------INSERT INTO `course` VALUES ('法外狂徒-张三', '语文', 98);INSERT INTO `course` VALUES ('法外狂徒-张三', '数学', 79);INSERT INTO `course` VALUES ('电瓶终结者-周某人', '语文', 77);INSERT INTO `course` VALUES ('电瓶终结者-周某人', '数学', 75);INSERT INTO `course` VALUES ('坦克驾驶员-榜一先生', '语文', 85);INSERT INTO `course` VALUES ('坦克驾驶员-榜一先生', '数学', 88);INSERT INTO `course` VALUES ('坦克驾驶员-榜一先生', '英语', 89);SET FOREIGN_KEY_CHECKS = 1;
问题及答案（非最优，能跑起来就行）：
# 查询各学科的最高数SELECT subject, MAX(grade) AS '最高分' FROM course GROUP BY subject;# 查询出这个学科最高分的人SELECT t1.subject AS '学科', t1.student_name AS '学生', t2.最高分 FROM course t1, (SELECT subject, MAX(grade) AS '最高分' FROM course GROUP BY subject) t2WHERE t1.subject = t2.subject AND t1.grade = t2.最高分;# 需求：查询出所有课程都高于80的学生名字# 查询有低于80分的学生名字SELECT student_name FROM course WHERE grade < 80;# 查询有低于80分的学生名字，去除重复SELECT DISTINCT(student_name) FROM course WHERE grade < 80;# 查询出所有课程都高于80的学生名字SELECT DISTINCT(student_name) FROM course WHERE student_name NOT IN (SELECT DISTINCT(student_name) FROM course WHERE grade < 80);# 求学生的平均数SELECT AVG(grade) FROM course GROUP BY student_name;# 查询平均分高于80的学生名字SELECT student_name, AVG(grade) AS '平均分数' FROM course GROUP BY student_name HAVING AVG(grade) > 80;# 查询平均分高于80的学生名字，保留两位小数SELECT student_name, ROUND(AVG(grade), 2) AS '平均分数' FROM course GROUP BY student_name HAVING AVG(grade) > 80;
真是拉了裤里了，活该过不了。
