mysql - 我如何找到特定月份的行数并考虑所有日期

Question

我有一个 student_registration 表，其中包含如下列，

id name reg_date
1  John 2018-03-05
2  Ramu 2018-03-07
3  Merl 2018-03-07
4  Petr 2018-03-31

我想要统计 3 月特定月份每天注册的学生数量

date        count

2018-03-01  0
2018-03-02  0
2018-03-03  0
2018-03-04  0
2018-03-05  1
2018-03-06  0
2018-03-07  2
------
------
2018-03-31  0

我怎样才能实现上述目标，

我试过了，但是这个查询只给出了存在计数的记录

SELECT Date(s.date_reg) AS month_date, 
       Count(s.id)                AS count_stud 
FROM   student_registration s 

我没有包含所有日期的任何其他日期表

Answer 1

您可以使用以下查询生成日期:

SELECT '2018-03-01' + INTERVAL t.n - 1 day day 
FROM   (SELECT a.n + b.n * 10 + 1 n 
        FROM   (SELECT 0 AS N 
                UNION ALL 
                SELECT 1 UNION ALL 
                SELECT 2 UNION ALL 
                SELECT 3 UNION ALL 
                SELECT 4 UNION ALL 
                SELECT 5 UNION ALL 
                SELECT 6 UNION ALL 
                SELECT 7 UNION ALL 
                SELECT 8 UNION ALL 
                SELECT 9) a, 
               (SELECT 0 AS N 
                UNION ALL 
                SELECT 1 UNION ALL 
                SELECT 2 UNION ALL 
                SELECT 3 UNION ALL 
                SELECT 4 UNION ALL 
                SELECT 5 UNION ALL 
                SELECT 6 UNION ALL 
                SELECT 7 UNION ALL 
                SELECT 8 UNION ALL 
                SELECT 9) b 
        ORDER  BY n) t
WHERE  t.n <= Datediff(Last_day('2018-03-01'), '2018-03-01')+ 1 

您可以根据需要更改数字。要在您的解决方案中使用它，您可以加入此表。

SELECT Date(dts.day) AS month_date, 
   Count(s.id)   AS count_stud 
FROM   (SELECT '2018-03-01' + INTERVAL t.n - 1 day day 
    FROM   (SELECT a.n + b.n * 10 + 1 n 
            FROM   (SELECT 0 AS N 
                UNION ALL 
                SELECT 1 UNION ALL 
                SELECT 2 UNION ALL 
                SELECT 3 UNION ALL 
                SELECT 4 UNION ALL 
                SELECT 5 UNION ALL 
                SELECT 6 UNION ALL 
                SELECT 7 UNION ALL 
                SELECT 8 UNION ALL 
                SELECT 9) a, 
               (SELECT 0 AS N 
                UNION ALL 
                SELECT 1 UNION ALL 
                SELECT 2 UNION ALL 
                SELECT 3 UNION ALL 
                SELECT 4 UNION ALL 
                SELECT 5 UNION ALL 
                SELECT 6 UNION ALL 
                SELECT 7 UNION ALL 
                SELECT 8 UNION ALL 
                SELECT 9) b 
            ORDER  BY n) t 
    WHERE  t.n <= Datediff(Last_day('2018-03-01'), '2018-03-01') 
                  + 1) dts 
   LEFT OUTER JOIN student_registration s 
                ON Date(s.date_reg) = Date(dts.day) 

有多种方法可以做到这一点。这是您可以在不创建新表的情况下使用的更简单的方法之一。您可以更改日期范围以获得所需的任何时间表。