mysql对当前年份按月统计sql

最新推荐文章于 2022-01-29 21:19:01 发布

原创最新推荐文章于 2022-01-29 21:19:01 发布 · 705 阅读

0 ·

CC 4.0 BY-SA版权

数据库专栏收录该内容

4 篇文章

订阅专栏

本文详细解析了如何使用SQL语句进行精确的日期筛选及每月数据统计，通过具体实例展示了如何利用date_format函数和CASE WHEN语句来实现对特定时间段内的数据进行高效统计。

-- SELECT DATE_FORMAT(NOW(), '%Y') FROM ts_contact_ask where createtime > '2019-01-01 00:00:00' AND createtime < '2019-12-31 23:59:59';
SELECT
SUM(CASE WHEN date_format(createtime,'%m%d')>=101 and date_format(createtime,'%m%d')<=131 then 1 else 0 end) as '1'
,SUM(CASE WHEN date_format(createtime,'%m%d')>=201 and date_format(createtime,'%m%d')<=229 then 1 else 0 end) as '2'
,SUM(CASE WHEN date_format(createtime,'%m%d')>=301 and date_format(createtime,'%m%d')<=331 then 1 else 0 end) as '3'
,SUM(CASE WHEN date_format(createtime,'%m%d')>=401 and date_format(createtime,'%m%d')<=430 then 1 else 0 end) as '4'
,SUM(CASE WHEN date_format(createtime,'%m%d')>=501 and date_format(createtime,'%m%d')<=531 then 1 else 0 end) as '5'
,SUM(CASE WHEN date_format(createtime,'%m%d')>=601 and date_format(createtime,'%m%d')<=630 then 1 else 0 end) as '6'
,SUM(CASE WHEN date_format(createtime,'%m%d')>=701 and date_format(createtime,'%m%d')<=731 then 1 else 0 end) as '7'
,SUM(CASE WHEN date_format(createtime,'%m%d')>=801 and date_format(createtime,'%m%d')<=831 then 1 else 0 end) as '8'
,SUM(CASE WHEN date_format(createtime,'%m%d')>=901 and date_format(createtime,'%m%d')<=930 then 1 else 0 end) as '9'
,SUM(CASE WHEN date_format(createtime,'%m%d')>=1001 and date_format(createtime,'%m%d')<=1031 then 1 else 0 end) as '10'
,SUM(CASE WHEN date_format(createtime,'%m%d')>=1101 and date_format(createtime,'%m%d')<=1130 then 1 else 0 end) as '11'
,SUM(CASE WHEN date_format(createtime,'%m%d')>=1201 and date_format(createtime,'%m%d')<=1231 then 1 else 0 end) as '12'
FROM ts_contact_ask
where createtime > '2019-01-01 00:00:00' AND createtime < '2019-12-31 23:59:59';