mysql - 加入日历表并过滤某些用户时显示缺失的日期

Question

我生成了一个calendar表格，其中包含从 2000-01-01 到 2050-12-31 的每个日期。

除此之外，我还有user表格，该表格包含以下列：

id, created, is_profile_public

最后，我有一个表格，将我的用户链接到一个或多个组织（这是可选的，不是每个用户都会链接到一个组织）。此表称为user_organisation。

我想为统计目的获取数据，我从用户的最早创建日期到昨天获取数据。缺失的日期应该在每列中只包含 0 个值。

我创建了这个查询：

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)
LEFT JOIN (select max(organisation_id), user_id from user_organisation group by user_id) AS uo on uo.user_id=u.id
WHERE 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) AND (c.datefield BETWEEN (SELECT MIN(DATE(created)) FROM user) AND DATE(NOW()))
GROUP BY c.datefield

这仅向我显示创建用户的日期。但它没有给我任何关于没有创建用户的日期的行。

标签： mysqlsql