题目:现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况,请取出相应数据,并按照准确率升序输出。 示例: 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 马上挑战 算法知识视频讲解 提交运行 算法知识视频讲解 添加笔记 求解答(0) 邀请回答 收藏(1250) 分享 提交结果有问题? 316个回答 120篇题解 添加回答 17 Mysql Qiuxj SELECT difficult_level, AVG(IF(result=’right’,1,0)) AS correct_rate FROM user_profile u, question_practice_detail qpd, question_detail qd WHERE u.device_id = qpd.device_id AND qpd.question_id = qd.question_id AND university=’浙江大学’ GROUP BY difficult_level ORDER BY correct_rate; 发表于 2021-09-29 22:58:04 回复(4) 12 Mysql 老狗丶 select difficult_level, (sum(case when qpd.result = "right" then 1 else 0 end)/count(u.answer_cnt)) as correct_rate from user_profile u inner join question_practice_detail qpd on u.device_id = qpd.device_id inner join question_detail qd on qd.question_id = qpd.question_id  where university = "浙江大学" group by difficult_level order by correct_rate; 发表于 2021-08-28 16:11:28 回复(9) 4 智能路障 这题考察的是多表查询,只要把条件梳理到位就可迎刃而解 select       difficult_level, count(if(result = ‘right’, 1, null)) / count(result) as correct_rate from user_profile t1,      question_practice_detail t2,      question_detail t3 where t1.device_id = t2.device_id and t2.question_id = t3.question_id and university = ‘浙江大学’ group by difficult_level order by correct_rate 发表于 2021-10-31 11:17:26 回复(2) 4 fire-light-guo select      d.difficult_level,     sum(if(result = ‘right’, 1, 0)) / count(*) as correct_rate from      user_profile u, question_practice_detail qp, question_detail d where      u.university = ‘浙江大学’      and u.device_id = qp.device_id     and qp.question_id = d.question_id group by d.difficult_level order by correct_rate 1. 做个三表关联 2. 按问题难度分组     3. sum()嵌套if()计算出正确的答题数量,再除以答题总数 发表于 2021-10-13 20:45:23 回复(2) 7 Baily1234 SELECT difficult_level, SUM(IF(result = "wrong", 0, 1)) / count(*) AS correct_rate FROM question_practice_detail q LEFT JOIN user_profile u ON q.device_id = u.device_id LEFT JOIN question_detail q2 ON q.question_id = q2.question_id WHERE university = "浙江大学" GROUP BY difficult_level ORDER BY correct_rate; 发表于 2021-10-15 16:22:28 回复(2) 3 茵桃小蚊子 没有上一题难… 现在习惯写代码前开始用加粗关键词和初步处理方法先捋一遍题目,然后就写得很顺手呢。如下: 现在运营想要了解浙江大学(where)的用户在不同难度题目(group by)下答题的正确率(正确数/题数)情况,请取出相应数据,并按照准确率升序(order by asc)输出。 SELECT qd.difficult_level as difficult_level, sum(if(qpd.result="right",1,0))/ COUNT(qpd.result) as correct_rate FROM user_profile u RIGHT JOIN question_practice_detail qpd ON (u.device_id = qpd.device_id) LEFT JOIN question_detail qd ON (qd.question_id = qpd.question_id) WHERE u.university = "浙江大学" GROUP BY qd.difficult_level ORDER BY correct_rate ASC 发表于 2021-11-25 21:05:25 回复(0) 3 Mysql 其实是牛哥 select c.difficult_level, count(if(result=’right’,a.question_id,null)) / count(a.question_id) as correct_rate from (select device_id,question_id,result from question_practice_detail )a join (select device_id from user_profile where university = ‘浙江大学’ )b on a.device_id = b.device_id left join ( select question_id,difficult_level from question_detail )c on a.question_id = c.question_id group by difficult_level order by correct_rate 发表于 2021-08-27 10:57:13 回复(0) 2 Mysql YonChun select difficult_level, count(if(result=’right’,1,null)) / count(result) as correct_rate from user_profile up join question_practice_detail using(device_id) join question_detail using(question_id) where university=’浙江大学’ group by difficult_level order by correct_rate 应该是最精简的了吧 发表于 2022-01-31 11:04:12 回复(0) 2 Mysql 牛客597002486号 select difficult_level, (count(if(result=’right’,1,null)) / count(a.question_id)) as correct_rate from question_practice_detail a join (select device_id from user_profile where university=’浙江大学’) b on a.device_id=b.device_id join question_detail c on a.question_id=c.question_id group by difficult_level order by correct_rate 发表于 2021-10-03 00:47:26 回复(4) 1 Mysql 369186344 select difficult_level, (count(case when t2.result = ‘right’ then 1 else null end)/ count(t2.question_id))correct_rate from (select qp.device_id,question_id,result from  question_practice_detail qp where qp.device_id in  (select device_id from user_profile up where university = ‘浙江大学’))t2 join question_detail qd on t2.question_id = qd.question_id group by difficult_level order by correct_rate; 发表于 2022-04-22 23:01:00 回复(0) 1 黑白配XY select difficult_level,  count(if(result=’right’,result,null))/ count(result) from user_profile u join (select device_id,difficult_level,result from question_practice_detail q1 join question_detail q2 on q1.question_id = q2.question_id) t on u.device_id = t.device_id and university = ‘浙江大学’ group by difficult_level 发表于 2022-04-18 16:25:48 回复(0) 1 Mysql 牛客228167738号 SELECT d.difficult_level, count(if(q.result="right", 1, null)) / count(*) AS correct_rate FROM user_profile AS u INNER JOIN question_practice_detail AS q ON u.device_id = q.device_id INNER JOIN question_detail AS d ON q.question_id = d.question_id WHERE u.university = "浙江大学" GROUP BY d.difficult_level ORDER BY correct_rate ASC; 发表于 2022-04-16 23:45:52 回复(0) 1 Mysql 牛客398096850号 有没有哪位大神帮我看下我的none是哪步错了吗 SELECT Q.difficult_level     ,COUNT(CASE WHEN P.result = ‘right’ THEN 1 ELSE NULL END)/COUNT(P.question_id) AS correct_rate FROM  (     (     SELECT device_id         ,university     FROM user_profile     WHERE university = ‘浙江大学’     )U     LEFT JOIN     (     SELECT device_id         ,question_id         ,result     FROM question_practice_detail     )P     ON P.device_id = U.device_id          LEFT JOIN     (     SELECT question_id         ,difficult_level     FROM question_detail     )Q     ON Q.question_id = P.question_id ) GROUP BY Q.difficult_level ORDER BY correct_rate 发表于 2022-04-05 17:35:03 回复(0) 1 Mysql 柒小漠 select difficult_level,  count(case when result =’right’ then 1 else null end)/ count(question_detail.question_id) as rightrate from question_practice_detail  join user_profile on question_practice_detail.device_id=user_profile.device_id join question_detail on question_practice_detail.question_id=question_detail.question_id where university = ‘浙江大学’ group by difficult_level order by rightrate 发表于 2022-03-29 14:55:59 回复(0) 1 Mysql Fayuan select   t3.difficult_level,sum(case t2.result when ‘right’ then 1 else 0 end) / count(1) as ‘correct_rate’ from user_profile t1 join question_practice_detail t2 on t1.device_id=t2.device_id join question_detail t3 on t2.question_id=t3.question_id where t1.university=’浙江大学’ group by t3.difficult_level order by correct_rate  发表于 2022-03-22 15:07:27 回复(0) 1 XXCC111111 1、学位为浙江大学,为条件;2、不同难度水平的,就表示可能需要根据难度水平来分组,使用group by函数;3、答题正确率用sum(if(qpd.result = ‘right’, 1, 0))/count(*) 来计算 select difficult_level, sum(if(qpd.result = ‘right’, 1, 0))/count(*) as correct_rate from question_practice_detail as qpd left join user_profile as up on qpd.device_id = up.device_id left join question_detail as qd on qpd.question_id = qd.question_id where university = ‘浙江大学’ group by qd.difficult_level order by correct_rate; 发表于 2022-03-03 13:22:17 回复(0) 1 Mysql 柒鳴飞 select difficult_level, sum(if(result=’right’,1,0))/count(qd.question_id) 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 university = ‘浙江大学’  group by difficult_level order by correct_rate  发表于 2021-12-14 16:48:23 回复(0) 1 Mysql 牛客689071742号 SELECT      d.difficult_level,      sum(      IF      ( q.result = ‘right’, 1, 0 ))/ COUNT(*) AS correct_rate  FROM      question_practice_detail AS q      INNER JOIN user_profile AS u ON u.device_id = q.device_id       AND u.university = ‘浙江大学’      LEFT JOIN question_detail AS d ON q.question_id = d.question_id  GROUP BY      d.difficult_level  ORDER BY      correct_rate ASC 发表于 2021-11-20 11:22:56 回复(0) 1 Mysql 键盘盘盘盘 select       difficult_level,      sum(case when result = ‘right’ then 1 else 0 end)/count(*) as correct_rate from  question_practice_detail as qpd left join  user_profile as u on  u.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 order by correct_rate 1注意写的时候如果都和中间的表有关,就得让中间的表left join其他的两个表 2注意count是统计行数,并不是计算,如果要计算的话,需要使用sum函数计算列里面的值!! 发表于 2021-11-08 14:50:46 回复(0) 1 Mysql 码农矿工小付 SELECT tmp.difficult_level, AVG(IF(tmp.result = ‘right’, 1, 0)) AS correct_rate FROM (SELECT qpd.question_id, qpd.result, qd.difficult_level, qpd.date     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 up.university = ‘浙江大学’) AS tmp     GROUP BY tmp.difficult_level; 这个怎么改啊?用例就是一个过不去,而且是顺序问题,这种该怎么处理啊? 发表于 2021-08-25 15:14:02 回复(7) 这道题你会答吗?花几分钟告诉大家答案吧! 提交观点

区块链毕设网qklbishe.com为您提供问题的解答

题目:现在运营想要了解江大学的用户在不同难度题目下答题的正确率情况,请取出相应数据,并按照准确率升序输出。
示例: 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

