首页 > 试题广场 >

浙大不同难度题目的正确率

[编程题]浙大不同难度题目的正确率

热度指数：441399 时间限制：C/C++ 1秒，其他语言2秒空间限制：C/C++ 256M，其他语言512M
算法知识视频讲解

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

示例： 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

示例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

算法知识视频讲解

Mysql

在刷代码的代码渣渣很想run

select
    c.difficult_level,
    round(
        (
            sum(
                case
                    when result = 'right' then 1
                    else 0
                end
            ) / count(result)
        ),
        4
    ) as correct_rate
from
    user_profile as a
    inner join question_practice_detail b on a.device_id = b.device_id
    left join question_detail c on b.question_id = c.question_id
where
    a.university = '浙江大学'
group by
    c.difficult_level
order by
    correct_rate;

发表于 2026-04-10 16:58:23 回复(0)

Mysql

爱学习的小白z

select

sum(

case

when t2.result = "right" then 1

end

) / count(t2.question_id) as correct_rate,

t3.difficult_level

from

user_profile as t1

left join question_practice_detail as t2 on t1.device_id = t2.device_id

left join question_detail as t3 on t2.question_id = t3.question_id

where

t1.university = "浙江大学"

group by

t3.difficult_level

order by

correct_rate

发表于 2026-03-30 19:11:32 回复(1)

Mysql

Shellien

##分析思路：
##浙江大学答得全部题 left join 避免有人没答题表格为空
##不同的难度下，答题总数、答对的题 right join
##准确率==sum（答对题的人）/答题总数 保留4位小数
select
    difficult_level,
    round(
        sum(
            case
                when result = 'right' then 1
                else 0
            end
        ) / count(qpd.device_id),
        4
    ) as correct_rate
from
    question_detail qd
    right join question_practice_detail qpd on qpd.question_id = qd.question_id
    left join user_profile up on up.device_id = qpd.device_id
where
    university = '浙江大学'
group by
    difficult_level
order by
    correct_rate asc

发表于 2026-03-26 21:49:50 回复(1)

Mysql

三加二苏打夹心饼干

with
    t1 as (
        select
            qp.question_id, 
            qp.result,
            qd.difficult_level
        from
            user_profile as u
            inner join question_practice_detail as qp on u.device_id = qp.device_id
            inner join question_detail as qd on qd.question_id = qp.question_id
        where
            university = '浙江大学'
    )
select
    difficult_level, 
    round(
        sum(if(result = 'right', 1, 0)) / count(1), 
        4
    ) as correct_rate
from
    t1
group by
    difficult_level 
order by
    correct_rate;

发表于 2026-03-12 22:00:18 回复(0)

Mysql

567777_

select

p.difficult_level difficult_level,

sum(if(u.result = 'right', 1,0)) / count(u.result) as correct_rate

from

(

select

u.device_id,

u.question_id,

u.result,

p.difficult_level

from

user_profile q,

question_practice_detail u,

question_detail p

where

q.device_id = u.device_id

and u.question_id = p.question_id

and university = '浙江大学'

)

group by

p.difficult_level

ORDER BY

correct_rate ASC

有没有大佬帮我看看哪里有错误

发表于 2026-02-26 18:57:52 回复(0)

Mysql

巴比Q了的打工人很帅气

说白了白说了，一张表再计算

select

c.difficult_level,

sum(if(result = 'right', 1, 0)) / count(*) as correct_rate

from

user_profile a

join question_practice_detail b on a.device_id = b.device_id

join question_detail c on b.question_id = c.question_id

where

a.university = "浙江大学"

group by

c.difficult_level

order by

correct_rate asc

发表于 2026-02-26 16:09:38 回复(0)

Mysql

辣椒炒鸡蛋o

select difficult_level,
        # count(question_id) as all_question,
        sum(case when result='right' then 1 else 0 end)/count(question_id) as correct_rate
from (
select qd.difficult_level,
        pd.question_id,
        pd.result
from user_profile up
left join question_practice_detail pd on pd.device_id=up.device_id
left join question_detail qd on qd.question_id=pd.question_id
where up.university='浙江大学'
) t
where difficult_level is not null
group by difficult_level
order by correct_rate asc

使用left join 的话，会有空值出现，所以我再加一层为空过滤，聪明如我

