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

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

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

输出:

easy|0.5000
medium|1.0000

原站题解

SELECT qd.difficult_level,(
SUM(CASE WHEN qpd.result='right' THEN 1
	ELSE 0
	END
)/COUNT(qpd.result)
) correct_rated
FROM user_profile up,
     question_practice_detail qpd,
     question_detail qd
WHERE     up.university='浙江大学'
      AND up.device_id=qpd.device_id
      AND qpd.question_id=qd.question_id
GROUP BY  qd.difficult_level
order by correct_rated asc

SELECT
difficult_level,
sum(if(result = 'right',1,0)) / count(1) correct_rate
from question_practice_detail a1 
left join user_profile a2
on a1.device_id = a2.device_id
left join question_detail a3
on a1.question_id = a3.question_id
where university = '浙江大学'
group by difficult_level
order by correct_rate ASC

select difficult_level,count(case when result='right' then 1 else null end)/count(q.question_id)as correct_rate
from user_profile as u
join question_practice_detail as q
on u.device_id=q.device_id
join question_detail as qd
on q.question_id=qd.question_id
where university='浙江大学'
group by qd.difficult_level
order by correct_rate;

select
    qd.difficult_level,
    sum(case when qpd.result = 'right' then 1 else 0 end)/count(qpd.device_id) correct_rate
from
    question_practice_detail qpd
left join question_detail qd on qd.question_id = qpd.question_id
left join user_profile up on qpd.device_id = up.device_id
where 
up.university = "浙江大学"
group by qd.difficult_level order by correct_rate

select 
 c.difficult_level,
 sum(c.right_question_cnt) / sum(c.question_cnt)
from user_profile up 
    inner join (
        select 
            device_id,
            difficult_level,
            count(difficult_level) as question_cnt,
            sum(if(a.result = 'right', 1, 0)) as right_question_cnt
        from question_practice_detail a 
            inner join question_detail b on a.question_id = b.question_id
        group by a.device_id, difficult_level
    ) c on c.device_id = up.device_id
 where up.university = '浙江大学'
 group by c.difficult_level
 order by sum(c.right_question_cnt) / sum(c.question_cnt)