mysql统计天、周、月、季度、半年、年

之前在网上搜索按时间统计，发现不是很全，接着别人的思路进行延伸下，
mysql统计天、周、月、季度、半年、年

前期工作创建辅助表

CREATE TABLE num (i INT);
INSERT INTO num (i) VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);

创建要查询的表

CREATE TABLE `user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(32) NOT NULL COMMENT '用户名称',
  `sex` char(1) DEFAULT NULL COMMENT '性别',
  `address` varchar(256) DEFAULT NULL COMMENT '地址',
  `create_time` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of user
-- ----------------------------
INSERT INTO `user` VALUES ('1', '小南', '男', '湖南湘潭', '2019-07-01');
INSERT INTO `user` VALUES ('2', '小谭', '男', '湖南湘潭', '2019-07-02');
INSERT INTO `user` VALUES ('3', '小梅', '女', '广东梅县', '2019-07-01');
INSERT INTO `user` VALUES ('4', '小仪', '女', '四川仪陇', '2019-05-10');
INSERT INTO `user` VALUES ('5', '小蓝', '女', '上海嘉定', '2019-11-11');
INSERT INTO `user` VALUES ('6', '小苍', '男', '湖南湘潭', '2018-06-01');
INSERT INTO `user` VALUES ('7', '小萨', '男', '湖南湘潭', '2018-01-02');
INSERT INTO `user` VALUES ('8', '小静', '女', '广东梅县', '2018-06-01');
INSERT INTO `user` VALUES ('9', '大幂幂', '女', '四川仪陇', '2019-03-10');
INSERT INTO `user` VALUES ('10', '宋小宝', '女', '上海嘉定', '2019-05-11');

按天统计

SELECT
    temp.date,
    COALESCE (u.unmber, 0) 'number'
FROM
    (
        SELECT
            adddate('2019-07-01', numlist.id) AS 'date'
        FROM
            (
                SELECT
                    n1.i + n10.i * 10 + n100.i * 100 AS id
                FROM
                    num n1
                CROSS JOIN num AS n10
                CROSS JOIN num AS n100
            ) AS numlist
        WHERE
            adddate('2019-07-01', numlist.id) < date_add('2019-07-05',INTERVAL 1 DAY)
    ) temp
LEFT JOIN (
    SELECT
        LEFT (create_time, 10) AS udate,
        count(create_time) unmber
    FROM
        user
    WHERE
        1=1
    GROUP BY
        udate
) u ON temp.date = u.udate
ORDER BY
    temp.date;

mysql统计天、周、月、季度、半年、年

按周统计

SELECT
    temp.monthWeek,
    COALESCE (plan.number, 0) 'number'
FROM
    (
        SELECT
            YEARWEEK(
                adddate('2019-07-01',    INTERVAL numlist.id WEEK),1    ) AS 'date',
            CONCAT(
                MONTH (date_format('2019-07-01', '%Y-%m-%d')),'月第',    numlist.id + 1,'周') AS 'monthWeek'
        FROM
            (
                SELECT
                    *
                FROM
                    (
                        SELECT
                            n1.i + n10.i * 10 AS id
                        FROM
                            num n1
                        CROSS JOIN num AS n10
                    ) a
                WHERE
                    a.id <= 11
            ) AS numlist
        WHERE
            adddate('2019-07-01',INTERVAL numlist.id WEEK    ) <= '2019-07-31'
    ) temp
LEFT JOIN (
    SELECT
        YEARWEEK(    date_format(create_time, '%Y-%m-%d'),    1) date,
        count(    YEARWEEK(date_format(create_time, '%Y-%m-%d'),1    )    ) number
    FROM
        USER
    WHERE
        1 = 1
    AND create_time >= '2019-07-01'
    AND create_time <= '2019-07-31'
    GROUP BY
        YEARWEEK(
            date_format(create_time, '%Y-%m-%d'),
            1
        )
) plan ON temp.date = plan.date;

mysql统计天、周、月、季度、半年、年

按月统计

SELECT
    LEFT (temp.date, 7) monthStr,
    COALESCE (u.unmber, 0) 'number'
FROM
    (
        SELECT
            adddate('2019-01-01',INTERVAL numlist.id MONTH    ) AS 'date'
        FROM
            (
                SELECT
                    *
                FROM
                    (
                        SELECT
                            n1.i + n10.i * 10 AS id
                        FROM
                            num n1
                        CROSS JOIN num AS n10
                    ) a
                WHERE
                    a.id <= 11
            ) AS numlist
        WHERE
            adddate('2019-01-01',INTERVAL numlist.id MONTH) <= '2019-12-3'
    ) temp
LEFT JOIN (
    SELECT
        LEFT (create_time, 7) AS udate,
        count(create_time) unmber
    FROM
        user
    WHERE
        1=1
    GROUP BY
        udate
) u ON LEFT (temp.date, 7) = u.udate
ORDER BY
    temp.date

按季度统计

SELECT
    temp.monthWeek,
    COALESCE(plan.number, 0) 'number'
FROM
    (
    SELECT
    QUARTER(adddate('2019-01-01',    INTERVAL numlist.id QUARTER )) AS 'date',
        CONCAT(year(date_format('2019-01-01','%Y-%m-%d')), '年第', numlist.id +1, '季度') AS 'monthWeek'
    FROM
        (
            SELECT
                *
            FROM
                (
                    SELECT
                        n1.i + n10.i * 10 AS id
                    FROM
                        num n1
                    CROSS JOIN num AS n10
                ) a
            WHERE
                a.id <= 11
        ) AS numlist
    WHERE
        adddate('2019-01-01',INTERVAL numlist.id QUARTER) <= '2019-12-31'
) temp
LEFT JOIN (
   SELECT QUARTER(date_format(create_time,'%Y-%m-%d')) date,
        count(QUARTER(date_format(create_time,'%Y-%m-%d'))) number 
FROM user
        where 1=1 
        and create_time >= '2019-01-01' 
        and create_time <= '2019-12-31'
        group by QUARTER(date_format(create_time,'%Y-%m-%d'))
) plan
on temp.date = plan.date

按半年统计

SELECT
        bannian 'content',
        COALESCE (u.unmber, 0) 'number'
        FROM
        (
            SELECT
            adddate( '2018-01-01',INTERVAL numlist.id MONTH) AS 'date',
            case DATE_FORMAT(adddate( '2018-01-01',INTERVAL numlist.id MONTH),'%c')
            when 7 then '下半年'
            when 8 then '下半年'
            when 9 then '下半年'
            when 10 then '下半年'
            when 11 then '下半年'
            when 12 then '下半年'
            else '上半年' end
            as bannian
            FROM
            (
                SELECT * FROM(
                    SELECT
                    n1.i + n10.i * 10 AS id
                    FROM
                    num n1
                    CROSS JOIN num AS n10
                ) a
                WHERE a.id <= 11
            ) AS numlist
            WHERE
            adddate('2018-01-01',INTERVAL numlist.id MONTH) <=  '2018-12-31'
            GROUP BY bannian
        ) temp
        LEFT JOIN (
                SELECT
                    case DATE_FORMAT(create_time,'%c')
                    when 7 then '下半年'
                    when 8 then '下半年'
                    when 9 then '下半年'
                    when 10 then '下半年'
                    when 11 then '下半年'
                    when 12 then '下半年'
                    else '上半年' end as udate,
                    count(create_time) unmber
                FROM  user
                    WHERE 1=1
                    and date_format(create_time,'%Y-%m-%d') >='2018-01-01'
                    and date_format(create_time,'%Y-%m-%d') <= '2018-12-31'
                GROUP BY udate
        ) u ON bannian = u.udate

ORDER BY bannian asc;

mysql统计天、周、月、季度、半年、年

按年统计（近五年）

SELECT
    COALESCE(plan.number, 0) 'number',
    temp.date fiveYear
FROM
    (
    SELECT
    Year(adddate('2015-01-01',    INTERVAL numlist.id Year )) AS 'date'
    FROM
        (
            SELECT
                *
            FROM
                (
                    SELECT
                        n1.i + n10.i * 10 AS id
                    FROM
                        num n1
                    CROSS JOIN num AS n10
                ) a
            WHERE
                a.id <= 11
        ) AS numlist
    WHERE
        adddate('2015-01-01',INTERVAL numlist.id Year) <= '2019-12-31'
) temp
LEFT JOIN (
   SELECT Year(date_format(create_time,'%Y-%m-%d')) date,
        count(Year(date_format(create_time,'%Y-%m-%d'))) number 
FROM user
        where 1=1
        group by year(date_format(create_time,'%Y-%m-%d'))
) plan
on temp.date = plan.date
order by temp.date asc

秒客网

mysql统计天、周、月、季度、半年、年

按天统计

按周统计

按月统计

按季度统计

按半年统计

按年统计（近五年）

相关文章