MYSQL:是否有条件计算加入1:N课程类别的课程注册人数

Question

我花了太长时间思考这个问题的标题，所以我希望我选择的标题是可以理解的。但让我解释一下：

CREATE TABLE IF NOT EXISTS `course` (
`course_id` int(11) NOT NULL,
`course_name` varchar(255) NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=9 DEFAULT CHARSET=utf8;
INSERT INTO `course` (`course_id`, `course_name`) VALUES
(1, 'Course A'),
(2, 'Course B'),
(3, 'Course C'),
(4, 'Course D'),
(5, 'Course E');
CREATE TABLE IF NOT EXISTS `course_category` (
`course_id` int(11) NOT NULL,
`category_id` int(11) NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
INSERT INTO `course_category` (`course_id`, `category_id`) VALUES
(1, 1),
(1, 2),
(1, 3),
(2, 2),
(3, 1),
(3, 3),
(5, 1),
(5, 3);
CREATE TABLE IF NOT EXISTS `enrolment` (
`enrolment_id` int(11) NOT NULL,
`course_id` int(11) NOT NULL,
`status` int(11) NOT NULL
) ENGINE=MyISAM AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;
INSERT INTO `enrolment` (`enrolment_id`, `course_id`, `status`) VALUES
(1, 1, 0),
(2, 1, 1),
(3, 1, 0),
(4, 3, 0),
(5, 3, 0),
(6, 3, 0),
(7, 4, 1),
(8, 4, 1),
(9, 4, 1),
(10, 4, 0);

ALTER TABLE `course`
ADD PRIMARY KEY (`course_id`);
ALTER TABLE `enrolment`
ADD PRIMARY KEY (`enrolment_id`);
ALTER TABLE `course`
MODIFY `course_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=9;
ALTER TABLE `enrolment`
MODIFY `enrolment_id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=11;

小提琴：https://www.db-fiddle.com/f/dc3bgfMS5jTxE7wmp3SJ/0

# Table `course`
+-----------+-------------+
| course_id | course_name |
+-----------+-------------+
|         1 | Course A    |
|         2 | Course B    |
|         3 | Course C    |
|         4 | Course D    |
|         5 | Course E    |
+-----------+-------------+

# Table `course_category`
+-----------+-------------+
| course_id | category_id |
+-----------+-------------+
|         1 |           1 |
|         1 |           2 |
|         1 |           3 |
|         2 |           2 |
|         3 |           1 |
|         3 |           3 |
|         5 |           1 |
|         5 |           3 |
+-----------+-------------+

# Table `enrolment`
+--------------+-----------+--------+
| enrolment_id | course_id | status |
+--------------+-----------+--------+
|            1 |         1 |      0 |
|            2 |         1 |      1 |
|            3 |         1 |      0 |
|            4 |         3 |      0 |
|            5 |         3 |      0 |
|            6 |         3 |      0 |
|            7 |         4 |      1 |
|            8 |         4 |      1 |
|            9 |         4 |      1 |
|           10 |         4 |      0 |
+--------------+-----------+--------+

(还有一个category表，但这对问题来说并不重要。(

我正在尝试获得以下结果：

1. 所有课程都必须在结果集中
2. 每个课程的类别ID必须是一个单独的字符串，用逗号分隔
3. 每个课程应计算状态为1的注册总数(请记住，"1"只是这里的一个例子，它也可以是另一个数字或要检查的字符串(

+-------------+--------------+------------+
| course_name | category_ids | enrolments |
+-------------+--------------+------------+
| Course A    | 3,1,2        |          1 |
| Course B    | 2            |          0 |
| Course C    | 1,3          |          0 |
| Course D    | NULL         |          3 |
| Course E    | 1,3          |          0 |
+-------------+--------------+------------+

我尝试过将SUM与条件一起使用，将COUNT与DISTINCT关键字和WHERE条件一起使用。但我无法实现所有必要的点。

COUNT实验：

SELECT 
course_name,
GROUP_CONCAT(DISTINCT category_id) AS category_ids,
COUNT(DISTINCT enrolment_id) as enrolments
FROM 
course
LEFT JOIN course_category USING (course_id)
LEFT JOIN enrolment USING (course_id)
WHERE status = 1 OR status IS NULL
GROUP BY course_id
+-------------+--------------+------------+
| course_name | category_ids | enrolments |
+-------------+--------------+------------+
| Course A    | 3,1,2        |          1 |
| Course D    | NULL         |          3 |
+-------------+--------------+------------+

在这种情况下，我只有在至少有一个注册状态为1或没有注册该课程的情况下才能获得该课程，否则整个课程将在结果中丢失。

SUM实验：

SELECT 
course_name,
GROUP_CONCAT(DISTINCT category_id) AS category_ids,
SUM(IF(status=1,1,0)) as enrolments
FROM 
course
LEFT JOIN course_category USING (course_id)
LEFT JOIN enrolment USING (course_id)
GROUP BY course_id
+-------------+--------------+------------+
| course_name | category_ids | enrolments |
+-------------+--------------+------------+
| Course A    | 3,1,2        |          3 |
| Course B    | 2            |          0 |
| Course C    | 1,3          |          0 |
| Course D    | NULL         |          3 |
| Course E    | 1,3          |          0 |
+-------------+--------------+------------+

在这种情况下，所有的课程都在那里，但计数是错误的，因为我无法计算不同的入学人数。

我错过了哪种说法？

Answer 1

SELECT c.course_name, 
GROUP_CONCAT(cc.category_id ORDER BY cc.category_id) category_ids,
COALESCE(e.status, 0) enrolments
FROM course c
LEFT JOIN course_category cc USING (course_id)
-- calculate statuses per course separately
LEFT JOIN (SELECT course_id, SUM(status) status   -- maybe SUM(status=1)?
FROM enrolment 
GROUP BY course_id ) e USING (course_id)
GROUP BY c.course_name, e.status

小提琴

Answer 2

我认为用相关的子查询比外部聚合更简单地表达：

select c.course_name,
(
select group_concat(cc.category_id order by cc.category_id)
from course_category cc
where cc.course_id = c.course_id
) category_ids,
(
select coalesce(sum(e.status), 0)
from enrolment e
where e.course_id = c.course_id
) enrolments
from course c
order by c.course_name