SELECT difficult_level, AVG(IF(result='right',1,0)) AS correct_rate FROM user_profile u, question_practice_detail qpd, question_detail qd WHERE u.device_id = qpd.device_id AND qpd.question_id = qd.question_id AND university='浙江大学' GROUP BY difficult_level ORDER BY correct_rate;

58:04

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

11:28

这题考察的是多表查询,只要把条件梳理到位就可迎刃而解
select       difficult_level, count(if(result = 'right', 1, null)) / count(result) as correct_rate from user_profile t1,      question_practice_detail t2,      question_detail t3 where t1.device_id = t2.device_id and t2.question_id = t3.question_id and university = '浙江大学' group by difficult_level order by correct_rate

17:26

select      d.difficult_level,     sum(if(result = 'right', 1, 0)) / count(*) as correct_rate from      user_profile u, question_practice_detail qp, question_detail d where      u.university = '浙江大学'      and u.device_id = qp.device_id     and qp.question_id = d.question_id group by d.difficult_level order by correct_rate   
1. 做个三表关联
2. 按问题难度分组
    3. sum()嵌套if()计算出正确的答题数量,再除以答题总数

45:23

SELECT         difficult_level,     SUM(IF(result = "wrong", 0, 1)) / count(*) AS correct_rate  FROM     question_practice_detail q     LEFT JOIN user_profile u ON q.device_id = u.device_id     LEFT JOIN question_detail q2 ON q.question_id = q2.question_id WHERE     university = "浙江大学" GROUP BY     difficult_level ORDER BY      correct_rate;

22:28

没有上一题难…
现在习惯写代码前开始用加粗关键词初步处理方法先捋一遍题目,然后就写得很顺手呢。如下:
现在运营想要了解浙江大学(where)的用户在不同难度题目(group by)下答题的正确率(正确数/题数)情况,请取出相应数据,并按照准确率升序(order by asc)输出。

SELECT qd.difficult_level as difficult_level, sum(if(qpd.result="right",1,0))/ COUNT(qpd.result) as correct_rate FROM user_profile u RIGHT JOIN question_practice_detail qpd ON (u.device_id = qpd.device_id) LEFT JOIN question_detail qd ON (qd.question_id = qpd.question_id) WHERE u.university = "浙江大学" GROUP BY qd.difficult_level ORDER BY correct_rate ASC

05:25

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

57:13

select difficult_level, count(if(result='right',1,null)) / count(result) as correct_rate from user_profile up join question_practice_detail using(device_id) join question_detail using(question_id) where university='浙江大学' group by difficult_level order by correct_rate 

应该是最精简的了吧

04:12

select difficult_level, (count(if(result=’right’,1,null)) / count(a.question_id)) as correct_rate
from
question_practice_detail a
join (select device_id from user_profile where university=’浙江大学’) b on a.device_id=b.device_id
join question_detail c on a.question_id=c.question_id
group by difficult_level
order by correct_rate
47:26

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

01:00

select difficult_level, 
count(if(result=’right’,result,null))/ count(result)
from user_profile u
join
(select device_id,difficult_level,result
from question_practice_detail q1
join
question_detail q2
on q1.question_id = q2.question_id) t
on u.device_id = t.device_id and university = ‘浙江大学’
group by difficult_level
25:48

SELECT     d.difficult_level,     count(if(q.result="right", 1, null)) / count(*) AS correct_rate FROM     user_profile AS u     INNER JOIN question_practice_detail AS q ON u.device_id = q.device_id     INNER JOIN question_detail AS d ON q.question_id = d.question_id WHERE     u.university = "浙江大学" GROUP BY     d.difficult_level ORDER BY     correct_rate ASC;

45:52

有没有哪位大神帮我看下我的none是哪步错了吗
题目:现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况,请取出相应数据,并按照准确率升序输出。             示例: 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                                                                                                                                                                                                                                                                                                                                                                     马上挑战                                                         算法知识视频讲解                                                                                                                                                                                                      提交运行                                                         算法知识视频讲解                                                                                                                                                                                             添加笔记                                                                                                                                                   求解答(0)                                                                                                               邀请回答                                                                                      收藏(1250)                                                                                                           分享                                                                                                      提交结果有问题?                                                                                                                                                                                                                                                                                                       316个回答                          120篇题解                                                                   添加回答                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 17                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           Qiuxj                                                                                                                                                                                 SELECT difficult_level, AVG(IF(result='right',1,0)) AS correct_rate FROM user_profile u, question_practice_detail qpd, question_detail qd WHERE u.device_id = qpd.device_id AND qpd.question_id = qd.question_id AND university='浙江大学' GROUP BY difficult_level ORDER BY correct_rate;                                                                                                                                                                                                                                                                                                                                             发表于  2021-09-29 22:58:04                                                                                                                                                                                                    回复(4)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        12                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           老狗丶                                                                                                                                                                                 select difficult_level, (sum(case when qpd.result = "right" then 1 else 0 end)/count(u.answer_cnt)) as correct_rate from user_profile u inner join question_practice_detail qpd on u.device_id = qpd.device_id inner join question_detail qd on qd.question_id = qpd.question_id  where university = "浙江大学" group by difficult_level order by correct_rate;                                                                                                                                                                                                                                                                                                                                             发表于  2021-08-28 16:11:28                                                                                                                                                                                                    回复(9)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        4                                                                                                                                                                                                                                                                                                                                                                                         智能路障                                                                                                                                                                                   这题考察的是多表查询,只要把条件梳理到位就可迎刃而解   select       difficult_level, count(if(result = 'right', 1, null)) / count(result) as correct_rate from user_profile t1,      question_practice_detail t2,      question_detail t3 where t1.device_id = t2.device_id and t2.question_id = t3.question_id and university = '浙江大学' group by difficult_level order by correct_rate                                                                                                                                                                                                                                                                                                                                              发表于  2021-10-31 11:17:26                                                                                                                                                                                                    回复(2)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        4                                                                                                                                                                                                                                                                                                                                                                                         fire-light-guo                                                                                                                                                                                 select      d.difficult_level,     sum(if(result = 'right', 1, 0)) / count(*) as correct_rate from      user_profile u, question_practice_detail qp, question_detail d where      u.university = '浙江大学'      and u.device_id = qp.device_id     and qp.question_id = d.question_id group by d.difficult_level order by correct_rate      1. 做个三表关联    2. 按问题难度分组        3. sum()嵌套if()计算出正确的答题数量,再除以答题总数                                                                                                                                                                                                                                                                                                                                                 发表于  2021-10-13 20:45:23                                                                                                                                                                                                    回复(2)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        7                                                                                                                                                                                                                                                                                                                                                                                         Baily1234                                                                                                                                                                                 SELECT         difficult_level,     SUM(IF(result = "wrong", 0, 1)) / count(*) AS correct_rate  FROM     question_practice_detail q     LEFT JOIN user_profile u ON q.device_id = u.device_id     LEFT JOIN question_detail q2 ON q.question_id = q2.question_id WHERE     university = "浙江大学" GROUP BY     difficult_level ORDER BY      correct_rate;                                                                                                                                                                                                                                                                                                                                          发表于  2021-10-15 16:22:28                                                                                                                                                                                                    回复(2)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        3                                                                                                                                                                                                                                                                                                                                                                                         茵桃小蚊子                                                                                                                                                                                   没有上一题难...    现在习惯写代码前开始用加粗关键词和初步处理方法先捋一遍题目,然后就写得很顺手呢。如下:           现在运营想要了解浙江大学(where)的用户在不同难度题目(group by)下答题的正确率(正确数/题数)情况,请取出相应数据,并按照准确率升序(order by asc)输出。        SELECT qd.difficult_level as difficult_level, sum(if(qpd.result="right",1,0))/ COUNT(qpd.result) as correct_rate FROM user_profile u RIGHT JOIN question_practice_detail qpd ON (u.device_id = qpd.device_id) LEFT JOIN question_detail qd ON (qd.question_id = qpd.question_id) WHERE u.university = "浙江大学" GROUP BY qd.difficult_level ORDER BY correct_rate ASC                                                                                                                                                                                                                                                                                                                                                 发表于  2021-11-25 21:05:25                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           其实是牛哥                                                                                                                                                                                 select c.difficult_level, count(if(result='right',a.question_id,null)) / count(a.question_id) as correct_rate from   (select device_id,question_id,result from question_practice_detail )a   join  (select device_id from user_profile where university = '浙江大学' )b on a.device_id = b.device_id   left join ( select question_id,difficult_level from question_detail )c   on a.question_id = c.question_id group by difficult_level order by correct_rate                                                                                                                                                                                                                                                                                                                                             发表于  2021-08-27 10:57:13                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           YonChun                                                                                                                                                                                 select difficult_level, count(if(result='right',1,null)) / count(result) as correct_rate from user_profile up join question_practice_detail using(device_id) join question_detail using(question_id) where university='浙江大学' group by difficult_level order by correct_rate  应该是最精简的了吧                                                                                                                                                                                                                                                                                                                                          发表于  2022-01-31 11:04:12                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           牛客597002486号                                                                                                                                                                                 select difficult_level, (count(if(result='right',1,null)) / count(a.question_id)) as correct_rate   from   question_practice_detail a   join (select device_id from user_profile where university='浙江大学') b on a.device_id=b.device_id   join question_detail c on a.question_id=c.question_id   group by difficult_level   order by correct_rate                                                                                                                                                                                                                                                                                                                                            发表于  2021-10-03 00:47:26                                                                                                                                                                                                    回复(4)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           369186344                                                                                                                                                                                 select difficult_level, (count(case when t2.result = 'right' then 1 else null end)/ count(t2.question_id))correct_rate from (select qp.device_id,question_id,result from  question_practice_detail qp where qp.device_id in  (select device_id from user_profile up where university = '浙江大学'))t2 join question_detail qd on t2.question_id = qd.question_id group by difficult_level order by correct_rate;                                                                                                                                                                                                                                                                                                                                             发表于  2022-04-22 23:01:00                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                         黑白配XY                                                                                                                                                                                 select difficult_level,    count(if(result='right',result,null))/ count(result)   from user_profile u   join   (select device_id,difficult_level,result   from question_practice_detail q1   join   question_detail q2   on q1.question_id = q2.question_id) t   on u.device_id = t.device_id and university = '浙江大学'   group by difficult_level                                                                                                                                                                                                                                                                                                                                            发表于  2022-04-18 16:25:48                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           牛客228167738号                                                                                                                                                                                 SELECT     d.difficult_level,     count(if(q.result="right", 1, null)) / count(*) AS correct_rate FROM     user_profile AS u     INNER JOIN question_practice_detail AS q ON u.device_id = q.device_id     INNER JOIN question_detail AS d ON q.question_id = d.question_id WHERE     u.university = "浙江大学" GROUP BY     d.difficult_level ORDER BY     correct_rate ASC;                                                                                                                                                                                                                                                                                                                                          发表于  2022-04-16 23:45:52                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           牛客398096850号                                                                                                                                                                                   有没有哪位大神帮我看下我的none是哪步错了吗         SELECT Q.difficult_level     ,COUNT(CASE WHEN P.result = 'right' THEN 1 ELSE NULL END)/COUNT(P.question_id) AS correct_rate FROM  (     (     SELECT device_id         ,university     FROM user_profile     WHERE university = '浙江大学'     )U      LEFT JOIN     (     SELECT device_id         ,question_id         ,result     FROM question_practice_detail     )P     ON P.device_id = U.device_id          LEFT JOIN     (     SELECT question_id         ,difficult_level     FROM question_detail     )Q     ON Q.question_id = P.question_id ) GROUP BY Q.difficult_level ORDER BY correct_rate                                                                                                                                                                                                                                                                                                                                                 发表于  2022-04-05 17:35:03                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           柒小漠                                                                                                                                                                                 select difficult_level,    count(case when result ='right' then 1 else null end)/ count(question_detail.question_id) as rightrate   from question_practice_detail    join user_profile on question_practice_detail.device_id=user_profile.device_id   join question_detail on question_practice_detail.question_id=question_detail.question_id   where university = '浙江大学'   group by difficult_level   order by rightrate                                                                                                                                                                                                                                                                                                                                            发表于  2022-03-29 14:55:59                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           Fayuan                                                                                                                                                                                 select   t3.difficult_level,sum(case t2.result when 'right' then 1 else 0 end) / count(1) as 'correct_rate'   from user_profile t1 join question_practice_detail t2 on t1.device_id=t2.device_id   join question_detail t3 on t2.question_id=t3.question_id   where t1.university='浙江大学'   group by t3.difficult_level   order by correct_rate                                                                                                                                                                                                                                                                                                                                             发表于  2022-03-22 15:07:27                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                         XXCC111111                                                                                                                                                                                   1、学位为浙江大学,为条件;2、不同难度水平的,就表示可能需要根据难度水平来分组,使用group by函数;3、答题正确率用sum(if(qpd.result = 'right', 1, 0))/count(*) 来计算          select difficult_level,   sum(if(qpd.result = 'right', 1, 0))/count(*) as correct_rate   from question_practice_detail as qpd   left join user_profile as up   on qpd.device_id = up.device_id   left join question_detail as qd   on qpd.question_id = qd.question_id   where university = '浙江大学'   group by qd.difficult_level   order by correct_rate;                                                                                                                                                                                                                                                                                                                                                   发表于  2022-03-03 13:22:17                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           柒鳴飞                                                                                                                                                                                 select difficult_level, sum(if(result='right',1,0))/count(qd.question_id) 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 university = '浙江大学'  group by difficult_level order by correct_rate                                                                                                                                                                                                                                                                                                                                              发表于  2021-12-14 16:48:23                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           牛客689071742号                                                                                                                                                                                 SELECT        d.difficult_level,        sum(        IF        ( q.result = 'right', 1, 0 ))/ COUNT(*) AS correct_rate    FROM        question_practice_detail AS q        INNER JOIN user_profile AS u ON u.device_id = q.device_id         AND u.university = '浙江大学'        LEFT JOIN question_detail AS d ON q.question_id = d.question_id    GROUP BY        d.difficult_level    ORDER BY        correct_rate ASC                                                                                                                                                                                                                                                                                                                                            发表于  2021-11-20 11:22:56                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           键盘盘盘盘                                                                                                                                                                                 select         difficult_level,        sum(case when result = 'right' then 1 else 0 end)/count(*) as correct_rate   from    question_practice_detail as qpd   left join    user_profile as u   on    u.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     order by correct_rate          1注意写的时候如果都和中间的表有关,就得让中间的表left join其他的两个表    2注意count是统计行数,并不是计算,如果要计算的话,需要使用sum函数计算列里面的值!!                                                                                                                                                                                                                                                                                                                                           发表于  2021-11-08 14:50:46                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           码农矿工小付                                                                                                                                                                                 SELECT tmp.difficult_level, AVG(IF(tmp.result = 'right', 1, 0)) AS correct_rate FROM (SELECT qpd.question_id, qpd.result, qd.difficult_level, qpd.date     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 up.university = '浙江大学') AS tmp     GROUP BY tmp.difficult_level;   这个怎么改啊?用例就是一个过不去,而且是顺序问题,这种该怎么处理啊?                                                                                                                                                                                                                                                                                                                                          发表于  2021-08-25 15:14:02                                                                                                                                                                                                    回复(7)                                                                                                                                                                                                                                                                                                                                                                                                 这道题你会答吗?花几分钟告诉大家答案吧!                                                                                                                                                                                                                                                                                       提交观点
SELECT Q.difficult_level     ,COUNT(CASE WHEN P.result = 'right' THEN 1 ELSE NULL END)/COUNT(P.question_id) AS correct_rate FROM  (     (     SELECT device_id         ,university     FROM user_profile     WHERE university = '浙江大学'     )U      LEFT JOIN     (     SELECT device_id         ,question_id         ,result     FROM question_practice_detail     )P     ON P.device_id = U.device_id          LEFT JOIN     (     SELECT question_id         ,difficult_level     FROM question_detail     )Q     ON Q.question_id = P.question_id ) GROUP BY Q.difficult_level ORDER BY correct_rate

