MySQL SUM(列) + (SUBQUERY WITH SUM)

Question

我想查询一个团队的积分。通过在列上执行 SUM + 来自具有相同团队 ID 的另一个表的 SUM 来添加这些点。我试着这样写:

SELECT 
    k.id, 
    s.fylke, 
    s.Kommune, 
    s.Skolenavn, 
    k.schoolid, 
    k.number, 
    k.letter, 
    SUM(e.amount) + (SELECT SUM(poeng) FROM oppdrag WHERE klasseid=k.id ) AS poeng 
FROM skoler AS s, klasser AS k, etappe AS e 
WHERE s.id=k.schoolid AND k.id=e.klasseid AND e.year='2016'
GROUP BY k.id 
ORDER BY poeng

问题是，当我以这种方式编写它时，如果团队在表“oppdrag”中有一个条目，它会给出正确的点数，否则它只会返回 NULL 作为点数 (poeng)。

Answer 1

如果您的子查询返回 NULL，这将导致尝试将一个数字与 NULL 相加，返回 NULL。

要解决此问题，您可以尝试使用 IFNULL 函数，如果查询未返回任何值，它将用 0 替换它:

SELECT 
    k.id, 
    s.fylke, 
    s.Kommune, 
    s.Skolenavn, 
    k.schoolid, 
    k.number, 
    k.letter, 
    SUM(e.amount) + IFNULL((SELECT SUM(poeng) FROM oppdrag WHERE klasseid=k.id ), 0) AS poeng 
FROM skoler AS s, klasser AS k, etappe AS e 
WHERE s.id=k.schoolid AND k.id=e.klasseid AND e.year='2016'
GROUP BY k.id 
ORDER BY poeng