- 之前在网上搜索按时间统计，发现不是很全 ，接着别人的思路进行延伸下，
- 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 ('', '小南', '男', '湖南湘潭', '2019-07-01');
INSERT INTO `user` VALUES ('', '小谭', '男', '湖南湘潭', '2019-07-02');
INSERT INTO `user` VALUES ('', '小梅', '女', '广东梅县', '2019-07-01');
INSERT INTO `user` VALUES ('', '小仪', '女', '四川仪陇', '2019-05-10');
INSERT INTO `user` VALUES ('', '小蓝', '女', '上海嘉定', '2019-11-11');
INSERT INTO `user` VALUES ('', '小苍', '男', '湖南湘潭', '2018-06-01');
INSERT INTO `user` VALUES ('', '小萨', '男', '湖南湘潭', '2018-01-02');
INSERT INTO `user` VALUES ('', '小静', '女', '广东梅县', '2018-06-01');
INSERT INTO `user` VALUES ('', '大幂幂', '女', '四川仪陇', '2019-03-10');
INSERT INTO `user` VALUES ('', '宋小宝', '女', '上海嘉定', '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;
-
按周统计
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;
-
按月统计
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;
-
按年统计（近五年）
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