35:03

select difficult_level, 
count(case when result =’right’ then 1 else null end)/ count(question_detail.question_id) as rightrate
from question_practice_detail 
join user_profile on question_practice_detail.device_id=user_profile.device_id
join question_detail on question_practice_detail.question_id=question_detail.question_id
where university = ‘浙江大学’
group by difficult_level
order by rightrate
55:59

select   t3.difficult_level,sum(case t2.result when ‘right’ then 1 else 0 end) / count(1) as ‘correct_rate’
from user_profile t1 join question_practice_detail t2 on t1.device_id=t2.device_id
join question_detail t3 on t2.question_id=t3.question_id
where t1.university=’浙江大学’
group by t3.difficult_level
order by correct_rate 
07:27

1、学位为浙江大学,为条件;2、不同难度水平的,就表示可能需要根据难度水平来分组,使用group by函数;3、答题正确率用sum(if(qpd.result = ‘right’, 1, 0))/count(*) 来计算
select difficult_level,
sum(if(qpd.result = ‘right’, 1, 0))/count(*) as correct_rate
from question_practice_detail as qpd
left join user_profile as up
on qpd.device_id = up.device_id
left join question_detail as qd
on qpd.question_id = qd.question_id
where university = ‘浙江大学’
group by qd.difficult_level
order by correct_rate;

22:17

select difficult_level, sum(if(result='right',1,0))/count(qd.question_id) 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 university = '浙江大学'  group by difficult_level order by correct_rate 

48:23

SELECT
    d.difficult_level,
    sum(
    IF
    ( q.result = ‘right’, 1, 0 ))/ COUNT(*) AS correct_rate 
FROM
    question_practice_detail AS q
    INNER JOIN user_profile AS u ON u.device_id = q.device_id 
    AND u.university = ‘浙江大学’
    LEFT JOIN question_detail AS d ON q.question_id = d.question_id 
GROUP BY
    d.difficult_level 
ORDER BY
    correct_rate ASC
22:56

select 
     difficult_level,
     sum(case when result = ‘right’ then 1 else 0 end)/count(*) as correct_rate
from 
question_practice_detail as qpd
left join 
user_profile as u
on 
u.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

order by correct_rate
1注意写的时候如果都和中间的表有关,就得让中间的表left join其他的两个表
2注意count是统计行数,并不是计算,如果要计算的话,需要使用sum函数计算列里面的值!!

50:46

SELECT tmp.difficult_level, AVG(IF(tmp.result = 'right', 1, 0)) AS correct_rate FROM (SELECT qpd.question_id, qpd.result, qd.difficult_level, qpd.date     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 up.university = '浙江大学') AS tmp     GROUP BY tmp.difficult_level;  

这个怎么改啊?用例就是一个过不去,而且是顺序问题,这种该怎么处理啊?

14:02

