Mysql面试题

题目：给一个表person，五个字段：id，name, sex, tell, birth ，记录10000个人的姓名，性别，电话，出生日期，要求写sql语句查询出来：2020年每个月分别出生了多少人？

面试了几十个测试工程师，居然没有几个人能写出来？

CREATE TABLE `person` (

  `id` int NOT NULL AUTO_INCREMENT,

  `name` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,

  `sex` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,

  `tel` varchar(255) CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci DEFAULT NULL,

  `birth` datetime DEFAULT NULL,

  PRIMARY KEY (`id`) USING BTREE

) ENGINE=InnoDB AUTO_INCREMENT=1283 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci ROW_FORMAT=DYNAMIC;

sql脚本如下：

select

    year(birth) as 出生年份,

    month(birth) as 出生月份,

    count(1) as '月份出生人数汇总'

from person

where year(birth) = '2020'

group by

    year(birth),

    month(birth)

order by

    出生年份,

    出生月份

一次批量插入数据：

INSERT INTO demodb.person (name,sex,tel,birth)

VALUES('alice','F','18955555655','2018-2-14 20:30:34'),

      ('alice','F','18955555655','2018-2-17 20:30:34'),

      ('alice','F','18955555655','2018-2-14 20:30:34'),

      ('alice','F','18955555655','2018-2-14 20:30:34'),

      ('alice','F','18955555655','2018-2-17 20:30:34');