我有一系列表,我想从中获得以下格式的返回行:
Student ID | Last Name | First Name | Quiz Scores
-------------------------------------------------
xxxxxxx | Snow | Jon | 0,0,0,0,0,0,0,0
有3个相关的表(不能更改任何现有的DB结构):
person
-组织中所有人员的表enrollment
-学生和教师入学数据表tilt.quiz
-测验分数表,每行存储一个单独的分数
其中棘手的部分是测验分数。只有当学生参加了测验时,才会出现测验分数行。每个quiz
行都有一个module
,1-8。因此,学生可能的测验数据可以是(每一个都是单独的一行):
person_id | module | score
---------------------------
223355 | 1 | 100
223355 | 2 | 95
223355 | 4 | 80
223355 | 7 | 100
我需要以8个逗号分隔的值按正确顺序返回测验分数,无论是否遗漏了任何或所有测验。
我目前有以下查询:
SELECT
person.id,
first_name,
last_name,
GROUP_CONCAT(tilt.quiz.score) AS scores
FROM person
LEFT JOIN enrollment ON person.id = enrollment.person_id
LEFT JOIN tilt.quiz ON person.id = tilt.quiz.person_id
WHERE
enrollment.course_id = '$num' AND enrollment_status_id = 1
GROUP BY person.id
ORDER BY last_name
这方面的问题是:
- 它不按模块排列测验
- 如果遗漏了任何测验,它只会返回较少的值
因此,我需要GROUP_CONCAT
分数至少包含缺失测验值的逗号,并正确排序。
我考虑的一个解决方案是创建一个临时的测验成绩表,但我不确定这是最有效的方法,也不确定该如何进行
编辑:另一个解决方案是执行一个查询,单独检查每个测验的存在,但这似乎很笨拙(总共有9个查询,而不是1个);我希望有一种更优雅的方式。
这将如何实现?
这里有一些关于数据结构的假设,但这应该非常接近您想要的。查看GROUP_CONCAT
和COALESCE
的文档。
SELECT `person`.`id`, `person`.`first_name`, `person`.`last_name`,
GROUP_CONCAT(
COALESCE(`tilt`.`quiz`.`score`, 'N/A')
ORDER BY `tilt`.`quiz`.`module_id`
) AS `scores`
FROM `person`
CROSS JOIN `modules`
LEFT JOIN `enrollment` USING (`person_id`)
LEFT JOIN `tilt`.`quiz` USING (`person_id`, `module_id`)
WHERE (`enrollment`.`course_id` = '$num')
AND (`enrollment`.`enrollment_status_id` = 1)
GROUP BY `person`.`id`
ORDER BY `person`.`last_name`
首先要做的是在分数上使用IFNULL()函数
然后,在GROUP_CONCT 中使用ORDER BY
这是我提出的查询
SELECT
person.id,
first_name,
last_name,
GROUP_CONCAT(IFNULL(tilt.quiz.score,0) ORDER BY tilt.quiz.module) AS scores
FROM person
LEFT JOIN enrollment ON person.id = enrollment.person_id
LEFT JOIN tilt.quiz ON person.id = tilt.quiz.person_id
WHERE
enrollment.course_id = '$num' AND enrollment_status_id = 1
GROUP BY person.id
ORDER BY last_name