blocks|key|4005079|text|不确定是否可以仅使用INNER+JOIN、UNION和CASE+->，但我找到了一个使用outer+join的解决方案(仍然非常简单)。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4005080|让我们试着分两步来解决这个问题。|4005081|1)让我们看看哪些学生没有完成所有的课程|4005082|select+s.code
++from++programs+p+inner+join+students+s+on+(p.degree+=+s.degree)
+++++++++++++++++++left+outer+join+exams+e+on+(e.course+=+p.course+and+e.student+=+s.code)
++where+e.course+is+null|code-block|syntax|javascript|4005083|我们加入学生的程序，然后离开外部加入考试，只过滤那些没有考试行匹配的行(这意味着学生还没有参加考试)。|4005084|2)我们得到这个查询没有返回的所有学生(意味着他们已经完成了所有必修课)|4005085|select
code,+name
from+student+where+code+not+in+(

++select+s.code
++from++programs+p+inner+join+students+s+on+(p.degree+=+s.degree)
+++++++++++++++++++left+outer+join+exams+e+on+(e.course+=+p.course+and+e.student+=+s.code)
++where+e.course+is+null

)|4005086|entityMap^0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|S|8|@]|9|@]|A|$]]|$1|B|3|C|5|6|7|T|8|@]|9|@]|A|$]]|$1|D|3|E|5|6|7|U|8|@]|9|@]|A|$]]|$1|F|3|G|5|H|7|V|8|@]|9|@]|A|$I|J]]|$1|K|3|L|5|6|7|W|8|@]|9|@]|A|$]]|$1|M|3|N|5|6|7|X|8|@]|9|@]|A|$]]|$1|O|3|P|5|H|7|Y|8|@]|9|@]|A|$I|J]]|$1|Q|3|-4|5|6|7|Z|8|@]|9|@]|A|$]]]|R|$]]

Not sure if this is possible with just INNER JOIN, UNION, and CASE -> but I found a solution with an outer join (still quite simple).

Lets try to solve this in two steps.

1) Lets see which students have NOT finished all their classes

<pre><code>select s.code
 from programs p inner join students s on (p.degree = s.degree)
 left outer join exams e on (e.course = p.course and e.student = s.code)
 where e.course is null
</code></pre>

We join programs with students and then left outer join the exams and filter only those rows where no exam row could be matched (which means the student has not taken the exam yet)

2) We get all students that are NOT returned by this query (meaning they have finished all required courses)

<pre><code>select
code, name
from student where code not in (

 select s.code
 from programs p inner join students s on (p.degree = s.degree)
 left outer join exams e on (e.course = p.course and e.student = s.code)
 where e.course is null

)
</code></pre>

blocks|key|4002181|text|我喜欢使用聚合来处理这些事情。|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4002182|select+s.code,+s.name,+s.degree
from+students+s+join
+++++programs+p
+++++on+p.degree+=+s.degree+join
+++++exams+e
+++++on+e.student+=+s.code+and+e.course+=+p.course
group+by+s.code,+s.name,+s.degree
having+count(distinct+e.course)+=+(select+count(*)+from+programs+p2+where+p2.degree+=+p.degree);|code-block|syntax|javascript|4002183|请注意，这包括degree和学生。毕竟，学生可以双倍主修。|offset|length|style|CODE|4002184|entityMap^0|0|0|7|6|0^^$0|@$1|2|3|4|5|6|7|O|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|P|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|Q|8|@$I|R|J|S|K|L]]|9|@]|A|$]]|$1|M|3|-4|5|6|7|T|8|@]|9|@]|A|$]]]|N|$]]

I like using aggregation for these things.

<pre><code>select s.code, s.name, s.degree
from students s join
 programs p
 on p.degree = s.degree join
 exams e
 on e.student = s.code and e.course = p.course
group by s.code, s.name, s.degree
having count(distinct e.course) = (select count(*) from programs p2 where p2.degree = p.degree);
</code></pre>

Note that this includes the <code>degree</code> as well as the student. After all, students could double major.

blocks|key|4002259|text|与Preli的答案类似，我会使用左连接来突出显示尚未参加的考试：|type|unstyled|depth|inlineStyleRanges|entityRanges|data|4002260|SELECT+s.code,+s.name,+p.course
FROM
++students+s
++INNER+JOIN
++programs+p+
++ON+(p.degree+=+s.degree)|code-block|syntax|javascript|4002261|这实际上是学生在课程中必须参加的考试清单。|4002262|现在添加他们已经参加的考试，并为他们没有参加的考试保留空值：|4002263|SELECT+s.code,+s.name,+p.course,+e.course
FROM
++students+s
++INNER+JOIN
++programs+p+
++ON+(p.degree+=+s.degree)

