我有一个查询，它从子查询中的同一个表中选择多个值
SELECT ...,
(
SELECT IFNULL(SUM(pts), 0)
FROM grades
WHERE s.id = student
) AS spts,
(
SELECT IFNULL(SUM(mat), 0)
FROM grades
WHERE s.id = student
) AS smat,
(
SELECT IFNULL(SUM(bio), 0)
FROM grades
WHERE s.id = student
)AS sbio
FROM ...
它现在工作得很好，但我认为它可以优化为一个子查询而不是 3 个。
我试过的
SELECT ...,
(
SELECT IFNULL(SUM(pts), 0) AS spts, IFNULL(SUM(mat), 0) AS smat, IFNULL(SUM(bio), 0) AS sbio
FROM grades
WHERE s.id = student
)
FROM ...
但我不断收到以下错误
“消息”：SQLSTATE[21000]：基数违规：1241 操作数应包含 1 列
预期产出
{
"spts": "89",
"smat": "44",
"sbio": "45"
}
使用 a 简化它left join：
SELECT ...,
COALESCE(spts, 0) as spts,
COALESCE(smat, 0) as smat,
COALESCE(sbio, 0) as sbio
FROM ... LEFT JOIN
(SELECT student, SUM(pts) AS spts, SUM(mat) AS smat, SUM(bio) AS sbio
FROM grades
GROUP BY student
) g
ON s.id = g.student;
根据FROM子句（以及潜在的WHERE子句）中的其他内容，这可能不会加快查询速度。
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句