描述
题目：现在运营想要计算出2021年8月每天用户练习题目的数量，请取出相应数据。
示例：question_practice_detail
| id | device_id | question_id | result | date |
|---|---|---|---|---|
| 1 | 2138 | 111 | wrong | 2021-05-03 |
| 2 | 3214 | 112 | wrong | 2021-05-09 |
| 3 | 3214 | 113 | wrong | 2021-06-15 |
| 4 | 6543 | 111 | right | 2021-08-13 |
| 5 | 2315 | 115 | right | 2021-08-13 |
| 6 | 2315 | 116 | right | 2021-08-14 |
| 7 | 2315 | 117 | wrong | 2021-08-15 |
| …… |
根据示例，你的查询应返回以下结果：
| day | question_cnt |
|---|---|
| 13 | 5 |
| 14 | 2 |
| 15 | 3 |
| 16 | 1 |
| 18 | 1 |
示例1
输入：
drop table if exists `user_profile`;
drop table if exists `question_practice_detail`;
drop table if exists `question_detail`;
CREATE TABLE `user_profile` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`gender` varchar(14) NOT NULL,
`age` int ,
`university` varchar(32) NOT NULL,
`gpa` float,
`active_days_within_30` int ,
`question_cnt` int ,
`answer_cnt` int
);
CREATE TABLE `question_practice_detail` (
`id` int NOT NULL,
`device_id` int NOT NULL,
`question_id`int NOT NULL,
`result` varchar(32) NOT NULL,
`date` date NOT NULL
);
CREATE TABLE `question_detail` (
`id` int NOT NULL,
`question_id`int NOT NULL,
`difficult_level` varchar(32) NOT NULL
);
INSERT INTO user_profile VALUES(1,2138,'male',21,'北京大学',3.4,7,2,12);
INSERT INTO user_profile VALUES(2,3214,'male',null,'复旦大学',4.0,15,5,25);
INSERT INTO user_profile VALUES(3,6543,'female',20,'北京大学',3.2,12,3,30);
INSERT INTO user_profile VALUES(4,2315,'female',23,'浙江大学',3.6,5,1,2);
INSERT INTO user_profile VALUES(5,5432,'male',25,'山东大学',3.8,20,15,70);
INSERT INTO user_profile VALUES(6,2131,'male',28,'山东大学',3.3,15,7,13);
INSERT INTO user_profile VALUES(7,4321,'male',28,'复旦大学',3.6,9,6,52);
INSERT INTO question_practice_detail VALUES(1,2138,111,'wrong','2021-05-03');
INSERT INTO question_practice_detail VALUES(2,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(3,3214,113,'wrong','2021-06-15');
INSERT INTO question_practice_detail VALUES(4,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(5,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(6,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(7,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(8,3214,112,'wrong','2021-05-09');
INSERT INTO question_practice_detail VALUES(9,3214,113,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(10,6543,111,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(11,2315,115,'right','2021-08-13');
INSERT INTO question_practice_detail VALUES(12,2315,116,'right','2021-08-14');
INSERT INTO question_practice_detail VALUES(13,2315,117,'wrong','2021-08-15');
INSERT INTO question_practice_detail VALUES(14,3214,112,'wrong','2021-08-16');
INSERT INTO question_practice_detail VALUES(15,3214,113,'wrong','2021-08-18');
INSERT INTO question_practice_detail VALUES(16,6543,111,'right','2021-08-13');
INSERT INTO question_detail VALUES(1,111,'hard');
INSERT INTO question_detail VALUES(2,112,'medium');
INSERT INTO question_detail VALUES(3,113,'easy');
INSERT INTO question_detail VALUES(4,115,'easy');
INSERT INTO question_detail VALUES(5,116,'medium');
INSERT INTO question_detail VALUES(6,117,'easy');
输出：
13|5
14|2
15|3
16|1
18|1
解题
select
day(date) as day,
count(question_id) as question_cnt
from question_practice_detail
where month(date) = 8 and year(date) = 2021
group by date;
题解
日期函数
DAYOFWEEK(date)
返回日期date的星期索引(1=星期天，2=星期一, ……7=星期六)。这些索引值对应于ODBC标准。
mysql> select DAYOFWEEK('1998-02-03')
WEEKDAY(date)
返回date的星期索引(0=星期一，1=星期二, ……6= 星期天)。
mysql> select WEEKDAY('1997-10-04 22:23:00');
DAYOFMONTH(date)
返回date的月份中日期，在1到31范围内。
mysql> select DAYOFMONTH('1998-02-03');
DAYOFYEAR(date)
返回date在一年中的日数, 在1到366范围内。
mysql> select DAYOFYEAR('1998-02-03');
MONTH(date)
返回date的月份，范围1到12。
mysql> select MONTH('1998-02-03');
DAYNAME(date)
返回date的星期名字。
mysql> select DAYNAME("1998-02-05");
MONTHNAME(date)
返回date的月份名字。
mysql> select MONTHNAME("1998-02-05");
QUARTER(date)
返回date一年中的季度，范围1到4。
mysql> select QUARTER('98-04-01');
WEEK(date)
对于星期天是一周的第一天的地方，有一个单个参数，返回date的周数，范围在0到52。
mysql> select WEEK('1998-02-20');
WEEK(date,first)
2个参数形式WEEK()允许你指定星期是否开始于星期天或星期一。
如果第二个参数是0，星期从星期天开始，
如果第二个参数是1，从星期一开始。
mysql> select WEEK('1998-02-20',0);
mysql> select WEEK('1998-02-20',1);
YEAR(date)
返回date的年份，范围在1000到9999。
mysql> select YEAR('98-02-03');
HOUR(time)
返回time的小时，范围是0到23。
mysql> select HOUR('10:05:03');
MINUTE(time)
返回time的分钟，范围是0到59。
mysql> select MINUTE('98-02-03 10:05:03');
SECOND(time)
回来time的秒数，范围是0到59。
mysql> select SECOND('10:05:03');
PERIOD_ADD(P,N)
增加N个月到阶段P(以格式YYMM或YYYYMM)。以格式YYYYMM返回值。注意阶段参数P不是日期值。
mysql> select PERIOD_ADD(9801,2);
PERIOD_DIFF(P1,P2)
返回在时期P1和P2之间月数，P1和P2应该以格式YYMM或YYYYMM。注意，时期参数P1和P2不是日期值。
mysql> select PERIOD_DIFF(9802,199703);
