mysql - 使用 where 子句从另一个表中选择数据

Question

这个查询有什么问题，我在这里从 3 个不同的表中选择数据。第一个考试标题来自“class_exams”表，第二个从“results”表中选择总分之和。查询在没有 where 子句的情况下也可以正常工作。

SELECT id, exam_date , (
SELECT title
FROM class_exams
WHERE result_heads.exam_id = class_exams.id
) AS exam_title, (
SELECT sum( marks )
FROM results
WHERE result_heads.id = results.head_id
) AS obt_marks

FROM `result_heads` WHERE exam_title = 'test';

错误来了

Unknown column 'exam_title' in 'where clause'

Answer 1

考虑使用加入

如果我理解表架构，它应该是这样的:

SELECT result_heads.id, result_heads.exam_date , sum( results.marks )AS obt_marks
FROM results JOIN result_heads
     ON results.exam_id = result_heads.id
GROUP BY result_heads.id, result_heads.exam_date