题目:现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况,请取出相应数据,并按照准确率升序输出。             示例: 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                                                                                                                                                                                                                                                                                                                                                                     马上挑战                                                         算法知识视频讲解                                                                                                                                                                                                      提交运行                                                         算法知识视频讲解                                                                                                                                                                                             添加笔记                                                                                                                                                   求解答(0)                                                                                                               邀请回答                                                                                      收藏(1250)                                                                                                           分享                                                                                                      提交结果有问题?                                                                                                                                                                                                                                                                                                       316个回答                          120篇题解                                                                   添加回答                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 17                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           Qiuxj                                                                                                                                                                                 SELECT difficult_level, AVG(IF(result='right',1,0)) AS correct_rate FROM user_profile u, question_practice_detail qpd, question_detail qd WHERE u.device_id = qpd.device_id AND qpd.question_id = qd.question_id AND university='浙江大学' GROUP BY difficult_level ORDER BY correct_rate;                                                                                                                                                                                                                                                                                                                                             发表于  2021-09-29 22:58:04                                                                                                                                                                                                    回复(4)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        12                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           老狗丶                                                                                                                                                                                 select difficult_level, (sum(case when qpd.result = "right" then 1 else 0 end)/count(u.answer_cnt)) as correct_rate from user_profile u inner join question_practice_detail qpd on u.device_id = qpd.device_id inner join question_detail qd on qd.question_id = qpd.question_id  where university = "浙江大学" group by difficult_level order by correct_rate;                                                                                                                                                                                                                                                                                                                                             发表于  2021-08-28 16:11:28                                                                                                                                                                                                    回复(9)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        4                                                                                                                                                                                                                                                                                                                                                                                         智能路障                                                                                                                                                                                   这题考察的是多表查询,只要把条件梳理到位就可迎刃而解   select       difficult_level, count(if(result = 'right', 1, null)) / count(result) as correct_rate from user_profile t1,      question_practice_detail t2,      question_detail t3 where t1.device_id = t2.device_id and t2.question_id = t3.question_id and university = '浙江大学' group by difficult_level order by correct_rate                                                                                                                                                                                                                                                                                                                                              发表于  2021-10-31 11:17:26                                                                                                                                                                                                    回复(2)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        4                                                                                                                                                                                                                                                                                                                                                                                         fire-light-guo                                                                                                                                                                                 select      d.difficult_level,     sum(if(result = 'right', 1, 0)) / count(*) as correct_rate from      user_profile u, question_practice_detail qp, question_detail d where      u.university = '浙江大学'      and u.device_id = qp.device_id     and qp.question_id = d.question_id group by d.difficult_level order by correct_rate      1. 做个三表关联    2. 按问题难度分组        3. sum()嵌套if()计算出正确的答题数量,再除以答题总数                                                                                                                                                                                                                                                                                                                                                 发表于  2021-10-13 20:45:23                                                                                                                                                                                                    回复(2)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        7                                                                                                                                                                                                                                                                                                                                                                                         Baily1234                                                                                                                                                                                 SELECT         difficult_level,     SUM(IF(result = "wrong", 0, 1)) / count(*) AS correct_rate  FROM     question_practice_detail q     LEFT JOIN user_profile u ON q.device_id = u.device_id     LEFT JOIN question_detail q2 ON q.question_id = q2.question_id WHERE     university = "浙江大学" GROUP BY     difficult_level ORDER BY      correct_rate;                                                                                                                                                                                                                                                                                                                                          发表于  2021-10-15 16:22:28                                                                                                                                                                                                    回复(2)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        3                                                                                                                                                                                                                                                                                                                                                                                         茵桃小蚊子                                                                                                                                                                                   没有上一题难...    现在习惯写代码前开始用加粗关键词和初步处理方法先捋一遍题目,然后就写得很顺手呢。如下:           现在运营想要了解浙江大学(where)的用户在不同难度题目(group by)下答题的正确率(正确数/题数)情况,请取出相应数据,并按照准确率升序(order by asc)输出。        SELECT qd.difficult_level as difficult_level, sum(if(qpd.result="right",1,0))/ COUNT(qpd.result) as correct_rate FROM user_profile u RIGHT JOIN question_practice_detail qpd ON (u.device_id = qpd.device_id) LEFT JOIN question_detail qd ON (qd.question_id = qpd.question_id) WHERE u.university = "浙江大学" GROUP BY qd.difficult_level ORDER BY correct_rate ASC                                                                                                                                                                                                                                                                                                                                                 发表于  2021-11-25 21:05:25                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           其实是牛哥                                                                                                                                                                                 select c.difficult_level, count(if(result='right',a.question_id,null)) / count(a.question_id) as correct_rate from   (select device_id,question_id,result from question_practice_detail )a   join  (select device_id from user_profile where university = '浙江大学' )b on a.device_id = b.device_id   left join ( select question_id,difficult_level from question_detail )c   on a.question_id = c.question_id group by difficult_level order by correct_rate                                                                                                                                                                                                                                                                                                                                             发表于  2021-08-27 10:57:13                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           YonChun                                                                                                                                                                                 select difficult_level, count(if(result='right',1,null)) / count(result) as correct_rate from user_profile up join question_practice_detail using(device_id) join question_detail using(question_id) where university='浙江大学' group by difficult_level order by correct_rate  应该是最精简的了吧                                                                                                                                                                                                                                                                                                                                          发表于  2022-01-31 11:04:12                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           牛客597002486号                                                                                                                                                                                 select difficult_level, (count(if(result='right',1,null)) / count(a.question_id)) as correct_rate   from   question_practice_detail a   join (select device_id from user_profile where university='浙江大学') b on a.device_id=b.device_id   join question_detail c on a.question_id=c.question_id   group by difficult_level   order by correct_rate                                                                                                                                                                                                                                                                                                                                            发表于  2021-10-03 00:47:26                                                                                                                                                                                                    回复(4)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           369186344                                                                                                                                                                                 select difficult_level, (count(case when t2.result = 'right' then 1 else null end)/ count(t2.question_id))correct_rate from (select qp.device_id,question_id,result from  question_practice_detail qp where qp.device_id in  (select device_id from user_profile up where university = '浙江大学'))t2 join question_detail qd on t2.question_id = qd.question_id group by difficult_level order by correct_rate;                                                                                                                                                                                                                                                                                                                                             发表于  2022-04-22 23:01:00                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                         黑白配XY                                                                                                                                                                                 select difficult_level,    count(if(result='right',result,null))/ count(result)   from user_profile u   join   (select device_id,difficult_level,result   from question_practice_detail q1   join   question_detail q2   on q1.question_id = q2.question_id) t   on u.device_id = t.device_id and university = '浙江大学'   group by difficult_level                                                                                                                                                                                                                                                                                                                                            发表于  2022-04-18 16:25:48                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           牛客228167738号                                                                                                                                                                                 SELECT     d.difficult_level,     count(if(q.result="right", 1, null)) / count(*) AS correct_rate FROM     user_profile AS u     INNER JOIN question_practice_detail AS q ON u.device_id = q.device_id     INNER JOIN question_detail AS d ON q.question_id = d.question_id WHERE     u.university = "浙江大学" GROUP BY     d.difficult_level ORDER BY     correct_rate ASC;                                                                                                                                                                                                                                                                                                                                          发表于  2022-04-16 23:45:52                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           牛客398096850号                                                                                                                                                                                   有没有哪位大神帮我看下我的none是哪步错了吗         SELECT Q.difficult_level     ,COUNT(CASE WHEN P.result = 'right' THEN 1 ELSE NULL END)/COUNT(P.question_id) AS correct_rate FROM  (     (     SELECT device_id         ,university     FROM user_profile     WHERE university = '浙江大学'     )U      LEFT JOIN     (     SELECT device_id         ,question_id         ,result     FROM question_practice_detail     )P     ON P.device_id = U.device_id          LEFT JOIN     (     SELECT question_id         ,difficult_level     FROM question_detail     )Q     ON Q.question_id = P.question_id ) GROUP BY Q.difficult_level ORDER BY correct_rate                                                                                                                                                                                                                                                                                                                                                 发表于  2022-04-05 17:35:03                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           柒小漠                                                                                                                                                                                 select difficult_level,    count(case when result ='right' then 1 else null end)/ count(question_detail.question_id) as rightrate   from question_practice_detail    join user_profile on question_practice_detail.device_id=user_profile.device_id   join question_detail on question_practice_detail.question_id=question_detail.question_id   where university = '浙江大学'   group by difficult_level   order by rightrate                                                                                                                                                                                                                                                                                                                                            发表于  2022-03-29 14:55:59                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           Fayuan                                                                                                                                                                                 select   t3.difficult_level,sum(case t2.result when 'right' then 1 else 0 end) / count(1) as 'correct_rate'   from user_profile t1 join question_practice_detail t2 on t1.device_id=t2.device_id   join question_detail t3 on t2.question_id=t3.question_id   where t1.university='浙江大学'   group by t3.difficult_level   order by correct_rate                                                                                                                                                                                                                                                                                                                                             发表于  2022-03-22 15:07:27                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                         XXCC111111                                                                                                                                                                                   1、学位为浙江大学,为条件;2、不同难度水平的,就表示可能需要根据难度水平来分组,使用group by函数;3、答题正确率用sum(if(qpd.result = 'right', 1, 0))/count(*) 来计算          select difficult_level,   sum(if(qpd.result = 'right', 1, 0))/count(*) as correct_rate   from question_practice_detail as qpd   left join user_profile as up   on qpd.device_id = up.device_id   left join question_detail as qd   on qpd.question_id = qd.question_id   where university = '浙江大学'   group by qd.difficult_level   order by correct_rate;                                                                                                                                                                                                                                                                                                                                                   发表于  2022-03-03 13:22:17                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           柒鳴飞                                                                                                                                                                                 select difficult_level, sum(if(result='right',1,0))/count(qd.question_id) 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 university = '浙江大学'  group by difficult_level order by correct_rate                                                                                                                                                                                                                                                                                                                                              发表于  2021-12-14 16:48:23                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           牛客689071742号                                                                                                                                                                                 SELECT        d.difficult_level,        sum(        IF        ( q.result = 'right', 1, 0 ))/ COUNT(*) AS correct_rate    FROM        question_practice_detail AS q        INNER JOIN user_profile AS u ON u.device_id = q.device_id         AND u.university = '浙江大学'        LEFT JOIN question_detail AS d ON q.question_id = d.question_id    GROUP BY        d.difficult_level    ORDER BY        correct_rate ASC                                                                                                                                                                                                                                                                                                                                            发表于  2021-11-20 11:22:56                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           键盘盘盘盘                                                                                                                                                                                 select         difficult_level,        sum(case when result = 'right' then 1 else 0 end)/count(*) as correct_rate   from    question_practice_detail as qpd   left join    user_profile as u   on    u.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     order by correct_rate          1注意写的时候如果都和中间的表有关,就得让中间的表left join其他的两个表    2注意count是统计行数,并不是计算,如果要计算的话,需要使用sum函数计算列里面的值!!                                                                                                                                                                                                                                                                                                                                           发表于  2021-11-08 14:50:46                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           码农矿工小付                                                                                                                                                                                 SELECT tmp.difficult_level, AVG(IF(tmp.result = 'right', 1, 0)) AS correct_rate FROM (SELECT qpd.question_id, qpd.result, qd.difficult_level, qpd.date     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 up.university = '浙江大学') AS tmp     GROUP BY tmp.difficult_level;   这个怎么改啊?用例就是一个过不去,而且是顺序问题,这种该怎么处理啊?                                                                                                                                                                                                                                                                                                                                          发表于  2021-08-25 15:14:02                                                                                                                                                                                                    回复(7)                                                                                                                                                                                                                                                                                                                                                                                                 这道题你会答吗?花几分钟告诉大家答案吧!                                                                                                                                                                                                                                                                                       提交观点

