首页 > 试题广场 >

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

[编程题]浙大不同难度题目的正确率
  • 热度指数：405373 时间限制：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
北沢真理头像 北沢真理
先在脑海里构思好关联后的表格每一行有什么样的数据，哪些数据需要被拿出来计算和统计，解决问题就不难了 
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。头像 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的记录 



发表于 2025-11-01 17:08:35 回复(1)
Mysql
大牛哥水青头像 大牛哥水青
WITH zjuser_profile AS
(SELECT * FROM user_profile
WHERE university = '浙江大学')
SELECT qd.difficult_level AS difficult_level,
COUNT(CASE WHEN qpd.result = 'right' THEN 1 END)/COUNT(qpd.question_id) AS correct_rate
FROM zjuser_profile AS zjp
LEFT JOIN question_practice_detail AS qpd ON zjp.device_id = qpd.device_id
LEFT JOIN question_detail AS qd ON qpd.question_id = qd.question_id
GROUP BY qd.difficult_level
ORDER BY correct_rate ASC;
不理解哪里错了，我在navicate上运行和答案一摸一样


发表于 2025-10-30 21:38:06 回复(0)
Mysql
提子味头像 提子味
select qd.difficult_level,
sum(result='right')/count(result) as correct_rate
from question_practice_detail as qpd
join question_detail qd on qpd.question_id = qd.question_id
left join user_profile u on u.device_id = qpd.device_id
where u.university = '浙江大学'
group by qd.difficult_level
order by correct_rate
发表于 2025-10-30 16:43:02 回复(0)
Mysql
求求offer的可乐很威猛头像 求求offer的可乐很威猛
select
qsdt.difficult_level,round(cast(sum(case when qpde.result='right' then 1 else 0 end) as float)/count(qpde.device_id),4) as correct_rate
from user_profile as uspr
left join question_practice_detail as qpde
on uspr.device_id=qpde.device_id
left join question_detail as qsdt
on qpde.question_id=qsdt.question_id
where uspr.university='浙江大学'
group by
qsdt.difficult_level

在windows server sql 中执行的代码
发表于 2025-10-24 09:03:20 回复(0)
Mysql
芒果点头像 芒果点
select difficult_level,sum(if(result="right",1,0))/count(q.question_id) as correct_rate
from user_profile u join
(select device_id,qd.question_id,result,difficult_level from  question_practice_detail qp, question_detail qd where qp.question_id=qd.question_id) as q
on u.device_id=q.device_id
where university="浙江大学"
group by difficult_level
order by correct_rate
发表于 2025-10-11 19:24:23 回复(0)
Mysql
牛客943437856号头像 牛客943437856号 
SELECT
    AA.difficult_level,
    SUM( CASE WHEN AA.result = 'right' THEN 1 ELSE 0 END )/SUM( CASE WHEN AA.result IS NULL THEN 0 ELSE 1 END ) AS 'correct_rate'
FROM
(
SELECT
    qpd.device_id,
    university,
    result,
    difficult_level
FROM question_practice_detail qpd
LEFT JOIN user_profile up
    ON qpd.device_id = up.device_id
LEFT JOIN question_detail qd
    ON qpd.question_id = qd.question_id
WHERE university = '浙江大学'
) AS AA
GROUP BY  AA.difficult_level
ORDER BY correct_rate ASC

发表于 2025-10-10 16:17:41 回复(0)
Mysql
矫健的小刺猬在看牛客头像 矫健的小刺猬在看牛客 
select
    difficult_level
    , count(case when result = 'right' then qpd.id end) / count(qpd.id) as correct_rate
from question_practice_detail qpd
    left join question_detail using(question_id)
    left join user_profile using(device_id)
where university = '浙江大学'
group by difficult_level
order by correct_rate
;

发表于 2025-10-09 10:25:04 回复(0)
Mysql
sp到手了的秋田犬很讲文明头像 sp到手了的秋田犬很讲文明 
SELECT
    qd.difficult_level,
    ROUND(
        COUNT(
            CASE
                WHEN qpd.result = "right" THEN 1
            END
        ) / COUNT(qpd.id),
        4
    ) AS correct_rate
FROM
    user_profile up
    JOIN question_practice_detail qpd ON up.device_id = qpd.device_id
    JOIN question_detail qd ON qpd.question_id = qd.question_id
