关联calendar表过滤指定用户时如何展示无用户创建的缺失日期

问题根因 #

你在WHERE子句中直接添加了u.id IN (...)的过滤规则，会把左连接后没有匹配到用户的calendar表行全部过滤：没有用户创建的日期对应的u.id为NULL，无法满足IN条件，因此这些日期不会出现在返回结果中。

解决方案 #

将用户ID的过滤条件从WHERE子句移动到user表的左关联ON子句中，仅过滤关联的用户数据，不会剔除没有用户匹配的日期行。如果需求是统计到昨日，可将原时间范围中的DATE(NOW())替换为DATE_SUB(CURDATE(), INTERVAL 1 DAY)。

修正后的查询语句 #

SELECT c.datefield, DATE(u.created) AS created, 
   SUM(case when u.is_profile_public=1 AND uo.user_id is null then 1 else 0 end) as amount_public_volunteers, 
   SUM(case when u.is_profile_public=0 AND uo.user_id is null then 1 else 0 end) as amount_private_volunteers, 
   SUM(case when u.is_profile_public=1 AND uo.user_id is not null then 1 else 0 end) as amount_public_volunteers_admin, 
   SUM(case when u.is_profile_public=0 AND uo.user_id is not null then 1 else 0 end) as amount_private_volunteers_admin
FROM calendar AS c
LEFT OUTER JOIN user AS u ON c.datefield = DATE(u.created) AND u.id IN (87,  89, 172, 185, 186, 341, 342, 343, 344, 443, 444, 445,
  446, 455, 459, 463,  20,  94,  61, 100, 101, 102, 109, 112,
  113, 115, 132, 166, 184, 198, 199, 203, 205, 206, 207, 271,
  272, 273, 274, 275, 276, 277, 280, 278, 279, 281, 284, 282,
  283, 285, 288, 286, 287, 289, 292, 290, 291, 293, 294, 295,
  302, 303, 304, 305, 306, 307, 308, 309, 310, 311, 312, 313,
  318, 314, 316, 315, 319, 317, 324, 325, 326, 328, 330, 332,
  340, 358, 369, 383, 384, 391, 395, 396, 397, 398, 405, 399,
  406, 400, 409, 401)
LEFT JOIN (select max(organisation_id), user_id from user_organisation group by user_id) AS uo on uo.user_id=u.id
WHERE c.datefield BETWEEN (SELECT MIN(DATE(created)) FROM user) AND DATE_SUB(CURDATE(), INTERVAL 1 DAY)
GROUP BY c.datefield

修改后没有用户创建的日期对应的所有统计字段都会返回0，符合需求。

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