mysql - MySQL - 从另一个表的 GROUP BY 查询中从一个表中获取关联的值列表
问题描述
我有一个包含用户的表,一个包含分配的表,以及一个包含任务的表,每个任务都与分配和用户相关联。我想获取在给定作业上有任务的学生列表。(对于我的问题,我已尝试尽可能简化此数据库示例。)
这是一个 SQL 小提琴: http ://sqlfiddle.com/#!9/625a13/1
作为参考,数据库结构如下所示:
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 查询 - 我只是想不出该怎么做。)
解决方案
您需要使用 GROUP_CONCAT 并加入另一个用户表
SELECT a.id, a.title, u.name AS teacher_name, GROUP_CONCAT(us.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
JOIN users AS us ON t.user_id = us.id
GROUP BY a.id
推荐阅读
- sql - 如何在 SQL 查询中添加和分组项目
- google-apps-script - 如何在运行谷歌应用脚本时显示负载指示器
- logstash - Logstash 无法改变文本日志
- swift - 呈现 - 在 SwiftUI 顶部显示视图(我不想导航)
- docker - Docker 没有在代理下连接
- vb.net - VB 在启动画面上加载照片
- php - Doctrine - 返回不为 0 的结果
- c# - 如何调试 C# .net 中的装饰器方法?
- python - 数数河内塔的动作
- soap - SOAP WebService:soapenv:Server.userExceptionorg.xml.sax.SAXException:坏信封标签:定义