MySQL:逗号分隔的项目数列表 [英] MySQL: comma-separated list of numbers of items

问题描述

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

I have a learning system based on students watching videos then answering questions attached to those videos. The (simplified) model is basically this:

课程(课程定义，例如基础代数)

Courses (a course definition, e.g. Basic Algebra)

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

课程实例(当用户开始课程时会在此处登录一个条目)

Course instances (an entry is logged in here when a user begins a course)

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

视频(与给定课程相关)

Videos (associated with a given course)

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

问题(与给定视频相关)

Questions (associated with a given video)

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

我想要的是获取所有课程实例和每个视频的问题数量，逗号分隔.

What I want is to get all course instances and the number of questions per video, comma separated.

例如，如果一门课程附有三个视频，第一个视频有两个问题，第二个和第三个视频各有一个问题，我想要这个:

So for example, if a course had three videos attached to it, and the first video had two questions and the second and third video had one question each, I want this:

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

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

The best I've got from my own efforts is this:

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 序列推断出第一个视频有三个问题，第二个和第三个有两个问题.

...from which I can infer, by the repeated video ID sequences, that the first vid has three questions, and the second and third two.

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

But how can I end up with my desired output above?

推荐答案

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

You need to aggregate twice. One method is before doing the 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;

子查询计算每个视频的问题数.外部查询然后将这些连接在一起成为您想要的结果字符串.

The subquery counts the number of questions for each video. The outer query then concatenates these together into the result string that you want.

这篇关于MySQL:逗号分隔的项目数列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！