这道题你会答吗?花几分钟告诉大家答案吧!

SELECT difficult_level, AVG(IF(result='right',1,0)) AS correct_rate FROM user_profile u, question_practice_detail qpd, question_detail qd WHERE u.device_id = qpd.device_id AND qpd.question_id = qd.question_id AND university='浙江大学' GROUP BY difficult_level ORDER BY correct_rate;

58:04

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

11:28
这题考察的是多表查询,只要把条件梳理到位就可迎刃而解
select       difficult_level, count(if(result = 'right', 1, null)) / count(result) as correct_rate from user_profile t1,      question_practice_detail t2,      question_detail t3 where t1.device_id = t2.device_id and t2.question_id = t3.question_id and university = '浙江大学' group by difficult_level order by correct_rate

17:26
select      d.difficult_level,     sum(if(result = 'right', 1, 0)) / count(*) as correct_rate from      user_profile u, question_practice_detail qp, question_detail d where      u.university = '浙江大学'      and u.device_id = qp.device_id     and qp.question_id = d.question_id group by d.difficult_level order by correct_rate   
1. 做个三表关联
2. 按问题难度分组
    3. sum()嵌套if()计算出正确的答题数量,再除以答题总数

45:23
SELECT         difficult_level,     SUM(IF(result = "wrong", 0, 1)) / count(*) AS correct_rate  FROM     question_practice_detail q     LEFT JOIN user_profile u ON q.device_id = u.device_id     LEFT JOIN question_detail q2 ON q.question_id = q2.question_id WHERE     university = "浙江大学" GROUP BY     difficult_level ORDER BY      correct_rate;

22:28
没有上一题难…
现在习惯写代码前开始用加粗关键词初步处理方法先捋一遍题目,然后就写得很顺手呢。如下:
现在运营想要了解浙江大学(where)的用户在不同难度题目(group by)下答题的正确率(正确数/题数)情况,请取出相应数据,并按照准确率升序(order by asc)输出。

SELECT qd.difficult_level as difficult_level, sum(if(qpd.result="right",1,0))/ COUNT(qpd.result) as correct_rate FROM user_profile u RIGHT JOIN question_practice_detail qpd ON (u.device_id = qpd.device_id) LEFT JOIN question_detail qd ON (qd.question_id = qpd.question_id) WHERE u.university = "浙江大学" GROUP BY qd.difficult_level ORDER BY correct_rate ASC

05:25

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

57:13

select difficult_level, count(if(result='right',1,null)) / count(result) as correct_rate from user_profile up join question_practice_detail using(device_id) join question_detail using(question_id) where university='浙江大学' group by difficult_level order by correct_rate 

应该是最精简的了吧

04:12

select difficult_level, (count(if(result=’right’,1,null)) / count(a.question_id)) as correct_rate
from
question_practice_detail a
join (select device_id from user_profile where university=’浙江大学’) b on a.device_id=b.device_id
join question_detail c on a.question_id=c.question_id
group by difficult_level
order by correct_rate
47:26

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

01:00
select difficult_level, 
count(if(result=’right’,result,null))/ count(result)
from user_profile u
join
(select device_id,difficult_level,result
from question_practice_detail q1
join
question_detail q2
on q1.question_id = q2.question_id) t
on u.device_id = t.device_id and university = ‘浙江大学’
group by difficult_level
25:48

SELECT     d.difficult_level,     count(if(q.result="right", 1, null)) / count(*) AS correct_rate FROM     user_profile AS u     INNER JOIN question_practice_detail AS q ON u.device_id = q.device_id     INNER JOIN question_detail AS d ON q.question_id = d.question_id WHERE     u.university = "浙江大学" GROUP BY     d.difficult_level ORDER BY     correct_rate ASC;

45:52

