SQL语句查询：在同一张表查询学生大于60分的学生、总成绩、平均分 多门科目

SQL语句查询：在同一张表查询学生大于60分的人数、总成绩、平均分 多门科目

表结构：
INSERT INTO student VALUES (6, ‘语文’, 99, ‘张三’);
INSERT INTO student VALUES (7, ‘数学’, 88, ‘张三’);
INSERT INTO student VALUES (8, ‘英语’, 77, ‘张三’);
INSERT INTO student VALUES (9, ‘语文’, 59, ‘李四’);
INSERT INTO student VALUES (10, ‘数学’, 59, ‘李四’);
INSERT INTO student VALUES (11, ‘英语’, 99, ‘李四’);
INSERT INTO student VALUES (12, ‘语文’, 60, ‘王五’);
INSERT INTO student VALUES (13, ‘数学’, 60, ‘王五’);
INSERT INTO student VALUES (14, ‘英语’, 60, ‘王五’);
INSERT INTO student VALUES (15, ‘语文’, 60, ‘小明’);
INSERT INTO student VALUES (16, ‘数学’, 59, ‘小明’);
INSERT INTO student VALUES (17, ‘英语’, 100, ‘小明’);
INSERT INTO student VALUES (18, ‘语文’, 66, ‘小红’);
INSERT INTO student VALUES (19, ‘数学’, 87, ‘小红’);
INSERT INTO student VALUES (20, ‘英语’, 65, ‘小红’);
INSERT INTO student VALUES (21, ‘语文’, 11, ‘小李’);
INSERT INTO student VALUES (22, ‘数学’, 33, ‘小李’);
INSERT INTO student VALUES (23, ‘英语’, 22, ‘小李’);
INSERT INTO student VALUES (24, ‘语文’, 44, ‘小刚’);
INSERT INTO student VALUES (25, ‘数学’, 88, ‘小刚’);
INSERT INTO student VALUES (26, ‘英语’, 89, ‘小刚’);

插入数据库后

图片:

着手编写sql

代码.

SELECT
	student.`Name` AS 姓名,
	count(1) AS 科目数,
sum(number)/3 as 平均分,
sum(number) as 总分
FROM
	student
WHERE
	number > 60
GROUP BY
	NAME
HAVING
	count(*) = 3

图片:结果输出

最简单好理解的方式~