++LEFT+OUTER+JOIN
++exams+e
++ON+e.student+=+s.code+AND+e.course+=+p.course|4002264|结果将如下所示：|4002265|Code+++%7C+Name+%7C+p.Course+%7C+e.Course
stu001+%7C+John+%7C+Calc+A+++%7C+Calc+A
stu001+%7C+John+%7C+Calc+B+++%7C+Calc+B
stu002+%7C+Doe++%7C+Calc+A+++%7C+<null>
stu002+%7C+Doe++%7C+Calc+B+++%7C+<null>|4002266|现在，将其缩减为每门课程都参加过考试的学生的列表。我们可以通过检查计数(+e.course+)与计数(+p.course+)是否相同来实现这一点，因为NULL不是由COUNT()计数的，因此e.course中出现任何null+(无检查)都会减少计数，而不是计算p.course的总出现次数(我也可以使用count(*))：|4002267|SELECT+s.code,+s.name
FROM
++students+s
++INNER+JOIN
++programs+p+
++ON+(p.degree+=+s.degree)

++LEFT+OUTER+JOIN
++exams+e
++ON+e.student+=+s.code+AND+e.course+=+p.course

GROUP+BY+s.code,+s.name
HAVING+COUNT(p.course)+=+COUNT(e.course)|4002268|John的COUNT(p.Course)将是2，COUNT(e.course)也将是2，因此他显示(只有一次，因为他是分组的。无名氏的计数(p.course)是2，但是计数(e.course)是0，因为所有的值都是空的，2+!=+0，所以他是隐藏的|4002269|entityMap^0|0|0|0|0|0|0|0|0|0|0^^$0|@$1|2|3|4|5|6|7|Y|8|@]|9|@]|A|$]]|$1|B|3|C|5|D|7|Z|8|@]|9|@]|A|$E|F]]|$1|G|3|H|5|6|7|10|8|@]|9|@]|A|$]]|$1|I|3|J|5|6|7|11|8|@]|9|@]|A|$]]|$1|K|3|L|5|D|7|12|8|@]|9|@]|A|$E|F]]|$1|M|3|N|5|6|7|13|8|@]|9|@]|A|$]]|$1|O|3|P|5|D|7|14|8|@]|9|@]|A|$E|F]]|$1|Q|3|R|5|6|7|15|8|@]|9|@]|A|$]]|$1|S|3|T|5|D|7|16|8|@]|9|@]|A|$E|F]]|$1|U|3|V|5|6|7|17|8|@]|9|@]|A|$]]|$1|W|3|-4|5|6|7|18|8|@]|9|@]|A|$]]]|X|$]]

In a similar vein to Preli's answer, I'd use a left join to highlight exams that had not been taken:

<pre><code>SELECT s.code, s.name, p.course
FROM
 students s
 INNER JOIN
 programs p 
 ON (p.degree = s.degree)
</code></pre>

This is effectively the list of exams a student must take for the course

Now add on the exams they have taken, and leave nulls for those they havent:

<pre><code>SELECT s.code, s.name, p.course, e.course
FROM
 students s
 INNER JOIN
 programs p 
 ON (p.degree = s.degree)

 LEFT OUTER JOIN
 exams e
 ON e.student = s.code AND e.course = p.course
</code></pre>

The results would look like:

<pre><code>Code | Name | p.Course | e.Course
stu001 | John | Calc A | Calc A
stu001 | John | Calc B | Calc B
stu002 | Doe | Calc A | &lt;null&gt;
stu002 | Doe | Calc B | &lt;null&gt;
</code></pre>

Now reduce it to a list of just students that have taken an exam for every course. We can do this by checking that the COUNT(e.course) is the same as the COUNT(p.course) because NULL isn't counted by COUNT() so any occurrences of null (no exam) in e.course reduce the count, compared to counting the overall occurrences of p.course (i could also have used count(*)):

<pre><code>SELECT s.code, s.name
FROM
 students s
 INNER JOIN
 programs p 
 ON (p.degree = s.degree)

 LEFT OUTER JOIN
 exams e
 ON e.student = s.code AND e.course = p.course

GROUP BY s.code, s.name
HAVING COUNT(p.course) = COUNT(e.course)
</code></pre>

John's COUNT(p.Course) would be 2 and COUNT(e.course) would also be 2, so he shows (just once, because he's grouped. Doe's COUNT(p.course) is 2 but COUNT(e.course) is 0, because all values are null, and 2 != 0 so he is hidden

I've been trying to do a "Not exist" approach to isolate the answer, but I think I've gone a bit too deep. I'm limited to essentially only using like really basic stuff for my intro to database class. So, basically only INNER JOIN, UNION, and CASE and maybe a few other stuff, but probably best assume like the bare bones stuff. We're not allowed to use coalesce yet.

Students who have taken the test for every course in their degree

数据库

我一直试图用一种“不存在”的方法来隔离问题的答案，但我觉得我做得有点太深入了。在我的数据库入门类中，我基本上只能使用一些非常基本的东西。所以，基本上只有INNER、UNION和CASE，也许还有一些其他的东西，但最好假设是最基本的东西。我们还不允许使用coalesce。

问参加了学位中每门课程的考试的学生
EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问参加了学位中每门课程的考试的学生EN

社区

活动

资源

关于

腾讯云开发者

热门产品

热门推荐

更多推荐

问参加了学位中每门课程的考试的学生
EN