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

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

1.sql 语句

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

3.一年的月均量

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;  

5.第二种方法

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';

6.

最后

以上就是过时黑夜为你收集整理的MySQL 查询一个表的 一年中每个月的数据的均数量的全部内容，希望文章能够帮你解决MySQL 查询一个表的 一年中每个月的数据的均数量所遇到的程序开发问题。

如果觉得靠谱客网站的内容还不错，欢迎将靠谱客网站推荐给程序员好友。