如何优化该例中NOT IN的SQL

Question

现有2张表

course
    course_id  int   课程ID
    score      float 课程得分，满分100,60及格
    student_id int   学生ID

student
    student_id int      学生ID
    name       string   学生姓名

一个学生可能有多个课程得分（即student一对多于course）

现定义一个学生及格的条件为所有课程中只要有一门课程及格则算及格，只有所有课程不及格的情况下才算不及格

求所有及格的学生和所有不及格的学生。

及格很简单

SELECT
    s.student_id,
    s.name
FROM
    student s
INNER JOIN
    course c
ON
    s.student_id = c.student_id
WHERE
    c.score >= 60
GROUP BY
    student_id

但是不及格却不能直接以 WHERE c.score < 60 GROUP BY student_id得出结果

问除了使用NOT IN及格的学生的结果 还有什么比较好的方法？

注意：此处只是模拟真实场景，实际上的条件比score >= 60要多，所以不应该特别针对此处使用MIN来做...（如果回复有我会再说明的）

Answer 1

我有个思路，
1、在课程表中查出分数小于60对应的学生id
2、再去课程表查该学生id下是否有课程大于60
3、如果不存在，根据id查出学生信息

这里可以用三种方式来实现：
1、逐句逐句来查。优点：思路清晰，不容易出错。
2、用In/Not In来做子查询
3、用mysql中的where exists/not exists来优化IN/NOT IN

我试着用方法3来写一下:

select s.student_id,s.name
from student s
inner join course c1
on s.student_id = c1.student_id
where c1.score < 60 and not exists(
    select course_id
    from course c2
    where c2.student_id = s.student_id
    and c2.score >= 60
)

(我本地跑了一下，没有问题)
对于exists于in的效率，可以去自己搜搜看。

这类问题可以划分到：除了满足一个条件，还需满足另外的条件这种情景上。以后遇到这种问题，可以直接往里套。

ps:
1、student表里的name为什么是int类型。。。
2、我最先以为你写的及格sql是错的，跑了下竟然可以。
涨知识：mysql中聚合函数可以查其他字段，而在oracle中不可以。

Answer 2

没必要非要用 sql 一次查询出来吧.
先拿到一共又多少门课程,比如说一共5门.(配置和数据库查询都行)

SELECT
    s.student_id,
    s.name,
    count(1) as c
FROM
    student s
INNER JOIN
    course c
ON
    s.student_id = c.student_id
WHERE
    c.score < 60
GROUP BY
    student_id 
having count(1) = 5;

如果你的程序在录入成绩时,不会因为缺考就不录入,即用户缺考,那么却考科目自动录入成绩为0 的话,那就还有一种方式来处理

SELECT
    s.student_id,
    s.name,
    sum(if(c.score>=60,1,0)) as pass,
    sum(if(c.score>=60,0,1)) as fail,
    count(1) as total;
FROM
    student s
INNER JOIN
    course c
ON
    s.student_id = c.student_id 
GROUP BY
    student_id
Having sum(if(c.score>=60,0,1)) = count(1);

我比较推荐先拿到一共又多少门课程,另外,having 是可以用子查询的,所以第一个方案中 having 还是可以用子查询来代替的.

Answer 3

1. group by student_id
2. max(score) > 60 及格的学生（说明有一门大于60分）
3. max(score) < 60 不及格的学生（说明所有课程都小于60分）