SQL JOIN+COUNT查询

Question

我正在尝试显示一个显示课程列表的表格。我想加入一个栏目，显示已完成课程的人数，如果可能，也显示尚未完成课程的人数。

我的表结构是这样的：

Courses
courseid | title | etc

Studying
courseid | studentid | some other fields | has_completed

理想情况下，输出应该是这样的，例如：

Course Title | some other fields | Students completed | Students not completed
Something      etc                 5                    3

我很难计算出这两个单独的字段。有没有人能推荐一个不错的方法呢？

任何建议，谢谢。

Answer 1

类似这样的代码应该可以：

SELECT c.CourseID, c.Title, SUM(s.Has_Completed) Completed, SUM(1-s.Has_Completed) NotCompleted
FROM Courses c
LEFT JOIN Studying s ON c.CourseID = s.CourseID
GROUP BY c.CourseID, c.Title

Answer 2

如果has_completed的值为1代表true，0代表false，你可以很简单地解决这个问题：

SELECT c.title, SUM(s.has_completed) as completed, COUNT(studentid) - SUM(s.has_completed) as notCompleted
FROM Courses c, Studying s
where c.courseid = s.courseid
GROUP BY c.courseid, c.title 

Answer 3

尝试使用SQL count command

SELECT COUNT(has_completed) FROM Studying WHERE has_completed='1'