MySQL统计过去12个月的数据没有则为0

雨雾cc

于 2015-12-18 09:25:32 发布

阅读量1.1w

点赞数 4

分类专栏：统计报表文章标签： mysql 报表数据 mysql统计每月

本文链接：https://blog.csdn.net/dsfyyh/article/details/50349519

版权

统计报表专栏收录该内容

2 篇文章 0 订阅

订阅专栏

查询每月新增用户数

celsp_users是用户表

createTime 为新增用户时间

SELECT a.count,b.time2 from
(select count(c.createTime) as count,FROM_UNIXTIME( UNIX_TIMESTAMP(c.createTime),'%m' ) as mon
from celsp_users c where FROM_UNIXTIME( UNIX_TIMESTAMP(c.createTime),'%Y' ) = '2015' GROUP BY mon) a
RIGHT JOIN (SELECT '01' as time2 from DUAL UNION ALL
SELECT '02' as time2 from DUAL UNION ALL
SELECT '03' as time2 from DUAL UNION ALL
SELECT '04' as time2 from DUAL UNION ALL
SELECT '05' as time2 from DUAL UNION ALL
SELECT '06' as time2 from DUAL UNION ALL
SELECT '07' as time2 from DUAL UNION ALL
SELECT '08' as time2 from DUAL UNION ALL
SELECT '09' as time2 from DUAL UNION ALL
SELECT '10' as time2 from DUAL UNION ALL
SELECT '11' as time2 from DUAL UNION ALL
SELECT '12' as time2 from DUAL )b on a.mon = b.time2

结果集