MySQL:以逗号分隔的项目数列表

Question

我有一个基于学生观看视频然后回答这些视频所附问题的学习系统。 (简化的)模型基本上是这样的:

类(class)(类(class)定义，例如基础代数)

----------------------------------------
| id   | int     | auto_increment | PK |
| name | varchar |                |    |
----------------------------------------

类(class)实例(当用户开始类(class)时，此处会登录一个条目)

---------------------------------------------
| id        | int     | auto_increment | PK |
| course_id | int     |                | FK |
---------------------------------------------

视频(与给定类(class)相关)

---------------------------------------------
| id        | int     | auto_increment | PK |
| course_id | int     |                | FK |
| name      | varchar |                |    |
---------------------------------------------

问题(与给定视频相关)

--------------------------------------------
| id       | int     | auto_increment | PK |
| video_id | int     |                | FK |
| q_text   | varchar |                |    |
--------------------------------------------

我想要的是获取所有类(class)实例和每个视频的问题数量，以逗号分隔。

例如，如果一门类(class)附加了三个视频，第一个视频有两个问题，第二个和第三个视频各有一个问题，我想要这个:

-----------------------------------
| course_instance_id | qs_per_vid |
-----------------------------------
| 1                  | 3,2,2      |
-----------------------------------

我通过自己的努力得到的最好结果是:

SELECT
    ci.id,
    COUNT(DISTINCT videos.id) AS num_vids,
    GROUP_CONCAT(questions.video_id ORDER BY questions.video_id) AS questions
FROM course_instances ci
LEFT JOIN videos ON videos.course_id = ci.course_id
LEFT JOIN questions ON questions.video_id = videos.id
GROUP BY ci.id

这会产生类似的结果:

---------------------------------
| id | num_vids | questions     | <-- actually it's the vid ID of each question
---------------------------------
| 1  | 4        | 1,1,1,2,2,3,3 |
---------------------------------

...通过重复的视频 ID 序列，我可以推断出第一个视频有三个问题，第二个和第三个视频有两个。

但是我怎样才能得到上面想要的输出呢？

Answer 1

您需要聚合两次。一种方法是在执行 join 之前:

SELECT ci.id,
       COUNT(v.id) AS num_vids,
       GROUP_CONCAT(q.numquestions ORDER BY q.video_id) AS questions
FROM course_instances ci LEFT JOIN
     videos v
     ON v.course_id = ci.course_id LEFT JOIN
     (SELECT q.video_id, COUNT(*) as numquestions
      FROM questions q
      GROUP BY q.video_id
     ) q
     ON q.video_id = v.id
GROUP BY ci.id;

子查询计算每个视频的问题数。然后，外部查询将它们连接在一起形成您想要的结果字符串。