MySQL - 从另一个表中使用 GROUP BY 的查询中的一个表获取关联的值列表

Question

我有一个包含用户的表、一个包含分配的表和一个包含任务的表，每个任务都与一个分配和用户相关联。我想获取在给定作业上有任务的学生列表。 (对于我的问题，我已尝试尽可能简化此数据库示例。)

这是一个 SQL fiddle : http://sqlfiddle.com/#!9/625a13/1

作为引用，DB 结构如下所示:

CREATE TABLE users (
  id SERIAL,
  name VARCHAR(32),
  PRIMARY KEY (id)
);

CREATE TABLE assignments (
  id SERIAL,
  title VARCHAR(45),
  assigned_by_user_id INT,
  PRIMARY KEY (id)
);

CREATE TABLE tasks (
  id SERIAL,
  user_id INT,
  assignment_id INT,
  complete INT,
  PRIMARY KEY (id)
);

INSERT INTO users VALUES (1, 'Student Joe'), (2, 'Student Fred'), (3, 'Teacher Bob');
INSERT INTO assignments VALUES (1, 'Math Homework', 3), (2, 'History Homework', 3), (3, 'Science Homework', 3);
INSERT INTO tasks VALUES (1,1,1,1), (2,1,2,1), (3,1,3,0), (4,2,1,1), (5,2,2,0), (6,2,3,0);

这是来自此数据的查询示例:

SELECT a.id, a.title, u.name AS teacher_name, '' AS students_included,
(COUNT(CASE WHEN t.complete = 1 THEN t.id ELSE NULL END)/COUNT(t.id)*100) AS percent_complete
FROM assignments AS a
JOIN users AS u ON a.assigned_by_user_id = u.id 
LEFT JOIN tasks AS t ON a.id = t.assignment_id 
GROUP BY a.id 

这是我所拥有的截图:


这是我的目标结果:


如何让查询返回与这些作业关联的学生姓名？在某些情况下，可能没有任何任务(没有学生)，在某些情况下一个学生，在某些情况下多个学生。我想以某种方式获取相关列中学生姓名的数组/CSV 列表。

(我知道我可以对每一行进行单独的查询——例如在 PHP 中，当我遍历结果时——并得到这个结果，但这会很慢、很麻烦，而且会占用大量资源——我想得到学生姓名值如果可能的话，使用单个 MySQL 查询 - 我只是想不出怎么做。)

Answer 1

您可以使用 GROUP_CONCAT(类似于 Oracle 中的 LISTAGG)，并且需要再加入一个 USERS 表以提取学生姓名

SELECT a.id, a.title, u.name AS teacher_name, GROUP_CONCAT(stu.name) AS students_included,
(COUNT(CASE WHEN t.complete = 1 THEN t.id ELSE NULL END)/COUNT(t.id)*100) AS percent_complete
FROM assignments AS a
JOIN users AS u ON a.assigned_by_user_id = u.id 
LEFT JOIN tasks AS t ON a.id = t.assignment_id
LEFT JOIN users AS stu ON stu.id = t.user_id 
GROUP BY a.id, a.title, u.name 

不要忘记在 GROUP BY 子句中添加列。