--初版代码
select up.device_id,
university,
count(qpd.device_id) as question_cnt,
sum(case when qpd.result = 'right' then 1 else 0 end) as right_question_cnt
from user_profile up left join question_practice_detail qpd
on up.device_id = qpd.device_id
where university = '复旦大学' and (month(date) = 8 or date is null)
group by up.device_id,university;
--优化后代码
select up.device_id,
'复旦大学',
count(qpd.device_id) as question_cnt,
sum(case when qpd.result = 'right' then 1 else 0 end) as right_question_cnt
from user_profile up left join question_practice_detail qpd
on up.device_id = qpd.device_id and month(qpd.date) = 8
where university = '复旦大学'
group by up.device_id
上述代码是我写的SQL语句(初版),我说一下自己的解题思路:
1、首先要熟悉所给两个表中的内容和逻辑,结合两个表读题发现需要进行表连接,连接条件就是两个表中的device_id,于是我把两个表连接到一块去查看它显示出来的内容,观察表数据并结合最后题目所给结果发现如果使用内连接的话,不会显示全部数据,不符合题目要求,所以需要用到左右外连接,我选择了左连接(左连接和内连接的区别我下面列出);
2、表连接完成后就需要加筛选条件了,于是where后面我加了两个条件,发现还是显示不全,意识到where是先连接后筛选,那么月份如果加到这里我的左连接岂不是白用了嘛,脑子没转过来,所以我直接把代码写死,就有了 month(date) = 8 or date is null , (之后优化了代码才有了新版本);
3、接下来就考虑分组了,因为需要显示每个用户的一个汇总信息,需要对用户分组,那么既然题目要求我显示一个university的字段,我就做了复合分组,当然也可以直接增加一个写死的列,个人感觉这块还是不写死的好;
4、第一个字段和第二个字段都显示成功了,接下来就是信息的一个汇总了,要计算用户在8月份练习的总题目数,个人感觉此处的逻辑没有表达清楚,带入现实中,我8月份刷23考研数学真题卷中的最后一道答题,我刷了5次,就隔着一道题狂刷,这到底是应该记录为刷了一道题还是五道题呢?(好像怎么理解都有道理哈),那最后肯定是要呈现出题目所给的结果,所以他题目中要表达的逻辑就是不去重,无论刷的题是不是同一道,都要记录;
5、那最后一个字段就最简单了,就是显示做对的题目数量,累加里面套了循环,我用的case when ,当然也可以用if,根据个人习惯写就好,如果该字段中值为right,那就返回1,否则返回0,循环走完,通过sum累加起来(使用case when要注意,要加else,不然就会显示null)。