盘点爱数据学院5月SQL月考中出现的问题以及解析

本次爱数据学院的同学在5月SQL月考中的完成情况：参加考试的同学有30人，其中优秀人数有3人，良好人数7人，合格人数16人，整体偏好。

以下将具体讲解在同学提交的答案中出现的问题，以及每道题的解决方法：

一、已知如下订单记录表orderlist：

1. 请根据上表同时查找出每年以及当年每个月的总实收金额（尽量简化代码）。

【解析】在本题中，同学们提交的代码主要有以下几种，我们一个一个来查看一下：

首先是第①个：

乍一看有年有月有sum计算，好像没有什么问题，但是我们仔细观察，这个代码好像并不能计算出一整年的总金额，那么我们来看一下实际运行的结果：

实际运行结果显示并不能计算出一整年的汇总数据，并且查询结果的顺序也比较凌乱，这里产生的问题就是同学们在做题的时候不仔细分析题目的意思，不仔细梳理其中的逻辑，想当然的就开始敲代码，各位同学千万不要有这样的学习习惯。

接下来是第②个：

初步看好像没有什么问题，先查询每年每个月的总实收金额，再查询每一年的总实收金额，接着再使用union来把2个查询的结果连接起来，就有了每年以及每年每个月的汇总数据了。那么我们来具实际运行一下：

报错？(问号脸.jpg) 理论上应该是没有问题的呀，这里为什么会报错呢？

原来，是由于MySQL默认开启了ONLY_FULL_GROUP_BY模式，导致我们在计算这一类包括sum函数、count函数以及avg函数等集合类计算函数的时候，如果前面有字段限制，而后面不跟上group by来运行就会无法执行，造成报错。因此，这也成了一个代码编写的规范和习惯，这一点大家一定要记住。

接下来我们继续看第③个：

话不多说，这下我们在后面跟上group by了，现在不会报错了吧？那么我们直接运行一下：

这下好像是没什么问题，年累计的总实收金额有了，月累计实收金额也有了，但是这个数据的顺序怎么那么乱？16年的跟17年的数据怎么都交错在一起了？

原来，MySQL的查询规则里，在查询结果里没有主键字段(Primary key)以及没有加order by的时候，获取的数据顺序是随机的，因此还需要加上order by来进行排序。为了是结果看起来更美观呢，也可以在最外面再查询一次，再进行排序，如下：

以上这样子写可能是大多数初学者会想到的方法，在这里还好，只是按年来计算汇总数据，那么如果有3个或者3个以上比如按年、按季度、按月来计算汇总的数据呢？按照以上方式是不是就需要3个或3个以上的表来进行连接，这样子写是不是会很麻烦？

于是，就有了with rollup。在MySQL中，with rollup是用来在计算分组统计技术的基础上再进行统计汇总，即可以用来得到group by的汇总信息。以下代码就是本题用到with rollup来进行解答，是不是很简洁呢？

我们来看一下运行结果：

有null的地方就是按年汇总的数据，同理，全是null的一行就是所有数据的累计汇总结果，你看，结果是不是既能够排序又很简洁方便？所以，这个方法技巧同学们一定要好好记住呀！这次没有用到没有关系，下次记住就可以啦~

二、已知如下销售信息表fct_sales和会员信息表dim_member：

fct_sales：

dim_member：

1. 请根据fct_sales表查询2017年7月总购买金额大于10000的会员，并按总购买金额从高到低的顺序输出。

【解析】咳咳，本道题主要考察having以及order by的用法，基本属于送分题，这里不再过多地赘述，唯一需要提的一点就是需要排除非会员的数据，在本道题中的影响不大，但是在下一道题里面如果没有排除那么就查不到想要的结果。

2. 请根据fct_sales表查询2017年8月购买金额最高的会员，以及该会员在2017年6/7/8月三个月的总消费金额、平均消费金额以及订单数。

【解析】在本道题中，主要产生的一些问题有：

①在筛选2017年8月购买金额最高的会员的时候，有的同学会用到max函数比如下面这条代码：

这部分同学这道题本该直接没有分数的，但是看着你们辛辛苦苦手写了那么长的代码，温油善良的老师实在下不了手，于是只是根据这些错的点来进行扣分。

其实本道题目跟上一道题是有一定的相关性的，甚至可以用上面的代码来进行套用，改一下时间加个条件就可以直接使用，然而好多同学都还是没有想到可以组合使用order by与limit来筛选。筛选最高的还好，那么如果是想要查询出8月总购买金额第二的会员呢？不知道同学们会写出什么花样来，看到的同学可以自己尝试一下，说不定以后会遇到这样的问题呢~

另外就是这道题在筛选购买金额最高的会员时，必须排除非会员的数据，因为这一部分的总购买金额远大于单个会员的月总购买金额，不排除非会员的数据就会导致最终结果就是这部分非会员的消费信息，如果题目要求根据dim_member查询该会员的信息，那么这样子就不会有任何结果了。

以下是本道题的参考答案，关于查询排在第二的会员，各位同学可以自行尝试一下~

3. 请根据fct_sales表和dim_member表查询2017年6月与7月都购买但是8月没有购买的会员所有信息。

【解析】这是一道非常有探讨性的题，只要大家逻辑思路正确我都没有判错，在这里我先给出一份仅供参考的答案，当然方法不限，下面会给出其他参考的方法：

提交的同学大多都想到了先查询出既在6月购买又在7月购买的会员名单，然后查找出不在8月份购买的会员名单，逻辑框架确实没有问题，但是在用代码实现却各不相同。

有的在筛选6月7月都购买的会员时，条件写的是where dimDateID between "20170601" and "20170731"，有的写的是where month(dimDateID) in (6,7)，看似没有错，但是仔细分析，有的可能在6月买了，7月不一定购买，这些是都是符合这2条代码的逻辑。然后在筛选8月份没有购买的会员名单时，用到not between "20170801" and "20170831"，当然逻辑也没有问题，但是如果把这几个条件同时放在一个where条件下面使用，就会导致查询结果为空。

还有的同学，绝对是套娃高手，环环相扣，当然逻辑都是没有问题的，但是不建议；也有的同学把思考的逻辑写在旁边了，但是没有把代码写全，说明还是对代码的练习太少了，需要多多练习。

这里放上关于使用in跟not in同时在where条件下使用的方法，但是建议是在数据量少的情况下使用，以及在not in的查询里面排除掉null的情况，否则也是不能使用的：