mysql - 如果计数 == 0，SQL 返回 0

Question

我想获取 Mysql 中两个日期之间的所有计数，但是，当特定日期中不存在值时，SQL 结果不返回任何值。但我想返回0。

    SELECT
    DATE(`date`) AS RegistrationDate,   COUNT(`id`) AS NumberOfRegistrations
 FROM
    Users
 WHERE
    `date` between "2018/05/1" and "2018/05/13"
 GROUP BY
    RegistrationDate

Answer 1

正如我在评论中所说，这是我解决您问题的方法。
(可能还有其他方法)

连接中的第一个表创建日期范围。

SELECT
  DATE(`gen_date`) AS RegistrationDate, 
  COUNT(`id`) AS NumberOfRegistrations 
FROM 
  (
    SELECT * 
    FROM
      (SELECT adddate('1970-01-01', t4 * 10000 + t3 * 1000 + t2 * 100 + t1 * 10 + t0) gen_date 
        FROM 
          (select 0 t0 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9) t0, 
          (select 0 t1 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)  t1, 
          (select 0 t2 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)  t2,  
          (select 0 t3 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)  t3, 
          (select 0 t4 union select 1 union select 2 union select 3 union select 4 union select 5 union select 6 union select 7 union select 8 union select 9)  t4
      ) v 
    WHERE 
      gen_date BETWEEN "2018-05-01" AND "2018-05-13"
  ) d 
LEFT OUTER JOIN users u ON Date(`gen_date`) = Date(`date`)
GROUP BY
  gen_date

查看它的工作情况 here , Updated