今天在qq群发现群友们在讨论一个sql查询问题，咋一看，好像不难，于是乎大家七嘴八舌说该如何如何查云云。有的说三次left join 课程和成绩表，然后再加条件；也有的直接列出他认为对的sql语句，就如同下面这位哥们列的：
select name from sys_student where id not in(select stu_id from sys_mark where score <=80 )
忘了说题目了，题目给的是三张表，如下图
题目
刚才的群友给的咋一看是对的啊，结果能查询出王五，立马大批人站队，说大兄弟的是对的。这位大兄弟的想法没错，与其找全部大于80的，不如找有一个小于80的，然后 结果not in 这里面不就是最终结果了吗？仔细想想没问题啊。可是回头又想想怎么没用到题目中的第二章表 sys_subject呢？那给这张表的意义在哪儿，是不？所以我深入的想了下这个结果，发现，如果张三的数学成绩如果不是75而是85，那么这个语句查询出来的不就是张三加王五吗？
75换成85？
这个结果对吗？题目要求是每门课程都大于80，张三的英语根本没有成绩，那么其实张三是不符合条件的。果然，广大群友中有机智的兄弟发现了这个问题，指出来啦，然后群里兄弟们说需要再加条件云云，最后也没给出个准确的结果，仔细看了看觉得不爽，于是想着自己写个正确的sql来吧。
先写select xx.name from (?) ，但括号里填什么呢？又怎么保证课程表都考了呢？一步一步来，要保证课程表中课程都考了，起码要知道有多少课程（你不能说3，虽然你能看出来哈，因为题目没给3），这时便想到了sql的count函数，对吧，直接select count(*) from sys_subject 这不就出来了嘛。那么sql语句就可以写成这样 select xx.name from (?) where 成绩表每个学生考试课程数量=select count(*) from sys_subject 是不是？那么现在就是怎么知道每个学生考试的课程数量是多少呢？直接count是不行的，直接count sub_id 得出的应该是全部的，比如这边应该是7，这时可能就需要分组了，依据stu_id来分组，这样count的结果就是每个结果了吧?试试，果然没错，哈哈，这样基本想要的都有了，只要学生表join一下成绩表就行。
查询成绩表每门课程的数量
最终写的sql如下：
select t.name from ( select stu.name,count(stu.name) As ct ,m.score from sys_student stu join sys_mark m on stu.id = m.stu_id where m.score>80 group by stu.name) t where t.ct = (select count(*) from sys_subject)
最终sql
到此算是问题解决了，但想来应该还有其他方法，欢迎大家私信讨论！
网友评论