MySQL 查询一个表的 一年中每个月的数据的均数量

1
2
3
4
5
6
7
SELECT t.myYear AS 年份,t.monthNo AS 月份,COUNT(1) AS 数量统计  
FROM(SELECT MONTH(a.`create_time`) AS monthNo,  
         YEAR(a.`create_time`) AS myYear,  
     a.`member_id` AS id  
 FROM BAOFOO_MA.`ma_member` a) AS t  
WHERE t.myYear='2017'  
GROUP BY t.monthNo

1
2
3
4
5
6
7
SELECT t1.myYear, avg(t1.countSUM) FROM (SELECT t.myYear, t.monthNo, COUNT(1) AS countSUM
FROM(SELECT MONTH(a.`create_time`) AS monthNo,  
         YEAR(a.`create_time`) AS myYear,  
     a.`member_id` AS id  
 FROM BAOFOO_MA.`ma_member` a) AS t  
WHERE t.myYear='2017'  
GROUP BY t.monthNo) AS t1;  

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT   
SUM(CASE MONTH(a.`create_time`) WHEN '1' THEN 1 ELSE 0 END) AS 一月份,  
SUM(CASE MONTH(a.`create_time`) WHEN '2' THEN 1 ELSE 0 END) AS 二月份,  
SUM(CASE MONTH(a.`create_time`)WHEN '3' THEN 1 ELSE 0 END) AS 三月份,  
SUM(CASE MONTH(a.`create_time`) WHEN '4' THEN 1 ELSE 0 END) AS 四月份,  
SUM(CASE MONTH(a.`create_time`) WHEN '5' THEN 1 ELSE 0 END) AS 五月份,  
SUM(CASE MONTH(a.`create_time`) WHEN '6' THEN 1 ELSE 0 END) AS 六月份,  
SUM(CASE MONTH(a.`create_time`) WHEN '7' THEN 1 ELSE 0 END) AS 七月份,  
SUM(CASE MONTH(a.`create_time`)WHEN '8' THEN 1 ELSE 0 END) AS 八月份,  
SUM(CASE MONTH(a.`create_time`) WHEN '9' THEN 1 ELSE 0 END) AS 九月份,  
SUM(CASE MONTH(a.`create_time`)WHEN '10' THEN 1 ELSE 0 END) AS 十月份,  
SUM(CASE MONTH(a.`create_time`)WHEN '11' THEN 1 ELSE 0 END) AS 十一月份,  
SUM(CASE MONTH(a.`create_time`) WHEN '12' THEN 1 ELSE 0 END) AS 十二月份, 
SUM(CASE MONTH(a.`create_time`) WHEN '1' THEN 1 ELSE 1 END) AS 总量,
SUM(CASE MONTH(a.`create_time`) WHEN '1' THEN 1 ELSE 1 END)/12 AS 月均量 
FROM BAOFOO_MA.`ma_member` a  
WHERE YEAR(a.`create_time`)='2017';