记录一道以前做过的SQL练习题

题目：

现在运营想要了解复旦大学的每个用户在8月份练习的总题目数和回答正确的题目数情况，请取出相应明细数据，对于在8月份没有练习过的用户，答题数结果返回0.

数据表如下：

1. user_profile表。

2. question_practice_detail表。

题目分析：

1. 复旦大学的每个用户：

• 复旦大学是定语，即过滤条件。

• 每个用户，需要通过device_id进行分组。

2. 在8月份练习的总题目数和回答正确的题目数：

• 在8月份是时间状语，如果不考虑题目中的最后一段话：对于在8月份没有练习过的用户，答题数结果返回0.，应该把8月份作为过滤条件之一。但是有了最后一段话，应该把8月份作为判断条件。

• 总题目数和回答正确的题目数：前面提到通过device_id进行分组，把8月份作为判断条件，就可以利用sum函数求出相应的题目总数和回答正确的题目数了。

具体过程

一、通过左联结、过滤找出所有复旦大学的用户的答题情况：

通过device_id建立左联结，把复旦大学作为过滤条件。SQL代码如下：

select

    up.device_id,

    up.university,

    qpd.question_id,

    qpd.result,

    qpd.date

from user_profile as up

left join question_practice_detail as qpd

on up.device_id=qpd.device_id

where university='复旦大学';

结果：

二、利用分组、条件判断、求和求出在8月份练习的总题目数和回答正确的题目数

通过device_id分组，把8月份作为判断条件，并进行求和。

1. 求在8月份练习的总题目数的SQL语句片段：

sum(if(month(qpd.date)=08,1,0)) as question_cnt,

即：在8月份答的题，总题目数就加1，否则就加0。

2. 求在8月份回答正确的题目数的SQL语句片段：

sum(if(month(qpd.date)=08 and qpd.result='right',1,0)) as right_question_cnt

即：月份为8月，并且结果为right才会加1，否则就加0。

完整的SQL语句：

select

  up.device_id,

  up.university,

  sum(if(month(qpd.date)=08,1,0)) as question_cnt,

  sum(if(month(qpd.date)=08 and qpd.result='right',1,0)) as right_question_cnt

from user_profile as up

left join

question_practice_detail as qpd

on up.device_id=qpd.device_id

where university='复旦大学'

group by up.device_id;

结果如下：

注意：

在求在8月份练习的总题目数时需要也需要将8月份作为判断条件，否则会把所有的回答正确的题目数都统计进来，而不管月份。

SQL语句片段如下：

sum(if(qpd.result='right',1,0)) as right_question_cnt

完整SQL语句：

select

  up.device_id,

  up.university,

  sum(if(month(qpd.date)=08,1,0)) 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 up.device_id=qpd.device_id

where university='复旦大学'

group by up.device_id;

结果：

表中的right_question_cnt字段的值，有些不为零，有些则为零，理论上都为零才对。因为8月份没有人答对过题目。

所有复旦大学的用户的答题情况：

本作品采用《CC 协议》，转载必须注明作者和本文链接