【SQL练习】学生成绩查询

题目1：查询出两门及两门以上不及格者的平均成绩(注意是所有科目的平均成绩)

-- 创建练习表
CREATE TABLE "tb_stu" (
  "id" INT(3) DEFAULT NULL,
  "name" VARCHAR(15) DEFAULT NULL,
  "subject" VARCHAR(15) DEFAULT NULL,
  "score" INT(3) DEFAULT NULL
) 
-- 插入7条数据
INSERT INTO tb_stu VALUES(1,'张三','数学',90);
INSERT INTO tb_stu VALUES(2,'张三','语文',50);
INSERT INTO tb_stu VALUES(3,'张三','地理',40);
INSERT INTO tb_stu VALUES(4,'李四','政治',45);
INSERT INTO tb_stu VALUES(5,'李四','语文',55);
INSERT INTO tb_stu VALUES(6,'王五','政治',30);
INSERT INTO tb_stu VALUES(7,'王五','语文',70);

-- 注意区分sum() 和 count()
SELECT NAME, SUM(score<60) FROM tb_stu GROUP BY NAME  -- 查询结果 2 条
SELECT NAME, COUNT(score<60) FROM tb_stu GROUP BY NAME -- 查询结果 3 条

注意：

count(a),每遇到一行，就数一个a，无论a是什么，跟条件无关！

score<60 返回 1 或 0；所以可以用sum(score<60)来计算不及格的科目数！

 

-- 实现方法1： 子查询里面是 先查询有2门功课不及格的用户 -- 外层查询是计算这些用户的平均成绩
SELECT t1.name ,AVG(t2.score)
FROM (SELECT NAME FROM tb_stu WHERE score<60 GROUP BY NAME HAVING COUNT(*)>=2)t1,
tb_stu t2
WHERE t1.name=t2.name
GROUP BY t2.name

-- 实现方法2：
SELECT NAME,AVG(score),SUM(score<60) AS gk FROM tb_stu
GROUP BY NAME HAVING gk>=2;