教你如何分析电话费

【面试题】

有一张“电话费用表”，包含3个字段：电话号码（8位数）、月份、月消费。

其中，月消费为0表明该月没有产生费用。

第一行数据含义：64262631的号码在2017年11月份产生了30.6元的话费。

问题：查找2017年6、7、8月有话费，但是9、10月没有产生话费，并且6、7、8月话费均在51-100元之间的电话号码。

【解题步骤】

1. 汇总分析

首先，通过简单查询获取满足6、7、8月三个月份条件的电话号码：

1）条件一：2017年6、7、8月有话费；

1 select 电话号码
2 from 电话费用表
3 where 月份 in (201706,201707,201708);

2）条件二：6、7、8月话费均在51-100元之间。

如果直接用简单查询，就无法使得6、7、8月同时满足以上条件。所以，这里面要用到分组汇总查询。

先按电话号码分组（group by 电话号码），得到每个电话号码月话费的最小值（min）、最大值（max）。

然后对分组后的结果用having子句筛选出符合条件的数据，也就是选出3个月（having count(distinct 月份) = 3）话费均在51-100元之间（min(月消费) >= 51 and max(月消费) <= 100）

1 select 电话号码
2 from 电话费用表
3 where 月份 in (201706,201707,201708)
4 group by 电话号码
5 having count(distinct 月份) = 3
6 and min(月消费) >= 51
7 and max(月消费) <= 100;

运行SQL语句，获得查询结果（中间结果一）：

其次，获取9、10月的话费记录：

1 select *
2 from 电话费用表
3 where 月份 in (201709,201710);

运行SQL语句，获得查询结果（中间结果二）：

“中间结果二”中只有一条话费记录，满足“9、10月没有产生话费”条件的号码只有这一个吗？

不是。还有一种情况，那就是在“电话费用表”中没有的电话号码，说明也没有产生话费。

2. 多表联结

为了满足“9、10月没有产生话费”的条件，这里面有两种情况：

1）在“电话费用表”中没有9、10月的话费记录；

2）在“电话费用表”中有9、10月的话费记录，但是月消费为0。

将步骤1中的“中间结果一”与“中间结果二”联结：

为了保留在“电话费用表”中没有9、10月话费记录的电话号码，就得保留左表中的全部数据。所以选择左联结。

1 select distinct t1.电话号码
2 from
2 (
4 select 电话号码
5 from 电话费用表
6 where 月份 in (201706,201707,201708)
7 group by 电话号码
8 having count(distinct 月份) = 3
9 and min(月消费) >= 51
10 and max(月消费) <= 100
11 ) as t1
12 left join
13 (
14 select *
15 from 电话费用表
16 where 月份 in (201709,201710)
17 ) as t2
18  on t1.电话号码 = t2.电话号码
19 where t2.电话号码 is null or t2.月消费 = 0;

运行SQ语句，获得结果：

我们发现“中间结果二”就是最终结果，原因是：

1）64262701号码在“电话费用表”中没有9、10月的话费记录，说明没有产生话费；

2）64262715号码在“电话费用表”中10月消费为0，说明也没有产生话费。

【本题考点】

1）考查对分组汇总的了解，以及灵活使用来解决业务问题；

2）考查对多表联结语句的熟练程度；

3）考查对常见函数的使用：count()、min()、max()。

【举一反三】

问题：查询所有4、5月份月均使用金额在30元以上的电话号码（结果不能重复出现）。

还是考察的对分组汇总的灵活使用。

1 select 电话号码
2 from 电话费用表
3 where 月份 in (201704,201705)
4 group by 电话号码
5 having sum(月消费)/2 > 30;

思考：求4、5月份的月均使用金额为何不直接用avg() 函数？

推荐：如何从零学会SQL？