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

ゝ一纸荒年。 2022-05-27 09:44 210阅读 0赞

**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

**![Center][]**

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

**![20180416163405555][]**

**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.**

![Center 1][]

[Center]: /images/20220527/ea3ab52e5a21472e84591490ab63d4f6.png
[20180416163405555]: /images/20220527/181e9a08eeae4d64944e3f9b4e30a95a.png
[Center 1]: /images/20220527/70d4a25415a449749b4e813c191b70cc.png