牛客網Mysql相應試題 SQL28 計算使用者8月每天的練題數量

无名之辈的ggb發表於2024-03-16
SQL28 計算使用者8月每天的練題數量
描述
題目:現在運營想要計算出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
 
select t.a as day,count(t.device_id) as question_cnt from
(select day(date) as a,device_id from question_practice_detail where date like '2021-08%') t
group by t.a;
NOW(),用於獲取當前日期和時間函式 2024-03-16 10:58:06 select Now()
CURDATE(),用於獲取當前日期,同CURRENT_DATE() 2024-03-16
CURTIME(),用於獲取當前時間,同CURRENT_TIME() 10:58:06
DATE()函式,用於提取時間欄位的日期 2024-03-16
YEAR()函式,用於提取時間欄位的年,,返回的格式為"YYYY"
MONTH()函式,用於提取時間欄位的月,返回的格式為"mm",若月份前有0會忽略
DAY()函式,用於提取時間欄位的日期,,返回的格式為"dd",若日期前有0會忽略
TIME()函式,用於提取時間欄位的時間,,返回的格式為"HH:ii:ss"
HOUR()函式,用於提取時間欄位的小時,,返回的格式為"HH",若小時前有0會忽略,如08則返回8
MINUTE()函式,用於提取時間欄位的分鐘,返回的格式為"ii",若分鐘前有0會忽略,如08則返回8
SECOND()函式,用於提取時間欄位的秒,返回的格式為"ss",若秒前有0會忽略,如08則返回8
UNIX_TIMESTAMP()函式,獲取當前時間戳,還可以將時間欄位轉換成時間戳
WEEK()函式 獲取日期對應的第幾周
QUARTER() 獲取日期對應的季度
DAYOFYEAR() 獲取當天一日為這一年的第幾天
DAYNAME() 獲取對應周幾
DAYOFMONTH() 獲取日期是當月的第幾天
TIME_TO_SEC() 獲取時間對應的秒
ADDDATE('2023-05-18 10:58:59',2) 日期加N天

計算兩個時間差

SELECT DATEDIFF('2023-05-31 10:58:59','2023-05-18 10:58:59')

日期減去N天

SELECT SUBDATE('2023-05-18 10:58:59',2)

--獲取當前日期在本週的週一

select subdate(curdate(),date_format(curdate(),'%w')-1) ;

--獲取當前日期在本週的週日

select subdate(curdate(),date_format(curdate(),'%w')-7);

返回:2023-05-21

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

相關文章