mysql中的年，月，日统计以及日历表的实现

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(datelist date); -- 生成一个存储日期的表，datalist是字段名
 
-- 这里是生成并插入日期数据
INSERT INTO calendar(datelist) SELECT
    adddate(
        (   -- 这里的起始日期，你可以换成当前日期
            DATE_FORMAT("2018-1-1", '%Y-%m-%d') 
        ),
        numlist.id
    ) AS `date`
FROM
    (
        SELECT
            n1.i + n10.i * 10 + n100.i * 100 + n1000.i * 1000+ n10000.i * 10000 AS id
        FROM
            num n1
        CROSS JOIN num AS n10
        CROSS JOIN num AS n100
        CROSS JOIN num AS n1000
        CROSS JOIN num AS n10000
    ) AS numlist;

这样就创建了2张表：num和calendar

 

2、按日期统计

SELECT
    c.datelist as date,
    COUNT(b.id) as count
FROM
    calendar AS c
LEFT JOIN (
    SELECT
        *
    FROM
        driver -- 真正想统计的表名
    WHERE
        lnvalid = 2 -- 这个是查询条件，你们酌情写自己的
    AND flag = 0 -- 这个是查询条件，你们酌情写自己的
) AS b ON c.datelist = DATE(b.create_time)  -- 两个表的关联，因为我表中crate_time是年月日时分秒的，所以要格式化后相等
WHERE
    c.datelist LIKE '2019-10%'       -- 这个是查询某月的日期统计，在代码中这块应该是一个参数。我这里查询的2019年10月份的统计
GROUP BY
    datelist  -- 这个分组按照日期分组，再次声明这个是天的，如果月的这里会不同
ORDER BY
    datelist   -- 这个排序是因为之前一次测试有count的在上面，没有的在下面，日期没有顺序，所以我又升序排了下。

3、按月份统计

SELECT
    DATE_FORMAT(c.datelist, '%Y-%m') as date, -- 查询的是年-月，所以要格式处理
    COUNT(b.id) as count
FROM
    calendar AS c
LEFT JOIN (
    SELECT
        *
    FROM
        driver -- 真正想统计的表名
    WHERE
        lnvalid = 2 -- 这个是查询条件，你们酌情写自己的
    AND flag = 0 -- 这个是查询条件，你们酌情写自己的
) AS b ON c.datelist = DATE(b.create_time)  -- 两个表的关联，因为我表中crate_time是年月日时分秒的，所以要格式化后相等
WHERE
    c.datelist LIKE '2019%'      -- 这个是查询某月的日期统计，在代码中这块应该是一个参数。我这里查询的2019年的统计
GROUP BY
    DATE_FORMAT(c.datelist, '%Y-%m')-- 这个分组按照月份分组的，因为datelist是年月日，所以要格式化处理成年月
ORDER BY
    datelist   -- 这个排序是因为之前一次测试有count的在上面，没有的在下面，日期没有顺序，所以我又升序排了下。

4、按照年统计

SELECT
    DATE_FORMAT(c.datelist, '%Y') as date, -- 查询的是年，所以要格式处理
    COUNT(b.id) as count
FROM
    calendar AS c
LEFT JOIN (
    SELECT
        *
    FROM
        base_driver -- 真正想统计的表名
    WHERE
        if_lnvalid = 2 -- 这个是查询条件，你们酌情写自己的
    AND flag = 0 -- 这个是查询条件，你们酌情写自己的
) AS b ON c.datelist = DATE(b.create_time)  -- 两个表的关联，因为我表中crate_time是年月日时分秒的，所以要格式化后相等
GROUP BY
    DATE_FORMAT(c.datelist, '%Y')-- 这个分组按照月份分组的，因为datelist是年月日，所以要格式化处理成年
ORDER BY
    datelist   -- 这个排序是因为之前一次测试有count的在上面，没有的在下面，日期没有顺序，所以我又升序排了下。