一个看似简单的数据分析面试问题，大家来玩玩

[qq_35529228]

一个很常见看似简单的数据分析面试问题，大家来聊聊如果是你会怎样处理。怎样才能更有效率，交流交流，哈哈。
有一个计费表 表名 jifei 字段如下： phone(8位的电话号码)， month（月份），expenses（月消费，费用为0表明该月没有产生费用） 如该表的一条记录：64262631,201011,30.6 这条记录的含义就是64262631的号码在2010年11月份产生了30.6元的话费。
写出满足下列条件的sql语句：
查找2010年6、7、8月有话费产生但9、10月没有使用并（6、7、8月话费均在51-100 元之间的用户。

[js14982]

select * from 
(select * from jifei where month in(201006,201007,201008) 
minus
select * from jifei where  month in(201009,201010))
where expenses between 51 and 100
;

[js14982]

另外还有not in 和 not exists的写法。可以满足需求，不妨一试

[js14982]

select * from 
(select * from jifei where month in(201006,201007,201008)  and expenses > 0
minus
select * from jifei where  month in(201009,201010) and expenses > 0)
where expenses between 51 and 100
;

[qq_35529228]

引用 3 楼 js14982 的回复:

select * from 
(select * from jifei where month in(201006,201007,201008)  and expenses > 0
minus
select * from jifei where  month in(201009,201010) and expenses > 0)
where expenses between 51 and 100
;

兄台，你这种写法貌似不是题目所需噢。是6，7，8均大于51，而且9，10均为0.你这种写法只要6，7，8一个满足即满足查询。

[js14982]

引用 4 楼 qq_35529228 的回复:

[quote=引用 3 楼 js14982 的回复:]

select * from 
(select * from jifei where month in(201006,201007,201008)  and expenses > 0
minus
select * from jifei where  month in(201009,201010) and expenses > 0)
where expenses between 51 and 100
;

兄台，你这种写法貌似不是题目所需噢。是6，7，8均大于51，而且9，10均为0.你这种写法只要6，7，8一个满足即满足查询。[/quote] 如果9,10月金额为0，可不可能不出现这条号码的记录？还是说必定会出现这条号码为0的记录

[js14982]

select phone from 
(select phone,count(1) cnt from 
(select * from jifei where month in(201006,201007,201008)  and expenses > 0
minus
select * from jifei where  month in(201009,201010) and expenses > 0)
where expenses between 51 and 100
group by phone)
where cnt = 3
;

再加一层嵌套查询了

[qq_35529228]

引用 5 楼 js14982 的回复:

[quote=引用 4 楼 qq_35529228 的回复:] [quote=引用 3 楼 js14982 的回复:]

select * from 
(select * from jifei where month in(201006,201007,201008)  and expenses > 0
minus
select * from jifei where  month in(201009,201010) and expenses > 0)
where expenses between 51 and 100
;

兄台，你这种写法貌似不是题目所需噢。是6，7，8均大于51，而且9，10均为0.你这种写法只要6，7，8一个满足即满足查询。[/quote] 如果9,10月金额为0，可不可能不出现这条号码的记录？还是说必定会出现这条号码为0的记录[/quote] 必定会出现一条0记录的

[js14982]

引用 7 楼 qq_35529228 的回复:

必定会出现一条0记录的

select phone from 
(select phone,count(1) cnt from 
(select * from jifei where month in(201006,201007,201008)  and expenses > 0
Intersect
select * from jifei where  month in(201009,201010) and expenses = 0)
where expenses between 51 and 100
group by phone)
where cnt = 3
;

用Intersect取并集的方法更直观一点

[覌海雲逺]

用intersect直接取这五个条件的交集

[chengccy]

with tmp as
(select '88888888' as phone, '201006' as month, 54 as expenses from dual union all 
select '88888888' as phone, '201007' as month, 80 as expenses from dual union all 
select '88888888' as phone, '201008' as month, 60 as expenses from dual union all 
select '88888888' as phone, '201009' as month, 0 as expenses from dual union all 
select '88888888' as phone, '201010' as month, 0 as expenses from dual union all 
select '77777777' as phone, '201006' as month, 54 as expenses from dual union all 
select '77777777' as phone, '201007' as month, 80 as expenses from dual union all 
select '77777777' as phone, '201008' as month, 23 as expenses from dual union all 
select '77777777' as phone, '201009' as month, 0 as expenses from dual union all 
select '77777777' as phone, '201010' as month, 0 as expenses from dual )
select phone
  from tmp t
 where month in ('201006', '201007', '201008', '201009', '201010')
   and ((month in ('201009', '201010') and expenses = 0) or
       (month in ('201006', '201007', '201008') and expenses between 51 and 100))
 group by phone
having count(1) = 5

[chengccy]

分析一下，就是3个条件： 条件1：6,7,8月都有话费产生； 条件2：9,10月没有话费； 条件3：6,7,8月话费均在51-100元之间； 条件3包含了条件1，即满足条件2,3即可。把条件2,3 做出or关系，然后通过判断满足的记录数是否等于涉及的月份数获取用户； select phone from tmp t where (month in ('201009', '201010') and expenses = 0) --条件2 or (month in ('201006', '201007', '201008') and expenses between 51 and 100) --条件3 group by phone having count(1) = 5

[qq_35529228]

大家各种方向的思考对我有诸多启发，多谢多谢

[sxq129601]

select userid from jifei t1 where exists (select 1 from (select userid from jifei where month in ('201009', '201010') and expenses = 0 union select userid from jifei where month in ('201006', '201007', '201008') and expenses between 51 and 100) t2 and t1.userid = t2.userid) and t1.month in ('201006', '201007', '201008') and t1.expenses > 0

[抓个青蛙遛一遛]

select t1.phone
from
(select phone
from jifei
where month in('201009','201010')
and expenses=0
group by phone)t1
inner join
(select phone
from jifei
where month in('201006','201007','201008')
and expenses between 51 and 100
group by phone)t2
on t1.phone=t2.phone