多表多求和计算：需忽略NULL值的MySQL查询疑难

我明白你遇到的困扰了——当某个commthumbs表没有对应用户的数据时，整个求和结果就变成NULL，哪怕其他表都有有效数据。核心问题出在NULL值的传播特性：只要表达式里有一个NULL，整个计算结果就会变成NULL，而你之前的IFNULL/ISNULL/COALESCE可能没用到正确的位置上。

为什么之前的尝试没生效？ #

假设你之前是直接对连接后的单个字段用IFNULL，比如IFNULL(t1.thumbsup, 0) - t1.thumbsdown，那如果t1.thumbsdown是NULL，计算结果还是NULL；或者你把IFNULL加在了整个求和表达式外面，那只要其中一个差值是NULL，整个总和就会先变成NULL，再被IFNULL转成0，这显然不是你想要的（你希望有数据的表的差值正常累加，无数据的表贡献0）。

两种可行的解决方法 #

方法1：对每个差值的单个字段先做NULL处理

针对每个commthumbs表的thumbsup和thumbsdown分别用COALESCE（或IFNULL）转为0，再计算差值，最后求和：

SELECT 
  user.userID,
  -- 对每个表的差值单独处理NULL
  (COALESCE(t1.thumbsup, 0) - COALESCE(t1.thumbsdown, 0)) +
  (COALESCE(t2.thumbsup, 0) - COALESCE(t2.thumbsdown, 0)) +
  (COALESCE(t3.thumbsup, 0) - COALESCE(t3.thumbsdown, 0)) +
  (COALESCE(t4.thumbsup, 0) - COALESCE(t4.thumbsdown, 0)) +
  (COALESCE(t5.thumbsup, 0) - COALESCE(t5.thumbsdown, 0)) AS total_score
FROM user
LEFT JOIN commthumbs1 t1 ON user.userID = t1.userID
LEFT JOIN commthumbs2 t2 ON user.userID = t2.userID
LEFT JOIN commthumbs3 t3 ON user.userID = t3.userID
LEFT JOIN commthumbs4 t4 ON user.userID = t4.userID
LEFT JOIN commthumbs5 t5 ON user.userID = t5.userID

方法2：用子查询预先计算每个表的差值

先在子查询里算出每个用户在单张commthumbs表的差值（并处理NULL为0），再左连接到主查询中求和：

SELECT 
  u.userID,
  COALESCE(t1.diff, 0) +
  COALESCE(t2.diff, 0) +
  COALESCE(t3.diff, 0) +
  COALESCE(t4.diff, 0) +
  COALESCE(t5.diff, 0) AS total_score
FROM user u
-- 子查询预先计算单表差值，无数据时diff不会出现在结果中，左连接后为NULL
LEFT JOIN (
  SELECT userID, COALESCE(thumbsup, 0) - COALESCE(thumbsdown, 0) AS diff 
  FROM commthumbs1
) t1 ON u.userID = t1.userID
LEFT JOIN (
  SELECT userID, COALESCE(thumbsup, 0) - COALESCE(thumbsdown, 0) AS diff 
  FROM commthumbs2
) t2 ON u.userID = t2.userID
LEFT JOIN (
  SELECT userID, COALESCE(thumbsup, 0) - COALESCE(thumbsdown, 0) AS diff 
  FROM commthumbs3
) t3 ON u.userID = t3.userID
LEFT JOIN (
  SELECT userID, COALESCE(thumbsup, 0) - COALESCE(thumbsdown, 0) AS diff 
  FROM commthumbs4
) t4 ON u.userID = t4.userID
LEFT JOIN (
  SELECT userID, COALESCE(thumbsup, 0) - COALESCE(thumbsdown, 0) AS diff 
  FROM commthumbs5
) t5 ON u.userID = t5.userID

这两种方法都能确保：即使某张commthumbs表没有用户的数据，该表对应的差值会被当作0参与求和，最终结果不会变成NULL。

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