Mysql查询指定年每个月的数据统计

最新推荐文章于 2024-08-02 04:12:59 发布

点滴1993

最新推荐文章于 2024-08-02 04:12:59 发布

阅读量9.3k

点赞数 4

分类专栏： mysql

本文链接：https://blog.csdn.net/qq_38765404/article/details/81010762

版权

mysql 专栏收录该内容

15 篇文章 0 订阅

订阅专栏

Mysql查询指定年每个月的数据统计，之前写的分组sql，如果当月无数据的话，默认不显示，距离项目需求有点不足哈...

SELECT
	sum( CASE MONTH ( a.register_time ) WHEN '1' THEN 1 ELSE 0 END ) AS january,
	sum( CASE MONTH ( a.register_time ) WHEN '2' THEN 1 ELSE 0 END ) AS february,
	sum( CASE MONTH ( a.register_time ) WHEN '3' THEN 1 ELSE 0 END ) AS march,
	sum( CASE MONTH ( a.register_time ) WHEN '4' THEN 1 ELSE 0 END ) AS april,
	sum( CASE MONTH ( a.register_time ) WHEN '5' THEN 1 ELSE 0 END ) AS may,
	sum( CASE MONTH ( a.register_time ) WHEN '6' THEN 1 ELSE 0 END ) AS june,
	sum( CASE MONTH ( a.register_time ) WHEN '7' THEN 1 ELSE 0 END ) AS july,
	sum( CASE MONTH ( a.register_time ) WHEN '8' THEN 1 ELSE 0 END ) AS august,
	sum( CASE MONTH ( a.register_time ) WHEN '9' THEN 1 ELSE 0 END ) AS september,
	sum( CASE MONTH ( a.register_time ) WHEN '10' THEN 1 ELSE 0 END ) AS october,
	sum( CASE MONTH ( a.register_time ) WHEN '11' THEN 1 ELSE 0 END ) AS november,
	sum( CASE MONTH ( a.register_time ) WHEN '12' THEN 1 ELSE 0 END ) AS december 
FROM
	associator  a
WHERE
	YEAR ( a.register_time ) = 2018;