V2EX = way to explore
V2EX 是一个关于分享和探索的地方
现在注册
已注册用户请  登录
V2EX 提问指南
sockball07
V2EX  ›  问与答

请教一个 SQL 问题

  •  
  •   sockball07 · 2020-09-19 17:04:45 +08:00 · 1139 次点击
    这是一个创建于 1286 天前的主题,其中的信息可能已经有所发展或是发生改变。

    现有 2 张表

    course
        course_id  int   课程 ID
        score      float 课程得分,满分 100,60 及格
        student_id int   学生 ID
    
    student
        student_id int   学生 ID
        name       int   学生姓名
    

    一个学生可能有多个课程得分(即 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来做...(如果回复有我会再说明的)

    第 1 条附言  ·  2020-10-06 10:54:32 +08:00
    抱歉,回复不及时

    其他回答比较针对这道题了,实际条件(及格)要比 score >= 6 0 要多(多个条件),已使用 exists
    5 条回复    2020-09-20 11:12:21 +08:00
    wisunny
        1
    wisunny  
       2020-09-19 17:26:22 +08:00 via Android
    exists
    TimePPT
        2
    TimePPT  
       2020-09-19 18:25:35 +08:00 via Android   ❤️ 2
    想了下,试试用 case when then else 把及格不及格二值化(及格赋值 1,不及格 0 ),然后 group by 求和,和大于 0 的及格,等于 0 的不及格
    chotow
        3
    chotow  
       2020-09-19 20:14:50 +08:00
    SELECT s.name, c.score, c.course_id
    FROM course AS c
    JOIN student AS s ON s.student_id = c.student_id
    LEFT JOIN course AS c_tmp ON c_tmp.student_id = c.student_id AND c.score < c_tmp.score
    WHERE c_tmp.course_id IS NULL
    AND c.score < 60

    https://dev.mysql.com/doc/refman/8.0/en/example-maximum-column-group-row.html
    wspsxing
        4
    wspsxing  
       2020-09-20 07:53:15 +08:00
    select student_id, name, pass, cc from (select student_id, sum(score >= 60) as pass, count(score) as cc from course group by student_id)a join student as s on a.student_id = s.id where pass <= 0
    awen233333
        5
    awen233333  
       2020-09-20 11:12:21 +08:00 via Android
    having max(c.score)<60
    关于   ·   帮助文档   ·   博客   ·   API   ·   FAQ   ·   我们的愿景   ·   实用小工具   ·   5291 人在线   最高记录 6543   ·     Select Language
    创意工作者们的社区
    World is powered by solitude
    VERSION: 3.9.8.5 · 35ms · UTC 09:32 · PVG 17:32 · LAX 02:32 · JFK 05:32
    Developed with CodeLauncher
    ♥ Do have faith in what you're doing.