有没有哪位大神帮我看下我的none是哪步错了吗
题目:现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况,请取出相应数据,并按照准确率升序输出。             示例: 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                                                                                                                                                                                                                                                                                                                                                                     马上挑战                                                         算法知识视频讲解                                                                                                                                                                                                      提交运行                                                         算法知识视频讲解                                                                                                                                                                                             添加笔记                                                                                                                                                   求解答(0)                                                                                                               邀请回答                                                                                      收藏(1250)                                                                                                           分享                                                                                                      提交结果有问题?                                                                                                                                                                                                                                                                                                       316个回答                          120篇题解                                                                   添加回答                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 17                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           Qiuxj                                                                                                                                                                                 SELECT difficult_level, AVG(IF(result='right',1,0)) AS correct_rate FROM user_profile u, question_practice_detail qpd, question_detail qd WHERE u.device_id = qpd.device_id AND qpd.question_id = qd.question_id AND university='浙江大学' GROUP BY difficult_level ORDER BY correct_rate;                                                                                                                                                                                                                                                                                                                                             发表于  2021-09-29 22:58:04                                                                                                                                                                                                    回复(4)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        12                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           老狗丶                                                                                                                                                                                 select difficult_level, (sum(case when qpd.result = "right" then 1 else 0 end)/count(u.answer_cnt)) as correct_rate from user_profile u inner join question_practice_detail qpd on u.device_id = qpd.device_id inner join question_detail qd on qd.question_id = qpd.question_id  where university = "浙江大学" group by difficult_level order by correct_rate;                                                                                                                                                                                                                                                                                                                                             发表于  2021-08-28 16:11:28                                                                                                                                                                                                    回复(9)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        4                                                                                                                                                                                                                                                                                                                                                                                         智能路障                                                                                                                                                                                   这题考察的是多表查询,只要把条件梳理到位就可迎刃而解   select       difficult_level, count(if(result = 'right', 1, null)) / count(result) as correct_rate from user_profile t1,      question_practice_detail t2,      question_detail t3 where t1.device_id = t2.device_id and t2.question_id = t3.question_id and university = '浙江大学' group by difficult_level order by correct_rate                                                                                                                                                                                                                                                                                                                                              发表于  2021-10-31 11:17:26                                                                                                                                                                                                    回复(2)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        4                                                                                                                                                                                                                                                                                                                                                                                         fire-light-guo                                                                                                                                                                                 select      d.difficult_level,     sum(if(result = 'right', 1, 0)) / count(*) as correct_rate from      user_profile u, question_practice_detail qp, question_detail d where      u.university = '浙江大学'      and u.device_id = qp.device_id     and qp.question_id = d.question_id group by d.difficult_level order by correct_rate      1. 做个三表关联    2. 按问题难度分组        3. sum()嵌套if()计算出正确的答题数量,再除以答题总数                                                                                                                                                                                                                                                                                                                                                 发表于  2021-10-13 20:45:23                                                                                                                                                                                                    回复(2)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        7                                                                                                                                                                                                                                                                                                                                                                                         Baily1234                                                                                                                                                                                 SELECT         difficult_level,     SUM(IF(result = "wrong", 0, 1)) / count(*) AS correct_rate  FROM     question_practice_detail q     LEFT JOIN user_profile u ON q.device_id = u.device_id     LEFT JOIN question_detail q2 ON q.question_id = q2.question_id WHERE     university = "浙江大学" GROUP BY     difficult_level ORDER BY      correct_rate;                                                                                                                                                                                                                                                                                                                                          发表于  2021-10-15 16:22:28                                                                                                                                                                                                    回复(2)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        3                                                                                                                                                                                                                                                                                                                                                                                         茵桃小蚊子                                                                                                                                                                                   没有上一题难...    现在习惯写代码前开始用加粗关键词和初步处理方法先捋一遍题目,然后就写得很顺手呢。如下:           现在运营想要了解浙江大学(where)的用户在不同难度题目(group by)下答题的正确率(正确数/题数)情况,请取出相应数据,并按照准确率升序(order by asc)输出。        SELECT qd.difficult_level as difficult_level, sum(if(qpd.result="right",1,0))/ COUNT(qpd.result) as correct_rate FROM user_profile u RIGHT JOIN question_practice_detail qpd ON (u.device_id = qpd.device_id) LEFT JOIN question_detail qd ON (qd.question_id = qpd.question_id) WHERE u.university = "浙江大学" GROUP BY qd.difficult_level ORDER BY correct_rate ASC                                                                                                                                                                                                                                                                                                                                                 发表于  2021-11-25 21:05:25                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        3                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           其实是牛哥                                                                                                                                                                                 select c.difficult_level, count(if(result='right',a.question_id,null)) / count(a.question_id) as correct_rate from   (select device_id,question_id,result from question_practice_detail )a   join  (select device_id from user_profile where university = '浙江大学' )b on a.device_id = b.device_id   left join ( select question_id,difficult_level from question_detail )c   on a.question_id = c.question_id group by difficult_level order by correct_rate                                                                                                                                                                                                                                                                                                                                             发表于  2021-08-27 10:57:13                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           YonChun                                                                                                                                                                                 select difficult_level, count(if(result='right',1,null)) / count(result) as correct_rate from user_profile up join question_practice_detail using(device_id) join question_detail using(question_id) where university='浙江大学' group by difficult_level order by correct_rate  应该是最精简的了吧                                                                                                                                                                                                                                                                                                                                          发表于  2022-01-31 11:04:12                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        2                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           牛客597002486号                                                                                                                                                                                 select difficult_level, (count(if(result='right',1,null)) / count(a.question_id)) as correct_rate   from   question_practice_detail a   join (select device_id from user_profile where university='浙江大学') b on a.device_id=b.device_id   join question_detail c on a.question_id=c.question_id   group by difficult_level   order by correct_rate                                                                                                                                                                                                                                                                                                                                            发表于  2021-10-03 00:47:26                                                                                                                                                                                                    回复(4)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           369186344                                                                                                                                                                                 select difficult_level, (count(case when t2.result = 'right' then 1 else null end)/ count(t2.question_id))correct_rate from (select qp.device_id,question_id,result from  question_practice_detail qp where qp.device_id in  (select device_id from user_profile up where university = '浙江大学'))t2 join question_detail qd on t2.question_id = qd.question_id group by difficult_level order by correct_rate;                                                                                                                                                                                                                                                                                                                                             发表于  2022-04-22 23:01:00                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                         黑白配XY                                                                                                                                                                                 select difficult_level,    count(if(result='right',result,null))/ count(result)   from user_profile u   join   (select device_id,difficult_level,result   from question_practice_detail q1   join   question_detail q2   on q1.question_id = q2.question_id) t   on u.device_id = t.device_id and university = '浙江大学'   group by difficult_level                                                                                                                                                                                                                                                                                                                                            发表于  2022-04-18 16:25:48                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           牛客228167738号                                                                                                                                                                                 SELECT     d.difficult_level,     count(if(q.result="right", 1, null)) / count(*) AS correct_rate FROM     user_profile AS u     INNER JOIN question_practice_detail AS q ON u.device_id = q.device_id     INNER JOIN question_detail AS d ON q.question_id = d.question_id WHERE     u.university = "浙江大学" GROUP BY     d.difficult_level ORDER BY     correct_rate ASC;                                                                                                                                                                                                                                                                                                                                          发表于  2022-04-16 23:45:52                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           牛客398096850号                                                                                                                                                                                   有没有哪位大神帮我看下我的none是哪步错了吗         SELECT Q.difficult_level     ,COUNT(CASE WHEN P.result = 'right' THEN 1 ELSE NULL END)/COUNT(P.question_id) AS correct_rate FROM  (     (     SELECT device_id         ,university     FROM user_profile     WHERE university = '浙江大学'     )U      LEFT JOIN     (     SELECT device_id         ,question_id         ,result     FROM question_practice_detail     )P     ON P.device_id = U.device_id          LEFT JOIN     (     SELECT question_id         ,difficult_level     FROM question_detail     )Q     ON Q.question_id = P.question_id ) GROUP BY Q.difficult_level ORDER BY correct_rate                                                                                                                                                                                                                                                                                                                                                 发表于  2022-04-05 17:35:03                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           柒小漠                                                                                                                                                                                 select difficult_level,    count(case when result ='right' then 1 else null end)/ count(question_detail.question_id) as rightrate   from question_practice_detail    join user_profile on question_practice_detail.device_id=user_profile.device_id   join question_detail on question_practice_detail.question_id=question_detail.question_id   where university = '浙江大学'   group by difficult_level   order by rightrate                                                                                                                                                                                                                                                                                                                                            发表于  2022-03-29 14:55:59                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           Fayuan                                                                                                                                                                                 select   t3.difficult_level,sum(case t2.result when 'right' then 1 else 0 end) / count(1) as 'correct_rate'   from user_profile t1 join question_practice_detail t2 on t1.device_id=t2.device_id   join question_detail t3 on t2.question_id=t3.question_id   where t1.university='浙江大学'   group by t3.difficult_level   order by correct_rate                                                                                                                                                                                                                                                                                                                                             发表于  2022-03-22 15:07:27                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                         XXCC111111                                                                                                                                                                                   1、学位为浙江大学,为条件;2、不同难度水平的,就表示可能需要根据难度水平来分组,使用group by函数;3、答题正确率用sum(if(qpd.result = 'right', 1, 0))/count(*) 来计算          select difficult_level,   sum(if(qpd.result = 'right', 1, 0))/count(*) as correct_rate   from question_practice_detail as qpd   left join user_profile as up   on qpd.device_id = up.device_id   left join question_detail as qd   on qpd.question_id = qd.question_id   where university = '浙江大学'   group by qd.difficult_level   order by correct_rate;                                                                                                                                                                                                                                                                                                                                                   发表于  2022-03-03 13:22:17                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           柒鳴飞                                                                                                                                                                                 select difficult_level, sum(if(result='right',1,0))/count(qd.question_id) 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 university = '浙江大学'  group by difficult_level order by correct_rate                                                                                                                                                                                                                                                                                                                                              发表于  2021-12-14 16:48:23                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           牛客689071742号                                                                                                                                                                                 SELECT        d.difficult_level,        sum(        IF        ( q.result = 'right', 1, 0 ))/ COUNT(*) AS correct_rate    FROM        question_practice_detail AS q        INNER JOIN user_profile AS u ON u.device_id = q.device_id         AND u.university = '浙江大学'        LEFT JOIN question_detail AS d ON q.question_id = d.question_id    GROUP BY        d.difficult_level    ORDER BY        correct_rate ASC                                                                                                                                                                                                                                                                                                                                            发表于  2021-11-20 11:22:56                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           键盘盘盘盘                                                                                                                                                                                 select         difficult_level,        sum(case when result = 'right' then 1 else 0 end)/count(*) as correct_rate   from    question_practice_detail as qpd   left join    user_profile as u   on    u.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     order by correct_rate          1注意写的时候如果都和中间的表有关,就得让中间的表left join其他的两个表    2注意count是统计行数,并不是计算,如果要计算的话,需要使用sum函数计算列里面的值!!                                                                                                                                                                                                                                                                                                                                           发表于  2021-11-08 14:50:46                                                                                                                                                                                                    回复(0)                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                        1                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                 Mysql                                                                                                                                                                                                                                           码农矿工小付                                                                                                                                                                                 SELECT tmp.difficult_level, AVG(IF(tmp.result = 'right', 1, 0)) AS correct_rate FROM (SELECT qpd.question_id, qpd.result, qd.difficult_level, qpd.date     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 up.university = '浙江大学') AS tmp     GROUP BY tmp.difficult_level;   这个怎么改啊?用例就是一个过不去,而且是顺序问题,这种该怎么处理啊?                                                                                                                                                                                                                                                                                                                                          发表于  2021-08-25 15:14:02                                                                                                                                                                                                    回复(7)                                                                                                                                                                                                                                                                                                                                                                                                 这道题你会答吗?花几分钟告诉大家答案吧!                                                                                                                                                                                                                                                                                       提交观点
SELECT Q.difficult_level     ,COUNT(CASE WHEN P.result = 'right' THEN 1 ELSE NULL END)/COUNT(P.question_id) AS correct_rate FROM  (     (     SELECT device_id         ,university     FROM user_profile     WHERE university = '浙江大学'     )U      LEFT JOIN     (     SELECT device_id         ,question_id         ,result     FROM question_practice_detail     )P     ON P.device_id = U.device_id          LEFT JOIN     (     SELECT question_id         ,difficult_level     FROM question_detail     )Q     ON Q.question_id = P.question_id ) GROUP BY Q.difficult_level ORDER BY correct_rate

35:03

select difficult_level, 
count(case when result =’right’ then 1 else null end)/ count(question_detail.question_id) as rightrate
from question_practice_detail 
join user_profile on question_practice_detail.device_id=user_profile.device_id
join question_detail on question_practice_detail.question_id=question_detail.question_id
where university = ‘浙江大学’
group by difficult_level
order by rightrate
55:59

select   t3.difficult_level,sum(case t2.result when ‘right’ then 1 else 0 end) / count(1) as ‘correct_rate’
from user_profile t1 join question_practice_detail t2 on t1.device_id=t2.device_id
join question_detail t3 on t2.question_id=t3.question_id
where t1.university=’浙江大学’
group by t3.difficult_level
order by correct_rate 
07:27
1、学位为浙江大学,为条件;2、不同难度水平的,就表示可能需要根据难度水平来分组,使用group by函数;3、答题正确率用sum(if(qpd.result = ‘right’, 1, 0))/count(*) 来计算
select difficult_level,
sum(if(qpd.result = ‘right’, 1, 0))/count(*) as correct_rate
from question_practice_detail as qpd
left join user_profile as up
on qpd.device_id = up.device_id
left join question_detail as qd
on qpd.question_id = qd.question_id
where university = ‘浙江大学’
group by qd.difficult_level
order by correct_rate;

22:17

select difficult_level, sum(if(result='right',1,0))/count(qd.question_id) 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 university = '浙江大学'  group by difficult_level order by correct_rate 

48:23

SELECT
    d.difficult_level,
    sum(
    IF
    ( q.result = ‘right’, 1, 0 ))/ COUNT(*) AS correct_rate 
FROM
    question_practice_detail AS q
    INNER JOIN user_profile AS u ON u.device_id = q.device_id 
    AND u.university = ‘浙江大学’
    LEFT JOIN question_detail AS d ON q.question_id = d.question_id 
GROUP BY
    d.difficult_level 
ORDER BY
    correct_rate ASC
22:56

select 
     difficult_level,
     sum(case when result = ‘right’ then 1 else 0 end)/count(*) as correct_rate
from 
question_practice_detail as qpd
left join 
user_profile as u
on 
u.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

order by correct_rate
1注意写的时候如果都和中间的表有关,就得让中间的表left join其他的两个表
2注意count是统计行数,并不是计算,如果要计算的话,需要使用sum函数计算列里面的值!!

50:46

SELECT tmp.difficult_level, AVG(IF(tmp.result = 'right', 1, 0)) AS correct_rate FROM (SELECT qpd.question_id, qpd.result, qd.difficult_level, qpd.date     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 up.university = '浙江大学') AS tmp     GROUP BY tmp.difficult_level;  

这个怎么改啊?用例就是一个过不去,而且是顺序问题,这种该怎么处理啊?

14:02