WHERE
    up.university = "浙江大学"
GROUP BY
    qd.difficult_level
ORDER BY
    correct_rate
有时候不得不感叹这CASE WHEN是真好用啊
发表于 2025-09-24 17:20:03 回复(0)
Mysql
我腿短先溜了ing头像 我腿短先溜了ing
不知道是不是因为做了上一题的原因，感觉这道题没有上一题难。
select qd.difficult_level,
count(if (qp.result='right',qp.question_id,null))/count(qp.question_id) as correct_rate
from user_profile us
join question_practice_detail qp on us.device_id=qp.device_id 
join question_detail qd on qp.question_id=qd.question_id 
where us.university='浙江大学' 
group by qd.difficult_level 
order by correct_rate;


发表于 2025-09-22 18:12:58 回复(0)
Mysql
想退休的小小嘻在改简历头像 想退休的小小嘻在改简历
正确率的计算除数用的是difficult_level，和答案结果是一样的，有没有评论区大神可以解释一下用difficult_level和question_id有没有什么区别呢？ 
select
  qd.difficult_level,
  sum(case
      when qpd.result = 'right' then 1
      else 0 
    end) / count(qd.difficult_level) as correct_rate
from user_profile as up
left join question_practice_detail as qpd
on up.device_id = qpd.device_id
left join question_detail as qd
on qpd.question_id = qd.question_id
where university = '浙江大学'
group by qd.difficult_level
发表于 2025-09-07 10:03:25 回复(0)
Mysql
山水一尘头像 山水一尘
select
    difficult_level
    ,正确数量/总量 correct_rate
from
    (select
        u.device_id
        ,q.difficult_level
        ,sum(case
            when qd.result='right' then 1 else 0 end) 正确数量
        ,sum(case
                when qd.result <>'' then 1 else 0 end) 总量
    from user_profile u
    left join
        question_practice_detail qd
        on u.device_id=qd.device_id
    left join
         question_detail q
        on qd.question_id=q.question_id
    where u.university='浙江大学'
    group by 1,2
    ) s
发表于 2025-09-02 17:51:25 回复(0)
Mysql
牛客120748413号头像 牛客120748413号
select
q2.difficult_level
,sum(case
    when q1.result = 'right' then 1 else 0 end)/count(*) correct_rate
from user_profile u
    join question_practice_detail q1 on u.device_id = q1.device_id
    join question_detail q2 on q1.question_id = q2.question_id
where university = '浙江大学'
group by q2.difficult_level
order by correct_rate
发表于 2025-08-25 15:35:02 回复(0)
Mysql
野蛮的熊熊求你们别卷了头像 野蛮的熊熊求你们别卷了
select difficult_level,avg(new11.rate) as 正确率 from  
(select * from
(select qd.question_id,difficult_level,device_id,result,case when result='right' then 1 else 0 end as 'rate' from question_detail as qd
left join question_practice_detail as qpd on qd.question_id=qpd.question_id) as new where device_id in
(select device_id from user_profile as up where university='浙江大学')) as new11
group by difficult_level
order by 正确率;
发表于 2025-08-18 15:08:08 回复(0)
Mysql
世俗__头像 世俗__ 
select difficult_level,count(if(result='right',difficult_level,null))/count(difficult_level)  correct_rate
from question_practice_detail q1
left join question_detail q2 on q1.question_id=q2.question_id
where device_id in(select device_id from user_profile where university='浙江大学')
group by difficult_level
order by correct_rate
根据表连接将难度与是否正确连接起来，随后计算得到正确率，最后通过子查询限制浙江大学学生，根据正确率排序得到结果
发表于 2025-08-08 10:05:24 回复(0)
Mysql
不知道哦_头像 不知道哦_
select
    c.difficult_level,
    (
        sum(
            case
                when b.result = 'right' then 1
                else 0
            end
        ) / count(b.result)
    ) 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
;

发表于 2025-08-02 14:05:17 回复(0)
提交观点

问题信息

SQL
难度：
683条回答 5190浏览

热门推荐

通过挑战的用户

查看代码

相关试题

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

扫一扫，把题目装进口袋

求职之前，先上牛客

扫描二维码，进入QQ群

扫描二维码，关注牛客公众号