SQLite单查询获取两表指定列平均值并处理无行默认值

当然可以用单条查询搞定这个需求！之前你担心IFNULL在无匹配行时不生效，核心问题是没利用好聚合函数的特性——哪怕没有符合条件的行，AVG()这类聚合函数也会返回一行NULL结果，而不是完全没有输出。基于这个特性，我们可以用子查询+COALESCE（或IFNULL）来实现目标：

方案一：子查询交叉连接（最直观的写法） #

这种写法把两个表的聚合逻辑拆成独立子查询，再通过交叉连接合并结果，最后用COALESCE将NULL转为0：

SELECT
  COALESCE(t1.avg_heartRate, 0) AS avg_heartRate,
  COALESCE(t1.avg_bodyWater, 0) AS avg_bodyWater,
  COALESCE(t2.avg_stepCount, 0) AS avg_stepCount,
  COALESCE(t2.avg_distance, 0) AS avg_distance,
  COALESCE(t2.avg_calories, 0) AS avg_calories,
  COALESCE(t2.avg_sleep, 0) AS avg_sleep
FROM
  -- 计算table1的平均值，无匹配行时返回一行NULL
  (SELECT avg(heartRate) AS avg_heartRate, avg(bodyWater) AS avg_bodyWater 
   FROM table1 
   WHERE userid = 1234 AND month = 1 AND year = 2018) t1
-- 交叉连接table2的聚合结果，因为两个子查询都最多一行，所以只会返回一行数据
CROSS JOIN
  (SELECT avg(stepCount) AS avg_stepCount, avg(distance) AS avg_distance,
          avg(calories) AS avg_calories, avg(sleep) AS avg_sleep 
   FROM table2 
   WHERE userid = 1234 AND month = 1 AND year = 2018) t2

方案二：LEFT JOIN 关联常数表（兼容更复杂场景） #

如果后续需要扩展多用户/多时间维度的查询，也可以用一个包含过滤条件的常数表作为基础，再LEFT JOIN两个表的聚合结果：

SELECT
  COALESCE(avg(t1.heartRate), 0) AS avg_heartRate,
  COALESCE(avg(t1.bodyWater), 0) AS avg_bodyWater,
  COALESCE(avg(t2.stepCount), 0) AS avg_stepCount,
  COALESCE(avg(t2.distance), 0) AS avg_distance,
  COALESCE(avg(t2.calories), 0) AS avg_calories,
  COALESCE(avg(t2.sleep), 0) AS avg_sleep
FROM
  -- 构造一个包含过滤条件的临时行，确保基础结果存在
  (SELECT 1234 AS userid, 1 AS month, 2018 AS year) filter
LEFT JOIN table1 t1 ON t1.userid = filter.userid AND t1.month = filter.month AND t1.year = filter.year
LEFT JOIN table2 t2 ON t2.userid = filter.userid AND t2.month = filter.month AND t2.year = filter.year

关键说明 #

• 两种方案都解决了“无匹配行返回0”的问题：聚合函数在无数据时返回NULL，COALESCE（或MySQL的IFNULL）会将NULL替换为0。
• 相比原来的两个查询，单查询减少了数据库交互次数，在高并发场景下性能更优，同时代码层无需手动合并结果，逻辑更简洁。
• 注意：COALESCE是SQL标准函数，兼容大多数数据库（MySQL、PostgreSQL、SQL Server等）；如果只用MySQL，IFNULL也可以达到同样效果。

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