MySQL - 统计每个月生日的人数

Person表定义如下：

create table person(id int primary key auto_increment, birthday datetime);

 

Person 数据如下：

MariaDB [test]> select * from person;
+----+---------------------+
| id | birthday            |
+----+---------------------+
|  1 | 2019-04-15 15:00:00 |
|  2 | 2019-04-16 15:00:00 |
|  3 | 2019-04-17 15:00:00 |
|  4 | 2019-04-18 15:00:00 |
|  5 | 2019-03-18 15:00:00 |
|  6 | 2019-03-17 15:00:00 |
|  7 | 2018-02-17 15:00:00 |
|  8 | 2019-02-16 15:00:00 |

 

按月统计每月生日人数：

MariaDB [test]> select DATE_FORMAT(birthday, "%m") as month, count(1) as num from person group by DATE_FORMAT(birthday, "%m");
+-------+-----+
| month | num |
+-------+-----+
| 02 | 2 |
| 03 | 2 |
| 04 | 4 |
+-------+-----+
3 rows in set (0.00 sec)