Mysql刷题题解_多表联查_浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。

题目详情

题目： 现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况，请取出相应数据，并按照准确率升序输出。

示例： user_profile

id	device_id	gender	age	university	gpa	active_days_within_30	question_cnt	answer_cnt
1	2138	male	21	北京大学	3.4	7	2	12
2	3214	male		复旦大学	4	15	5	25
3	6543	female	20	北京大学	3.2	12	3	30
4	2315	female	23	浙江大学	3.6	5	1	2
5	5432	male	25	山东大学	3.8	20	15	70
6	2131	male	28	山东大学	3.3	15	7	13
7	4321	female	26	复旦大学	3.6	9	6	52

示例： question_practice_detail

id	device_id	question_id	result
1	2138	111	wrong
2	3214	112	wrong
3	3214	113	wrong
4	6543	111	right
5	2315	115	right
6	2315	116	right
7	2315	117	wrong

示例： question_detail

question_id	difficult_level
111	hard
112	medium
113	easy
115	easy
116	medium
117	easy

根据示例，你的查询应返回以下结果：

difficult_level	correct_rate
easy	0.5000
medium	1.0000

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');
复制
输出：
easy|0.5000
medium|1.0000

题解

我们按照题目对条件进行拆分，可以得到如下条件

1、用户在浙江大学

where unibersity like '浙%

2、统计虽有难度下的答题正确率所以

 sum(CASE
      when result='right' then 1
      else 0
      end))/count(question_id) as correct_rate

3、还要升序排列所以

order by correct_rate asc

4、因为是计算所有不同难度下的题目准确率，所以需要对难度进行分组

group by difficult_level 

之后假设一张已经获取到所有结果的表t，我们可以通过该表t一次查询出我们所需要的内容
可以得到如下表达式

select 
   difficult_level,
   sum( CASE
      when tt.result='right' then 1
      else 0
      end) / count(tt.question_id) as correct_rate
from t 
where university like '浙%'
group by difficult_level order by correct_rate asc;

那我们如何组装这个表t，首先看题中给了三张表，分别记录用户基本信息qu、用户答题情况pu、题目难易程度qs。

分析条件一可知，用户需要时浙江大学的，所以用户基本信息表qu被需要。

分析条件二可知，需要统计所有难度下的答题率，那么我们首先就需要一张存着题目难易程度的用户答题情况表。这个表可以通过pu和qs表内连接得到，命名tt。

由此可以得到我们查询所必须的两张表qu和tt，那么我们把这两张表连接起来不就得到我们表达式中的表t了吗

由此得出答案

select 
 difficult_level,
 sum( CASE
      when tt.result='right' then 1
      else 0
      end) / count(tt.question_id) as correct_rate
from 
user_profile as up join 
   (select qu.*,qd.difficult_level
    from question_practice_detail as qu 
    inner join question_detail as qd on qu.question_id=qd.question_id)
    as tt
    on up.device_id=tt.device_id
where university like '浙%' 
group by difficult_level order by correct_rate asc;