My Sql 查询连续天数数据

1、新建1个日期表当中间表

 

　　　　

CREATE TABLE num (i INT);-- 创建一个表用来储存0-9的数字

INSERT INTO num (i) VALUES (0), (1), (2), (3), (4), (5), (6), (7), (8), (9);-- 生成0-9的数字，方便以后计算时间

CREATE TABLE  IF NOT EXISTS calendar(DATE DATE); -- 生成一个存储日期的表，date是字段名

-- 这里是生成并插入日期数据

INSERT INTO calendar(DATE) SELECT

    ADDDATE(

        (   -- 起始日期，从2017-1-1开始

            DATE_FORMAT("2017-1-1", '%Y-%m-%d')

        ),

        numlist.id

    ) AS `date`

FROM

    (

        SELECT

            n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000 AS id

        FROM

            num n1

        CROSS JOIN num AS n10

        CROSS JOIN num AS n100

        CROSS JOIN num AS n1000

    ) AS numlist;

 

 

2、联接日期表查询相应的数据

 

SELECT COUNT(u.id) AS num, c.date FROM calendar AS c

LEFT JOIN users AS u ON c.date = u.`created_at`

WHERE c.date BETWEEN '2017-05-01' AND '2017-05-07'

GROUP BY c.`date`

ORDER BY c.`date`

 

 

 

源：https://www.php.cn/mysql-tutorials-418668.html