以上就是关于问题题目:现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况,请取出相应数据,并按照准确率升序输出。
示例: 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 马上挑战 算法知识视频讲解
提交运行 算法知识视频讲解 添加笔记 求解答(0) 邀请回答 收藏(1250) 分享 提交结果有问题? 316个回答 120篇题解 添加回答 17 Mysql Qiuxj SELECT difficult_level, AVG(IF(result=’right’,1,0)) AS correct_rate FROM user_profile u, question_practice_detail qpd, question_detail qd WHERE u.device_id = qpd.device_id AND qpd.question_id = qd.question_id AND university=’浙江大学’ GROUP BY difficult_level ORDER BY correct_rate;
发表于 2021-09-29 22:58:04 回复(4) 12 Mysql 老狗丶 select difficult_level, (sum(case when qpd.result = "right" then 1 else 0 end)/count(u.answer_cnt)) as correct_rate from user_profile u inner join question_practice_detail qpd on u.device_id = qpd.device_id inner join question_detail qd on qd.question_id = qpd.question_id  where university = "浙江大学" group by difficult_level order by correct_rate;
发表于 2021-08-28 16:11:28 回复(9) 4 智能路障 这题考察的是多表查询,只要把条件梳理到位就可迎刃而解 select       difficult_level, count(if(result = ‘right’, 1, null)) / count(result) as correct_rate from user_profile t1,      question_practice_detail t2,      question_detail t3 where t1.device_id = t2.device_id and t2.question_id = t3.question_id and university = ‘浙江大学’ group by difficult_level order by correct_rate
发表于 2021-10-31 11:17:26 回复(2) 4 fire-light-guo select      d.difficult_level,     sum(if(result = ‘right’, 1, 0)) / count(*) as correct_rate from      user_profile u, question_practice_detail qp, question_detail d where      u.university = ‘浙江大学’      and u.device_id = qp.device_id     and qp.question_id = d.question_id group by d.difficult_level order by correct_rate 1. 做个三表关联 2. 按问题难度分组     3. sum()嵌套if()计算出正确的答题数量,再除以答题总数
发表于 2021-10-13 20:45:23 回复(2) 7 Baily1234 SELECT difficult_level, SUM(IF(result = "wrong", 0, 1)) / count(*) AS correct_rate FROM question_practice_detail q LEFT JOIN user_profile u ON q.device_id = u.device_id LEFT JOIN question_detail q2 ON q.question_id = q2.question_id WHERE university = "浙江大学" GROUP BY difficult_level ORDER BY correct_rate; 发表于 2021-10-15 16:22:28 回复(2) 3 茵桃小蚊子 没有上一题难… 现在习惯写代码前开始用加粗关键词和初步处理方法先捋一遍题目,然后就写得很顺手呢。如下: 现在运营想要了解浙江大学(where)的用户在不同难度题目(group by)下答题的正确率(正确数/题数)情况,请取出相应数据,并按照准确率升序(order by asc)输出。
SELECT qd.difficult_level as difficult_level, sum(if(qpd.result="right",1,0))/ COUNT(qpd.result) as correct_rate FROM user_profile u RIGHT JOIN question_practice_detail qpd ON (u.device_id = qpd.device_id) LEFT JOIN question_detail qd ON (qd.question_id = qpd.question_id) WHERE u.university = "浙江大学" GROUP BY qd.difficult_level ORDER BY correct_rate ASC

发表于 2021-11-25 21:05:25 回复(0) 3 Mysql 其实是牛哥 select c.difficult_level, count(if(result=’right’,a.question_id,null)) / count(a.question_id) as correct_rate from (select device_id,question_id,result from question_practice_detail )a join (select device_id from user_profile where university = ‘浙江大学’ )b on a.device_id = b.device_id left join ( select question_id,difficult_level from question_detail )c on a.question_id = c.question_id group by difficult_level order by correct_rate
发表于 2021-08-27 10:57:13 回复(0) 2 Mysql YonChun select difficult_level, count(if(result=’right’,1,null)) / count(result) as correct_rate from user_profile up join question_practice_detail using(device_id) join question_detail using(question_id) where university=’浙江大学’ group by difficult_level order by correct_rate 应该是最精简的了吧 发表于 2022-01-31 11:04:12 回复(0) 2 Mysql 牛客597002486号 select difficult_level, (count(if(result=’right’,1,null)) / count(a.question_id)) as correct_rate
from
question_practice_detail a
join (select device_id from user_profile where university=’浙江大学’) b on a.device_id=b.device_id
join question_detail c on a.question_id=c.question_id
group by difficult_level
order by correct_rate
发表于 2021-10-03 00:47:26 回复(4) 1 Mysql 369186344 select difficult_level, (count(case when t2.result = ‘right’ then 1 else null end)/ count(t2.question_id))correct_rate from (select qp.device_id,question_id,result from  question_practice_detail qp where qp.device_id in  (select device_id from user_profile up where university = ‘浙江大学’))t2 join question_detail qd on t2.question_id = qd.question_id group by difficult_level order by correct_rate;
发表于 2022-04-22 23:01:00 回复(0) 1 黑白配XY select difficult_level, 
count(if(result=’right’,result,null))/ count(result)
from user_profile u
join
(select device_id,difficult_level,result
from question_practice_detail q1
join
question_detail q2
on q1.question_id = q2.question_id) t
on u.device_id = t.device_id and university = ‘浙江大学’
group by difficult_level
发表于 2022-04-18 16:25:48 回复(0) 1 Mysql 牛客228167738号 SELECT d.difficult_level, count(if(q.result="right", 1, null)) / count(*) AS correct_rate FROM user_profile AS u INNER JOIN question_practice_detail AS q ON u.device_id = q.device_id INNER JOIN question_detail AS d ON q.question_id = d.question_id WHERE u.university = "浙江大学" GROUP BY d.difficult_level ORDER BY correct_rate ASC; 发表于 2022-04-16 23:45:52 回复(0) 1 Mysql 牛客398096850号 有没有哪位大神帮我看下我的none是哪步错了吗
SELECT Q.difficult_level     ,COUNT(CASE WHEN P.result = ‘right’ THEN 1 ELSE NULL END)/COUNT(P.question_id) AS correct_rate FROM  (     (     SELECT device_id         ,university     FROM user_profile     WHERE university = ‘浙江大学’     )U     LEFT JOIN     (     SELECT device_id         ,question_id         ,result     FROM question_practice_detail     )P     ON P.device_id = U.device_id          LEFT JOIN     (     SELECT question_id         ,difficult_level     FROM question_detail     )Q     ON Q.question_id = P.question_id ) GROUP BY Q.difficult_level ORDER BY correct_rate

发表于 2022-04-05 17:35:03 回复(0) 1 Mysql 柒小漠 select difficult_level, 
count(case when result =’right’ then 1 else null end)/ count(question_detail.question_id) as rightrate
from question_practice_detail 
join user_profile on question_practice_detail.device_id=user_profile.device_id
join question_detail on question_practice_detail.question_id=question_detail.question_id
where university = ‘浙江大学’
group by difficult_level
order by rightrate
发表于 2022-03-29 14:55:59 回复(0) 1 Mysql Fayuan select   t3.difficult_level,sum(case t2.result when ‘right’ then 1 else 0 end) / count(1) as ‘correct_rate’
from user_profile t1 join question_practice_detail t2 on t1.device_id=t2.device_id
join question_detail t3 on t2.question_id=t3.question_id
where t1.university=’浙江大学’
group by t3.difficult_level
order by correct_rate 
发表于 2022-03-22 15:07:27 回复(0) 1 XXCC111111 1、学位为浙江大学,为条件;2、不同难度水平的,就表示可能需要根据难度水平来分组,使用group by函数;3、答题正确率用sum(if(qpd.result = ‘right’, 1, 0))/count(*) 来计算
select difficult_level,
sum(if(qpd.result = ‘right’, 1, 0))/count(*) as correct_rate
from question_practice_detail as qpd
left join user_profile as up
on qpd.device_id = up.device_id
left join question_detail as qd
on qpd.question_id = qd.question_id
where university = ‘浙江大学’
group by qd.difficult_level
order by correct_rate;

发表于 2022-03-03 13:22:17 回复(0) 1 Mysql 柒鳴飞 select difficult_level, sum(if(result=’right’,1,0))/count(qd.question_id) 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 university = ‘浙江大学’  group by difficult_level order by correct_rate 
发表于 2021-12-14 16:48:23 回复(0) 1 Mysql 牛客689071742号 SELECT
     d.difficult_level,
     sum(
     IF
     ( q.result = ‘right’, 1, 0 ))/ COUNT(*) AS correct_rate 
FROM
     question_practice_detail AS q
     INNER JOIN user_profile AS u ON u.device_id = q.device_id 
     AND u.university = ‘浙江大学’
     LEFT JOIN question_detail AS d ON q.question_id = d.question_id 
GROUP BY
     d.difficult_level 
ORDER BY
     correct_rate ASC
发表于 2021-11-20 11:22:56 回复(0) 1 Mysql 键盘盘盘盘 select 
     difficult_level,
     sum(case when result = ‘right’ then 1 else 0 end)/count(*) as correct_rate
from 
question_practice_detail as qpd
left join 
user_profile as u
on 
u.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
order by correct_rate
1注意写的时候如果都和中间的表有关,就得让中间的表left join其他的两个表 2注意count是统计行数,并不是计算,如果要计算的话,需要使用sum函数计算列里面的值!! 发表于 2021-11-08 14:50:46 回复(0) 1 Mysql 码农矿工小付 SELECT tmp.difficult_level, AVG(IF(tmp.result = ‘right’, 1, 0)) AS correct_rate FROM (SELECT qpd.question_id, qpd.result, qd.difficult_level, qpd.date     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 up.university = ‘浙江大学’) AS tmp     GROUP BY tmp.difficult_level; 这个怎么改啊?用例就是一个过不去,而且是顺序问题,这种该怎么处理啊? 发表于 2021-08-25 15:14:02 回复(7) 这道题你会答吗?花几分钟告诉大家答案吧! 提交观点的答案

欢迎关注区块链毕设网-
专业区块链毕业设计成品源码,定制。

区块链NFT链游项目方科学家脚本开发培训

承接区块链项目定制开发

微信:btc9767

QQ :1330797917

TELEGRAM: BTCOK9

承接区块链项目定制开发


