手把手帶你學SQL—牛客網SQL 統計複旦使用者8月練題情況

牛客是一款不論是面試 還是刷題 都是非常有用的 還等什麼，傳送門 ​​牛客網sql​​

🥇作者簡介：大家好我是 uu 給剛入門的sql的小夥伴帶來一套sql 完整的入門基礎。

🥈個人首頁：uu首頁

📑 推薦一款非常火的面試、刷題神器👉​​牛客網sql​​  

覺得uu寫的不錯的話 麻煩動動小手 點贊👍 收藏⭐  評論📄

今天給大家帶來的刷題系列是： 統計複旦使用者8月練題情況

​​ 

題目介紹 ：

描述

題目： 現在營運想要了解複旦大學的每個使用者在8月份練習的總題目數和回答正确的題目數情況，請取出相應明細資料，對于在8月份沒有練習過的使用者，答題數結果傳回0.

示例：使用者資訊表user_profile

iddevice_idgenderageuniversitygpaactive_days_within_3012138male21北京大學3.4723214male複旦大學4.01536543female20北京大學3.21242315female23浙江大學3.6555432male25山東大學3.82062131male28山東大學3.31574321female26複旦大學3.69

示例：question_practice_detail

iddevice_idquestion_idresultdate12138111wrong2021-05-0323214112wrong2021-05-0933214113wrong2021-06-1546543111right2021-08-1352315115right2021-08-1362315116right2021-08-1472315117wrong2021-08-15……

根據示例，你的查詢應傳回以下結果：

device_iduniversityquestion_cntright_question_cnt3214複旦大學304321複旦大學00

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

複制輸出：

3214|複旦大學|3|0

4321|複旦大學|0|0

代碼解析：

​​select
t1.device_id,
t1.university,
sum(case when t2.result is not null then 1 else 0 end),
sum(case when t2.result = 'right' then 1 else 0 end)
FROM user_profile t1
LEFT JOIN question_practice_detail t2 
ON t1.device_id = t2.device_id and MONTH(t2.date) = '08'
where t1.university = '複旦大學'
group by t1.device_id​​