MySQL使用ROLLUP生成汇总行时部分列求和为0的解决方案问询

问题原因 #

你的ROLLUP操作和关联B、P统计子查询写在了同一个查询层级：
当ROLLUP生成汇总行时，该行的F.eindat值为NULL，执行LEFT JOIN时无法匹配到B、P子查询中任何非NULL的日期记录，导致B.KDA和P.Freigabe返回NULL，经过CASE判断后就转换为了0，出现汇总结果错误。

修复方案 #

调整查询逻辑分层：先在子查询中计算出每个日期的全部正确指标，再在外层对已经生成的每日指标做ROLLUP汇总，避免汇总行再关联子查询匹配不到值的问题。

修复后代码 #

SELECT 
  CASE WHEN ISNULL(Datum) THEN 'Summe' ELSE Datum END AS Datum,
  SUM(`Anzahl angenommen`) AS `Anzahl angenommen`,
  SUM(`unvollständig`) AS `unvollständig`,
  SUM(KDA) AS KDA,
  SUM(Freigabe) AS Freigabe
FROM (
  SELECT 
    F.eindat AS Datum,
    COUNT(F.eindat) AS 'Anzahl angenommen',
    COUNT(T.blocker) AS 'unvollständig',
    CASE WHEN B.KDA IS NULL THEN 0 ELSE B.KDA END AS KDA,
    CASE WHEN P.Freigabe IS NULL THEN 0 ELSE P.Freigabe END AS Freigabe
  FROM mukl.fall F
  LEFT JOIN mukl.ticket T ON T.fall = F.ID
  LEFT JOIN (
    SELECT F.beadat AS Datum, COUNT(F.beadat) AS KDA
    FROM mukl.fall F
    WHERE F.eindat >= '2021-08-07'
    GROUP BY F.beadat
  ) B ON B.Datum = F.eindat
  LEFT JOIN (
    SELECT F.prudat AS Datum, COUNT(F.prudat) AS Freigabe
    FROM mukl.fall F
    WHERE F.eindat >= '2021-08-07'
    GROUP BY F.prudat
  ) P ON P.Datum = F.eindat
  WHERE F.eindat >= '2021-08-07'
  GROUP BY F.eindat
) AS DT
GROUP BY Datum WITH ROLLUP;

内容的提问来源于stack exchange，提问作者momo