qklbishe.com区块链毕设代做网专注|以太坊fabric-计算机|java|毕业设计|代做平台-javagopython毕设 » 题目:现在运营想要了解浙江大学的用户在不同难度题目下答题的正确率情况,请取出相应数据,并按照准确率升序输出。 示例: 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 马上挑战 算法知识视频讲解 提交运行 算法知识视频讲解 添加笔记 求解答(0) 邀请回答 收藏(1250) 分享 提交结果有问题? 316个回答 120篇题解 添加回答 17 Mysql Qiuxj SELECT difficult_level, AVG(IF(result=’right’,1,0)) AS correct_rate FROM user_profile u, question_practice_detail qpd, question_detail qd WHERE u.device_id = qpd.device_id AND qpd.question_id = qd.question_id AND university=’浙江大学’ GROUP BY difficult_level ORDER BY correct_rate; 发表于 2021-09-29 22:58:04 回复(4) 12 Mysql 老狗丶 select difficult_level, (sum(case when qpd.result = "right" then 1 else 0 end)/count(u.answer_cnt)) as correct_rate from user_profile u inner join question_practice_detail qpd on u.device_id = qpd.device_id inner join question_detail qd on qd.question_id = qpd.question_id  where university = "浙江大学" group by difficult_level order by correct_rate; 发表于 2021-08-28 16:11:28 回复(9) 4 智能路障 这题考察的是多表查询,只要把条件梳理到位就可迎刃而解 select       difficult_level, count(if(result = ‘right’, 1, null)) / count(result) as correct_rate from user_profile t1,      question_practice_detail t2,      question_detail t3 where t1.device_id = t2.device_id and t2.question_id = t3.question_id and university = ‘浙江大学’ group by difficult_level order by correct_rate 发表于 2021-10-31 11:17:26 回复(2) 4 fire-light-guo select      d.difficult_level,     sum(if(result = ‘right’, 1, 0)) / count(*) as correct_rate from      user_profile u, question_practice_detail qp, question_detail d where      u.university = ‘浙江大学’      and u.device_id = qp.device_id     and qp.question_id = d.question_id group by d.difficult_level order by correct_rate 1. 做个三表关联 2. 按问题难度分组     3. sum()嵌套if()计算出正确的答题数量,再除以答题总数 发表于 2021-10-13 20:45:23 回复(2) 7 Baily1234 SELECT difficult_level, SUM(IF(result = "wrong", 0, 1)) / count(*) AS correct_rate FROM question_practice_detail q LEFT JOIN user_profile u ON q.device_id = u.device_id LEFT JOIN question_detail q2 ON q.question_id = q2.question_id WHERE university = "浙江大学" GROUP BY difficult_level ORDER BY correct_rate; 发表于 2021-10-15 16:22:28 回复(2) 3 茵桃小蚊子 没有上一题难… 现在习惯写代码前开始用加粗关键词和初步处理方法先捋一遍题目,然后就写得很顺手呢。如下: 现在运营想要了解浙江大学(where)的用户在不同难度题目(group by)下答题的正确率(正确数/题数)情况,请取出相应数据,并按照准确率升序(order by asc)输出。 SELECT qd.difficult_level as difficult_level, sum(if(qpd.result="right",1,0))/ COUNT(qpd.result) as correct_rate FROM user_profile u RIGHT JOIN question_practice_detail qpd ON (u.device_id = qpd.device_id) LEFT JOIN question_detail qd ON (qd.question_id = qpd.question_id) WHERE u.university = "浙江大学" GROUP BY qd.difficult_level ORDER BY correct_rate ASC 发表于 2021-11-25 21:05:25 回复(0) 3 Mysql 其实是牛哥 select c.difficult_level, count(if(result=’right’,a.question_id,null)) / count(a.question_id) as correct_rate from (select device_id,question_id,result from question_practice_detail )a join (select device_id from user_profile where university = ‘浙江大学’ )b on a.device_id = b.device_id left join ( select question_id,difficult_level from question_detail )c on a.question_id = c.question_id group by difficult_level order by correct_rate 发表于 2021-08-27 10:57:13 回复(0) 2 Mysql YonChun select difficult_level, count(if(result=’right’,1,null)) / count(result) as correct_rate from user_profile up join question_practice_detail using(device_id) join question_detail using(question_id) where university=’浙江大学’ group by difficult_level order by correct_rate 应该是最精简的了吧 发表于 2022-01-31 11:04:12 回复(0) 2 Mysql 牛客597002486号 select difficult_level, (count(if(result=’right’,1,null)) / count(a.question_id)) as correct_rate from question_practice_detail a join (select device_id from user_profile where university=’浙江大学’) b on a.device_id=b.device_id join question_detail c on a.question_id=c.question_id group by difficult_level order by correct_rate 发表于 2021-10-03 00:47:26 回复(4) 1 Mysql 369186344 select difficult_level, (count(case when t2.result = ‘right’ then 1 else null end)/ count(t2.question_id))correct_rate from (select qp.device_id,question_id,result from  question_practice_detail qp where qp.device_id in  (select device_id from user_profile up where university = ‘浙江大学’))t2 join question_detail qd on t2.question_id = qd.question_id group by difficult_level order by correct_rate; 发表于 2022-04-22 23:01:00 回复(0) 1 黑白配XY select difficult_level,  count(if(result=’right’,result,null))/ count(result) from user_profile u join (select device_id,difficult_level,result from question_practice_detail q1 join question_detail q2 on q1.question_id = q2.question_id) t on u.device_id = t.device_id and university = ‘浙江大学’ group by difficult_level 发表于 2022-04-18 16:25:48 回复(0) 1 Mysql 牛客228167738号 SELECT d.difficult_level, count(if(q.result="right", 1, null)) / count(*) AS correct_rate FROM user_profile AS u INNER JOIN question_practice_detail AS q ON u.device_id = q.device_id INNER JOIN question_detail AS d ON q.question_id = d.question_id WHERE u.university = "浙江大学" GROUP BY d.difficult_level ORDER BY correct_rate ASC; 发表于 2022-04-16 23:45:52 回复(0) 1 Mysql 牛客398096850号 有没有哪位大神帮我看下我的none是哪步错了吗 SELECT Q.difficult_level     ,COUNT(CASE WHEN P.result = ‘right’ THEN 1 ELSE NULL END)/COUNT(P.question_id) AS correct_rate FROM  (     (     SELECT device_id         ,university     FROM user_profile     WHERE university = ‘浙江大学’     )U     LEFT JOIN     (     SELECT device_id         ,question_id         ,result     FROM question_practice_detail     )P     ON P.device_id = U.device_id          LEFT JOIN     (     SELECT question_id         ,difficult_level     FROM question_detail     )Q     ON Q.question_id = P.question_id ) GROUP BY Q.difficult_level ORDER BY correct_rate 发表于 2022-04-05 17:35:03 回复(0) 1 Mysql 柒小漠 select difficult_level,  count(case when result =’right’ then 1 else null end)/ count(question_detail.question_id) as rightrate from question_practice_detail  join user_profile on question_practice_detail.device_id=user_profile.device_id join question_detail on question_practice_detail.question_id=question_detail.question_id where university = ‘浙江大学’ group by difficult_level order by rightrate 发表于 2022-03-29 14:55:59 回复(0) 1 Mysql Fayuan select   t3.difficult_level,sum(case t2.result when ‘right’ then 1 else 0 end) / count(1) as ‘correct_rate’ from user_profile t1 join question_practice_detail t2 on t1.device_id=t2.device_id join question_detail t3 on t2.question_id=t3.question_id where t1.university=’浙江大学’ group by t3.difficult_level order by correct_rate  发表于 2022-03-22 15:07:27 回复(0) 1 XXCC111111 1、学位为浙江大学,为条件;2、不同难度水平的,就表示可能需要根据难度水平来分组,使用group by函数;3、答题正确率用sum(if(qpd.result = ‘right’, 1, 0))/count(*) 来计算 select difficult_level, sum(if(qpd.result = ‘right’, 1, 0))/count(*) as correct_rate from question_practice_detail as qpd left join user_profile as up on qpd.device_id = up.device_id left join question_detail as qd on qpd.question_id = qd.question_id where university = ‘浙江大学’ group by qd.difficult_level order by correct_rate; 发表于 2022-03-03 13:22:17 回复(0) 1 Mysql 柒鳴飞 select difficult_level, sum(if(result=’right’,1,0))/count(qd.question_id) 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 university = ‘浙江大学’  group by difficult_level order by correct_rate  发表于 2021-12-14 16:48:23 回复(0) 1 Mysql 牛客689071742号 SELECT      d.difficult_level,      sum(      IF      ( q.result = ‘right’, 1, 0 ))/ COUNT(*) AS correct_rate  FROM      question_practice_detail AS q      INNER JOIN user_profile AS u ON u.device_id = q.device_id       AND u.university = ‘浙江大学’      LEFT JOIN question_detail AS d ON q.question_id = d.question_id  GROUP BY      d.difficult_level  ORDER BY      correct_rate ASC 发表于 2021-11-20 11:22:56 回复(0) 1 Mysql 键盘盘盘盘 select       difficult_level,      sum(case when result = ‘right’ then 1 else 0 end)/count(*) as correct_rate from  question_practice_detail as qpd left join  user_profile as u on  u.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 order by correct_rate 1注意写的时候如果都和中间的表有关,就得让中间的表left join其他的两个表 2注意count是统计行数,并不是计算,如果要计算的话,需要使用sum函数计算列里面的值!! 发表于 2021-11-08 14:50:46 回复(0) 1 Mysql 码农矿工小付 SELECT tmp.difficult_level, AVG(IF(tmp.result = ‘right’, 1, 0)) AS correct_rate FROM (SELECT qpd.question_id, qpd.result, qd.difficult_level, qpd.date     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 up.university = ‘浙江大学’) AS tmp     GROUP BY tmp.difficult_level; 这个怎么改啊?用例就是一个过不去,而且是顺序问题,这种该怎么处理啊? 发表于 2021-08-25 15:14:02 回复(7) 这道题你会答吗?花几分钟告诉大家答案吧! 提交观点

发表评论