mysql - 使用别名优化 Select 子查询

Question

我有一个查询，它从子查询中的同一个表中选择多个值

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 ...

但我不断收到以下错误

"message":SQLSTATE[21000]: Cardinality violation: 1241 Operand should contain 1 column(s)

预期输出

{
"spts": "89",
"smat": "44",
"sbio": "45"
}

Answer 1

使用左连接简化它:

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 子句)中的其他内容，这可能不会加快查询速度。