最近面试问到了,其实很简单,只是很久没有这样查询过了,一下子给整不会了。。。
数据库:
附上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;
真是拉了裤里了,活该过不了。