首页 > 试题广场 >

计算用户8月每天的练题数量

[编程题]计算用户8月每天的练题数量
  • 热度指数：213882 时间限制：C/C++ 1秒，其他语言2秒 空间限制：C/C++ 256M，其他语言512M
  • 算法知识视频讲解
题目：现在运营想要计算出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
算法知识视频讲解
添加回答
Mysql
凉凉201908162008308头像 凉凉201908162008308
select day(date) as day,count(question_id) as question_cnt
from question_practice_detail
where year(date)=2021 and month(date)=8
group by day
发表于 2024-05-08 11:27:52 回复(0)
Mysql
Raines_Xu头像 Raines_Xu 
select day(date) as day,
count(question_id) as question_cnt
from question_practice_detail
where date like'2021-08-%'
group by day;

发表于 2024-04-27 19:36:15 回复(0)
Mysql
略略略😜201907170935306头像 略略略😜201907170935306
select
    day(date) as day,
    count(question_id)
from
    question_practice_detail
where
    year(date) = 2021 and month(date) = 08
group by date

编辑于 2024-04-09 19:55:00 回复(0)
Mysql
驾校人头像 驾校人
day 不是关键字吗，为什么可以做列名 
select
    day(date) as day, count(question_id) as question_cnt
from
    question_practice_detail
where year(date)=2021 and month(date)=8
group by day


发表于 2024-02-27 12:14:34 回复(0)
Mysql
牛客804786355号头像 牛客804786355号
select extract(day from date) as day,count(*) as question_cnt
from question_practice_detail
where extract(month from date) = 8
group by extract(day from date);
编辑于 2024-02-17 14:55:26 回复(0)
Mysql
小谷围做题家头像 小谷围做题家 
select substr(date,9,2) day,count(id) question_cnt from question_practice_detail
where date like '2021-08%'
group by date;

发表于 2024-02-04 23:18:19 回复(0)
Mysql
盐焗小虾球头像 盐焗小虾球
我记得group by的执行顺序在select之前呀，为什么不用派生表呀，请教一下大佬们 
select 
    day, 
    count(question_id) question_cnt
from (
    select 
        date, 
        day(date) day, 
        question_id 
    from question_practice_detail) q2
where month(date) = 8 and year(date) = 2021
group by day


编辑于 2024-01-02 16:57:31 回复(0)
Mysql
牛客43484925号头像 牛客43484925号
1. 
select day(date) as day,count(*) as  question_cut from question_practice_detail where date between "2021-08-01" and "2021-08-31" group by day;
2.
select day(date) as day,count(*) as question_cut from question_practice_detail where date like "2021-08-%" group by day;

发表于 2023-12-18 15:31:09 回复(0)
Mysql
牛客554543829号头像 牛客554543829号 
select
    day(date) as day,
    count(question_id) as question_cnt
from question_practice_detail
where date regexp '2021-08'
group by day(date);
select
   EXTRACT(DAY from date) as day,
   COUNT(question_id) as question_cnt
from question_practice_detail
where EXTRACT(MONTH from date) = 08
group By day

发表于 2023-11-30 21:05:18 回复(0)
Mysql
vivian_ss头像 vivian_ss
select day(date),count(question_id) as question_cnt from question_practice_detail where month(date)=8 group by day(date)
发表于 2023-11-24 10:59:15 回复(0)
Mysql
阳光美少年头像 阳光美少年
不是，你们都不考虑执行顺序问题吗？
发表于 2023-10-14 11:01:57 回复(0)
Mysql
爱刷美剧的大学生正在背八股头像 爱刷美剧的大学生正在背八股
SELECT
    DAY (date) AS day,
    COUNT(*) AS question_cnt
FROM
    question_practice_detail
WHERE
    date >= '2021-08-01'
    AND date < '2021-09-01'
GROUP BY
    day;
发表于 2023-09-20 11:12:52 回复(0)
Mysql
祖安文科状元路过头像 祖安文科状元路过 
select day(date) as day ,count(1) as result from question_practice_detail 
where year(date)='2021' and month(date)='08' group by day
发表于 2023-09-14 18:45:35 回复(0)
Mysql
litohong头像 litohong
原来有日期函数hhhhh，我是截取出来的 
select SUBSTRING_INDEX(date, '-', -1) as day, count(*) as question_cnt
from question_practice_detail
where date like "2021-08%"
group by date;


发表于 2023-08-20 16:01:50 回复(0)
Mysql
小熊是小熊头像 小熊是小熊 
select
day(date) as day,   ----利用day函数将date里的日期转化为以日为单位的数字
count(question_id) as question_cnt
from
question_practice_detail
where
substr(date,1,7) = '2021-08'     ---使用substr函数将date里的字符从1到7拆分
group by day(date);

发表于 2023-08-17 14:52:08 回复(0)
Mysql
是真的帅~啊头像 是真的帅~啊
# 时间日期处理函数
# select day(date) as day ,
# count(question_id) question_cnt
# from question_practice_detail
# where year(date)='2021' and month(date)='08'
# group by day

# # 模糊运算符 like
# select day(date) as day ,
# count(question_id) as question_cnt
# from question_practice_detail
# where date like '2021-08%'
# group by day

# 正则运算符 regexp  类似like 效率比like高
# select day(date) day,
# count(question_id) question_cnt
# from question_practice_detail
# where date regexp '2021-08'
# group by day

# 使用字符串切割 substring
# select substring(date,9,10) as day ,
# count(question_id) question_cnt
# from question_practice_detail
# where substring(date,1,7) = '2021-08'
# group by day
发表于 2023-08-17 12:42:17 回复(0)
Mysql
牛客697066099号头像 牛客697066099号
select day(date) as day,count(question_id) as question_cnt
from question_practice_detail
where month(date)=8
group by day(date);
发表于 2023-08-13 18:29:59 回复(0)
Mysql
杜奆奆头像 杜奆奆 
select
    day (date) as day,
    count(date)
from
    question_practice_detail
where
    date like '2021-08-%'
group by
    day
对于常见的日期格式，类似’2021-08-01’，有时候在聚合计算时我们会想将日期中的
年、月、日分别提取出来，这时应该怎么做呢？SQL为此提供了对应的年、月、日提取
函数，分别为year(),month(),day()。
select
year('2021-08-01'),month('2021-08-01'),day('2021-08-01')

发表于 2023-06-20 23:07:53 回复(0)
提交观点

问题信息

难度：
258条回答 2653浏览

热门推荐

通过挑战的用户

查看代码

相关试题

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号