用一条SQL语句查询出每门课都大于80分的学生姓名

题目预览

最近面试遇到的，回来自己敲了一下，报错了，所以记录并分享一下。这里我没有考虑什么效率问题，只考虑能不能查出来，欢迎各位交流指教。

表：students

+------+-------+--------+
| name | calss |  score |
+------+-------+--------+
| 张三  | 语文  |    12   |
| 张三  | 英语  |    80   |
| 张三  | 数学  |    12   |
| 李四  | 语文  |    81   |
| 李四  | 数学  |    86   |
| 李四  | 英语  |    86   |
| 王五  | 语文  |    12   |
| 王五  | 数学  |    90   |
| 赵六  | 语文  |    90   |
| 田七  | 语文  |    90   |
+------+------+----------+

结果：

+------+-------+--------+
| name | calss |  score |
+------+-------+--------+
| 李四  | 语文  |    81   |
| 李四  | 数学  |    86   |
| 李四  | 英语  |    86   |
| 王五  | 语文  |    89   |
| 王五  | 数学  |    90   |
| 赵六  | 语文  |    90   |
| 田七  | 语文  |    90   |
+------+------+---------+

思路1：排除法

参考资料：https://blog.csdn.net/feinifi/article/details/80279897

链接更详细，可以直接去看他的。

大概思路：按name进行分组，然后检查组内分数的最小值是否 >= 80 ,如果组内的分数都>=80 ,则该学生符合条件，然后按name查询students表输出。

首先：先查出符合条件的学生的name

select t.name from student t group by t.name having min(t.score)>=80; 

然后：以这里查出的name为条件查出对应学生的全部信息

SELECT
    * 
FROM
    student_grade t1 
WHERE
    t1.NAME IN ( SELECT t2.NAME NAME FROM student_grade t2 GROUP BY t2.NAME HAVING min( t2.score )>= 80 );

自我总结一下注意点（非常小白的注意点）：

1. where后不可以使用聚合函数，但是having可以使用聚合函数

2. having内的属性使用:该属性要在前面的select中存在或者直接使用聚合函数，否则属性不存在。如下错误：

SELECT
    t.name,
    count(*) c2 
FROM
    student_grade t 
GROUP BY
    t.name
    having
--     't.score>=80; -- 此时分组后的查询结果没有 t.score这个属性，所以报错
    min(t.score)>=80;

---

思路2：对比法

上面是参考的，我当时给的思路是另外一种：按学生进行分组，一次不带 score>=80，一次带score>=80，如果两次分组后对应学生的记录数相同则输出该学生全部信息。

第一步：先查出符合条件的学生的name

SELECT
    t1.NAME 
FROM
    ( SELECT s1.NAME, count(*) c1 FROM student_grade_copy1 s1 WHERE s1.score >= 80 GROUP BY s1.NAME ) t1,
    (    SELECT s2.NAME, count(*) c2 FROM student_grade_copy1 s2 GROUP BY s2.NAME ) t2 
WHERE
    t1.NAME = t2.NAME 
    AND t1.c1 = t2.c2;

第二步：以这里查出的name为条件查出对应学生的全部信息

SELECT
* 
from 
student_grade_copy1 s
WHERE 
s.name in (
SELECT
    t1.NAME 
FROM
    ( SELECT s1.NAME, count(*) c1 FROM student_grade_copy1 s1 WHERE s1.score >= 80 GROUP BY s1.NAME ) t1,
    (    SELECT s2.NAME, count(*) c2 FROM student_grade_copy1 s2 GROUP BY s2.NAME ) t2 
WHERE
    t1.NAME = t2.NAME 
    AND t1.c1 = t2.c2
);

---

额外问题

上面看似已经按要求查出学生信息了？但是有的情况没有考虑到。如score可能为null时，一个学生可能重修多次，只要有一次过80就可以。

我的思路是，开始就设置score不可以为null,然后按name+class分组后求出每个学生的每一门课的最大分数，将这个表作为上面查询条件的总表。

新的总表如下：

SELECT
    t1.NAME,
    t1.class,
    count(*) c1 ,
    max( t1.score ) max_score 
FROM
    student_grade t1 
GROUP BY
    t1.NAME,
    t1.class;

最后还是要再次注意

1. where后不可以使用聚合函数，但是having可以使用聚合函数；

2. having内的属性使用:该属性要在前面的select中存在或者直接使用聚合函数，否则属性不存在；

3. 函数运算后的结果和新查出的表一定注意是否要重命名！

---

小白的笔记，欢迎指教