发表于 2026-01-29 11:14:53 回复(0)

Mysql

牛客204803886号

我这个代码，自测运行是正确的，提交了就错了，而且我去mysql里面做了答案也是正确的

select difficult_level,

sum(if(result="right",1,0))/count(*) correct_rate

from question_practice_detail

left join question_detail

using(question_id)

left join user_profile

using(device_id)

where university="浙江大学"

group by difficult_level

提交的结果变成了这样：

发表于 2026-01-20 16:59:57 回复(0)

Mysql

乐神在学习

select
    question_detail.difficult_level,
    sum(if(a.result = 'right', 1, null)) / count(a.result)
from
    user_profile
    left join (
        select distinct
            device_id,
            question_id,
            result
        from
            question_practice_detail
    ) as a on user_profile.device_id = a.device_id
    left join question_detail on a.question_id = question_detail.question_id
where
    university = '浙江大学'
group by
    question_detail.difficult_level

哪里出问题了啊，求大神

发表于 2025-12-22 10:48:25 回复(0)

Mysql

比耶小羊

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

纯新手，我在计算correct_rate的时候稍微复杂一点，用的之前的那个sum case when

发表于 2025-12-20 18:00:46 回复(0)

Mysql

牛客164101865号

select

qd.difficult_level,

count(distinct qd.question_id)/ count( qpd.question_id) as correct_rate

from user_profile up,

question_practice_detail qpd,

question_detail qd

where up.device_id = qpd.device_id

and qpd.question_id = qd.question_id

and up.university = '浙江大学'

group by qd.difficult_level

-----------------------------------

count(distinct qd.question_id)/ count( qpd.question_id) as correct_rate 这一步逻辑越弄越乱

发表于 2025-12-19 01:34:21 回复(0)

Mysql

牛客716341211号

select c.difficult_level, count(case when result='right' then 1 else null end)/count(a.result) correct_rate from question_practice_detail a left join user_profile b on a.device_id=b.device_id

left join question_detail c on a.question_id=c.question_id where b.university='浙江大学' group by c.difficult_level order by correct_rate

发表于 2025-12-16 09:13:27 回复(0)

Mysql

牛客979891978号

求哪位大神能帮助我解惑？为什么我的代码自测运行时答案是对的，但保存提交时会报错，我发现会出现None 值，当我把left join 改为 join 时就可以顺利提交，有没有大神能指导我下呀？

select

c.difficult_level,

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

from user_profile as a

left join

question_practice_detail as b

on a.device_id = b.device_id

left join

question_detail as c

on b.question_id = c.question_id

where

a.university ='浙江大学'

group by c.difficult_level

order by correct_rate;

发表于 2025-12-13 12:39:12 回复(0)

Mysql

北沢真理

先在脑海里构思好关联后的表格每一行有什么样的数据，哪些数据需要被拿出来计算和统计，解决问题就不难了

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

发表于 2025-11-21 00:39:28 回复(0)

Mysql

沫渡雅栖

select
    q2.difficult_level,
    count(if(q1.result='right',1,null))/count(q1.device_id)as correct_rate
from
    question_detail q2
    left join question_practice_detail q1 on q2.question_id = q1.question_id
    left join user_profile u on u.device_id = q1.device_id and u.university='浙江大学'
where
    u.device_id IS NOT NULL
group by
    q2.difficult_level
    order by correct_rate,q2.difficult_level

发表于 2025-11-09 17:06:58 回复(0)

Mysql

Gross。

with t1 as (
select qd.difficult_level,count(1) as zr from question_detail qd
join question_practice_detail q
on qd.question_id = q.question_id
join user_profile u
on q.device_id=u.device_id
where u.university = '浙江大学'
group by qd.difficult_level
),
t2 as (select qd.difficult_level,count(1) as cr from question_detail qd
join question_practice_detail q
on qd.question_id = q.question_id
join user_profile u
on q.device_id=u.device_id
where q.result='right' and
u.university = '浙江大学'
group by qd.difficult_level)

select t2.difficult_level,coalesce(t2.cr/t1.zr,0) as correct_rate from t1 join t2 on t1.difficult_level=t2.difficult_level order by correct_rate

有大佬帮我看看吗少了一条easy为0的记录