博客

最近面试问到了，其实很简单，只是很久没有这样查询过了，一下子给整不会了。。。

数据库：

附上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) t2
WHERE 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;

真是拉了裤里了，活该过不了。