首页 > 试题广场 >

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

[编程题]计算用户8月每天的练题数量

热度指数：211607 时间限制：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

忆_恒心_YD

两种方法求解，第一种的效率更高点

SELECT DAY(date) AS day, COUNT(question_id) AS question_cnt
FROM question_practice_detail
WHERE YEAR(date) = 2021 AND MONTH(date) = 8
# WHERE DATE(date) BETWEEN '2021-08-01 'AND '2021-08-31'
GROUP by date;

发表于 2021-12-01 12:37:14 回复(7)

更多回答

Mysql

美安克华

--法一：like运算符
select
    day(date) as day,
    count(question_id) as question_cnt
from question_practice_detail
where date like '2021-08%'
group by day(date);

--法二：regexp运算符
select
    day(date) as day,
    count(question_id) as question_cnt
from question_practice_detail
where date regexp '2021-08'
group by day(date);

--法三：substring提取日期
select
    day(date) as day,
    count(question_id) as question_cnt
from question_practice_detail
where substring(date,1,7) = '2021-08'
group by day(date);

发表于 2022-02-28 18:33:42 回复(16)

Mysql

其实是牛哥

看了下官方讲解才知道的：https://www.nowcoder.com/knowledge/intro-index?kcid=20

代码：

Select day(date) as day, count(question_id) as question_cnt
From question_practice_detail
Where year(date)=2021 and month(date)=08
Group by day

发表于 2021-08-25 10:49:32 回复(10)

Mysql

牛牛N号

select
DAY(date) as day,
count(question_id) as question_cnt
from question_practice_detail
where 
SUBSTR(date,1,7) = '2021-08'
group by day

运用substr也可以

发表于 2021-08-26 10:22:49 回复(2)

牛客286571841号

select
day(date)as day,
count(question_id)
from question_practice_detail
where date like '2021-08-%'
group by day

发表于 2021-10-10 14:40:53 回复(6)

Mysql

幸运的小猫拒绝内卷

SELECT
date
,count(device_id)
from question_practice_detail
group by date

having date between '2021-08-01' and '2021-08-31'

我想问下大佬们，为啥having就不行？

发表于 2021-10-01 19:31:43 回复(20)

Mysql

无花果子

用 EXTRACT 是可以的

SELECT EXTRACT(DAY from date) day, COUNT(question_id) question_cnt
FROM question_practice_detail
WHERE EXTRACT(MONTH from date) = 08
GROUP BY day

发表于 2021-09-01 14:34:59 回复(2)

Mysql

正儿八经欸

SELECT RIGHT(date,2) as day, COUNT(device_id) as question_cnt
FROM question_practice_detail
WHERE left(date,4) = "2021" and
(RIGHT((LEFT(date,7)),2)="08")

GROUP by day

左抽抽~~~右抽抽~~~

发表于 2021-11-24 09:05:48 回复(4)

Mysql

牛客541680488号

select
    SUBSTR(date, -2) day,
    COUNT(*) question_cnt
FROM
    question_practice_detail
WHERE 
    date between '2021-08-01'and '2021-08-31'
GROUP BY day

发表于 2021-11-12 20:09:21 回复(1)

Mysql

冲冲冲chong

select day(date),count(question_id)
from question_practice_detail
where date like "2021-08%"
group by date

发表于 2022-09-14 17:28:13 回复(0)

Mysql

风雨尘

select DATE_FORMAT(`date`,'%d') as day,COUNT(id) as question_cnt
FROM question_practice_detail 
where DATE_FORMAT(`date`,'%Y-%m') = '2021-08'
group BY day

不能做排序，加了测试报错！！

发表于 2022-02-13 15:35:54 回复(0)

Mysql

台灯亮了（知乎同名）

select day,
       count(question_id) as question_cnt 
from (
    select (case when month(date)=8 then day(date) end) as day,
            question_id
    from question_practice_detail
    ) as a 
where day is not null 
group by day;

发表于 2021-11-18 10:06:40 回复(1)

被折磨

select day(date) day, count(*) as question_cnt from question_practice_detail
group by date

having month(date)=8

一种很简单的方法：先算每天的用户练习题目量count(*)和group by (date)；再筛选出8月的month(date)=8；最后把要输出的日期换成day的格式day(date) day

发表于 2022-08-16 16:20:06 回复(1)

Mysql

冷凡社长

select day(date) as day,count(*) as question_cnt
from question_practice_detail
where year(date)=2021 and month(date)=8
group by day(date)

没有用其他的函数，只用了日期相关的函数

发表于 2022-07-14 21:11:57 回复(1)

Mysql

山药小牛

因为日期格式不一定是统一的，最稳妥的方法还是分别用day、month、year函数

select day(date) day, count(device_id) question_cnt
from question_practice_detail
where month(date)=8
and year(date)=2021
group by day(date)

从墨客e大佬那儿学了个regexp运算符的用法

select
    day(date) as day,
    count(question_id) as question_cnt
from question_practice_detail
where date regexp '2021-08'
group by day(date);

发表于 2022-07-24 14:06:39 回复(0)

Mysql

Stzolo

select day(date) as day, count(*) as  question_cnt
from question_practice_detail
where month(date)='08' and year(date)='2021'
group by day(date)

发表于 2022-04-24 14:15:05 回复(1)

Mysql

238狗嘉豪

select day(date) AS day,count(*) as question_cnt
from question_practice_detail
where MONTH(date)=8 and YEAR(date)=2021
group by day;

发表于 2022-04-10 10:15:34 回复(1)

Mysql

大唐小码农

select date_format(date, '%d') as "day",
count(question_id) as "question_cnt"
from question_practice_detail
where date_format(date, '%Y-%m') = '2021-08'
group by day

发表于 2022-04-06 23:21:28 回复(2)

Mysql

恶作剧之神阿蒙

select day(date) as day,count(*)
from question_practice_detail
where month(date)='08' and year(date)=2021

group by day

注意是2021-08的数据

也不要去排序

没注意审题，卡了好久

发表于 2021-08-25 11:01:34 回复(3)

viod

select 
    day(date) day,
    count(result) question_cnt
from
    question_practice_detail
where
    year(date)="2021"
    and month(date)="08"
group by 
    day

编辑于 2024-02-27 14:09:14 回复(0)