首页 > 试题广场 >

21年8月份练题总数

[编程题]21年8月份练题总数
  • 热度指数：159062 时间限制：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
……




根据的示例，你的查询应返回以下结果：
did_cnt question_cnt
3 12
示例1

输入

drop table if exists `user_profile`;
drop table if  exists `question_practice_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
);

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');

输出

3|12
算法知识视频讲解
添加回答
Mysql
忆_恒心_YD头像 忆_恒心_YD
...  就这？ 
SELECT 
COUNT(DISTINCT device_id) AS did_cnt,
COUNT(question_id) AS question_cnt
FROM question_practice_detail
WHERE DATE(date) BETWEEN '2021-08-01' AND '2021-08-31'
DISTINCE 需要注意一下

发表于 2021-12-02 10:24:04 回复(0)
更多回答
Mysql
酸菜鱼土豆大侠头像 酸菜鱼土豆大侠

【场景】：某月的情况

【分类】：聚合函数、日期函数、字符串函数

分析思路

难点：

1.计算总用户数时要对用户去重

2021年8月可以怎么写

  • [使用]：month和year
  • [使用]：date_format
  • [使用]：like
  • [使用]：mid
  • [使用]：left
  • [使用]：substring_index

扩展

前往查看：MySQL 字符串截取 left、right、substring、substring_index

求解代码

方法一：

month和year

select
    count(distinct device_id) as did_cnt,
    count(device_id) as question_cnt
from question_practice_detail
where month(date) = 8 and year(date) = 2021

方法二：

date_format

select
    count(distinct device_id) as did_cnt,
    count(device_id) as question_cnt
from question_practice_detail
where date_format(date,'%Y%m') = '202108'

方法三：

date_format

select
    count(distinct device_id) as did_cnt,
    count(device_id) as question_cnt
from question_practice_detail
where date_format(date,'%y%m') = '2108'

方法四：

like

select
    count(distinct device_id) as did_cnt,
    count(device_id) as question_cnt
from question_practice_detail
where date like '2021-08%'

方法五：

substring

select
    count(distinct device_id) as did_cnt,
    count(device_id) as question_cnt
from question_practice_detail
where substring(date,1,7) = '2021-08'

方法六：

mid

select
    count(distinct device_id) as did_cnt,
    count(device_id) as question_cnt
from question_practice_detail
where mid(date,1,7) = '2021-08'

方法七：

left

select
    count(distinct device_id) as did_cnt,
    count(device_id) as question_cnt
from question_practice_detail
where left(date,7) = '2021-08'

方法八：

substring_index

select
    count(distinct device_id) as did_cnt,
    count(device_id) as question_cnt
from question_practice_detail
where substring_index(date,'-',2) = '2021-08'
发表于 2022-11-27 17:31:12 回复(8)
Mysql
有滋有味小熊鞋头像 有滋有味小熊鞋
SELECT count(DISTINCT device_id) as did_cnt,count(question_id) as question_cnt
from question_practice_detail
where date >= '2021-08-01' and date <= '2021-08-31';
效率最高，尤其大数据时
发表于 2021-09-29 22:09:52 回复(14)
凌12头像 凌12 
select count(DISTINCT device_id),count(question_id) from question_practice_detail
where date like "2021-08%"

发表于 2021-09-24 14:09:50 回复(0)
牛客718659926号头像 牛客718659926号
这个题目怎么分到较难这一块
发表于 2021-11-03 14:42:42 回复(2)
橙留香_头像 橙留香_
select count(distinct device_id) did_cnt,count(question_id)
from question_practice_detail
#where `date` like '2021-08%';
where `date` between '2021-08-01' and '2021-08-31';
#where DATE_FORMAT(`date`,'%Y-%m') = '2021-08';
#where year(date)=2021 and month(date)=08;
发表于 2022-03-31 16:47:13 回复(0)
Mysql
2020万事胜意头像 2020万事胜意
select count(distinct device_id),count(*) question_cnt from question_practice_detail 
where month(date)=8
发表于 2021-12-07 14:31:12 回复(2)
Mysql
牛客154940960号头像 牛客154940960号
6种方法
select 
    count(distinct device_id)
    ,count(question_id)
from question_practice_detail
#where year(date) = '2021' and month(date) = '08'
#where date like "2021-08%"
#where date_format(date, '%Y-%m')='2021-08'
#where substring(date,1,7) = '2021-08'
#where substring_index(date,"-",2) = '2021-08'
where date_format(`date`,'%Y-%m') = '2021-08'

发表于 2022-08-06 17:46:46 回复(2)
Mysql
Sushine头像 Sushine 
select count(distinct device_id),count(question_id)from question_practice_detail where MONTH(date) = '08'

发表于 2021-08-27 11:05:25 回复(2)
一只懒懒的程序媛头像 一只懒懒的程序媛
select count(DISTINCT device_id) did_cnt,count(question_id) question_cnt from question_practice_detail
where date like "2021-08%";
发表于 2021-10-16 13:35:05 回复(0)
Mysql
已注销头像 已注销 
select
count(distinct device_id),
count(question_id)
from question_practice_detail where date like '2021-08%'
distinct 去重 
count 计数
data like '2021-08%' 模糊查询
发表于 2022-04-19 00:21:00 回复(1)
Mysql
恶作剧之神阿蒙头像 恶作剧之神阿蒙 
select count(distinct device_id) did_cnt,count(question_id) question_cnt
from question_practice_detail
where year(date)='2021' and month(date)='08'
没想到用like 用like方便啊，效率谁更高不太清楚
发表于 2021-08-26 10:28:21 回复(2)
Mysql
情话微甜头像 情话微甜
select
  count(distinct device_id) did_cnt,
  count(question_id) question_cnt
from
  question_practice_detail
where
  date_format(date,'%Y-%m') = '2021-08'
发表于 2022-08-20 23:03:13 回复(0)
Mysql
冷凡社长头像 冷凡社长 
select count(distinct device_id) as did_cnt,
count(*) as question_cnt
from question_practice_detail

主要是聚合函数

发表于 2022-08-06 16:35:58 回复(2)
Mysql
369186344头像 369186344 

select count(distinct device_id)did_cnt,
count(*)question_cnt from question_practice_detail
where year(date)='2021' and month(date)='08';

发表于 2022-04-22 23:17:58 回复(0)
Mysql
不知名具现头像 不知名具现
SELECT 
COUNT(DISTINCT device_id) did_cnt
,COUNT(question_id) question_cnt
FROM question_practice_detail
WHERE LEFT(date,7)='2021-08'
;
发表于 2022-03-25 19:45:10 回复(0)
Mysql
牛客586928673号头像 牛客586928673号
SELECT 
count(distinct device_id) did_cnt,
count(question_id) question_cnt
FROM question_practice_detail
where YEAR(date)=2021 and MONTH(date)=8
发表于 2022-01-19 20:40:50 回复(2)
Mysql
阿程7头像 阿程7
select count(distinct(device_id)) as did_cnt,
count(result) as question_cnt
from question_practice_detail
where year(date)=2021 and month(date)=08
发表于 2021-10-07 14:14:37 回复(1)
牛客830408403号头像 牛客830408403号
select count(distinct device_id) as did_cnt,count(question_id) as question_cnt
from question_practice_detail 
where DATE_FORMAT(date,'%Y-%m')='2021-08'
发表于 2021-08-31 15:38:52 回复(0)
Mysql
不太可爱的程序猪头像 不太可爱的程序猪 
select count(distinct device_id),count(device_id)
from question_practice_detail 
where date like '2021-08%';

发表于 2023-12-06 11:53:06 回复(0)
牛客263406344号头像 牛客263406344号
select count(distinct device_id) did_cnt,
count(question_id) question_cnt
from question_practice_detail
where month(date) = 8;
发表于 2023-09-06 00:33:13 回复(0)
提交观点

问题信息

难度：
347条回答 2013浏览

热门推荐

通过挑战的用户

查看代码

相关试题

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

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