SQL综合练习

SQL综合练习（选自牛客网）

提供三个表use_profile, question_practice_detail, question_detail

代码如下

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

现有如下问题

1.统计复旦学生8月份练题情况

2.统计浙大不同题目难度的正确率

答：第一题

我的代码

-- 查询复旦8月份刷题及正确情况

select
       up.device_id,
       up.university,
       count(qpd.question_id)as question_cnt,
       sum(if(qpd.result='right',1,0))as right_question_cnt
from question_practice_detail as qpd
inner join  user_profile as up
    on qpd.device_id = up.device_id
           and year(qpd.date)=2021 and month(qpd.date)=8
where up.university='复旦大学'

group by up.device_id

结果：

device_id	universsity	question_cnt	right_question_cnt
3214	复旦大学	3	0

大神代码

select up.device_id, '复旦大学' as university,
    count(question_id) as question_cnt,
    sum(if(qpd.result='right', 1, 0)) as right_question_cnt
from user_profile as up

left join question_practice_detail as qpd
  on qpd.device_id = up.device_id and month(qpd.date) = 8

where up.university = '复旦大学'
group by up.device_id

device_id	university	question_cnt	right_question_cnt
3214	复旦大学	3	0
4321	复旦大学	0	0

两者差距在于大神使用的连接是left join，我使用的是inner join ，两者区别如下

1.返回不同

inner join：inner join只返回两个表中联结字段相等的行。

left join：left join返回包括左表中的所有记录和右表中联结字段相等的记录。

2.数量不同

inner join：inner join的数量小于等于左表和右表中的记录数量。

left join：left join的数量以左表中的记录数量相同。

3.记录属性不同

inner join：inner join不足的记录属性会被直接舍弃。

left join：left join不足的记录属性用NULL填充。

从上表中的返回不同可以得出我出错的原因：我使用的是inner join，只能返回user_profile和question_practice_detail同时存在且联结字段相等的记录。由于device_id为4321的学生没有答题，所以在question_practice_detail中没有这个学生的记录，因此使用inner join就不会显示这个学生。

答：第二题

示例

difficult_level	correct_rate
easy	0.5000
medium	1.0000

解答：

select  qd.difficult_level,
      sum(if(qpd.result='right',1,0))/count(qpd.question_id) as correct_rate
from 
     question_practice_detail as qpd
left join question_detail as qd 
    on qpd.question_id = qd.question_id
left join user_profile as up 
    on qpd.device_id = up.device_id
where university='浙江大学'
 group by qd.difficult_level
order by correct_rate
== 结果正确

本题使用了sum，count，if函数，

易混知识点包括group后不能用where，而是having，

where后可以用group（慎用）

---

个人观点：sql语句中group by，order by等通常放在最后，同时又通常和列名有关 ✌️