SQL教你如何分析电话费？（分组汇总、常见函数）

【面试题】

有一张“电话费用表”，包含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月有话费；

select 电话号码
from 电话费用表
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）

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

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

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

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

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

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

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

2. 多表联结

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

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

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

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

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

select distinct t1.电话号码
from 
(
select 电话号码
from 电话费用表
where 月份 in (201706,201707,201708)
group by 电话号码
having count(distinct 月份) = 3
and min(月消费) >= 51
and max(月消费) <= 100
) as t1
left join 
(
select *
from 电话费用表
where 月份 in (201709,201710)
) as t2 
on t1.电话号码 = t2.电话号码
where t2.电话号码 is null or t2.月消费 = 0;

 

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

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

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

【本题考点】

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

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

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

【举一反三】

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

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

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