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

题目:

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

数据表如下:

1. user_profile表。

Laravel

2. question_practice_detail表。

Laravel

题目分析:

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='复旦大学';

结果:

Laravel

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

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

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

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

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

  1. 在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;

结果如下:

Laravel

注意:

在求在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;

结果:

Laravel

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

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

Laravel

本作品采用《CC 协议》,转载必须注明作者和本文链接
《L01 基础入门》
我们将带你从零开发一个项目并部署到线上,本课程教授 Web 开发中专业、实用的技能,如 Git 工作流、Laravel Mix 前端工作流等。
《L05 电商实战》
从零开发一个电商项目,功能包括电商后台、商品 & SKU 管理、购物车、订单管理、支付宝支付、微信支付、订单退款流程、优惠券等
讨论数量: 0
(= ̄ω ̄=)··· 暂无内容!

讨论应以学习和精进为目的。请勿发布不友善或者负能量的内容,与人为善,比聪明更重要!