我这是以oracle为例的.. .. ..
有些可能繁琐，但可以保证结果准确
楼上的第一题有误，只用话费多少是不可能精准定位出哪个电话号码在那几个月中花费那么多
1[0].select distinct t1.phone from
( select * from jifei
where to_date(month,'yyyymm') = to_date('2011-06','yyyy-mm') and expenses > 50 and expenses < 100 ) t1,
( select * from jifei
where to_date(month,'yyyymm') = to_date('2011-07','yyyy-mm') and expenses > 50 and expenses < 100 ) t2,
(select * from jifei
where to_date(month,'yyyymm') = to_date('2011-08','yyyy-mm') and expenses > 50 and expenses < 100 ) t3,
(
select * from jifei
where to_date(month,'yyyymm') = to_date('2011-09','yyyy-mm') and expenses = 0 ) t4,
(
select * from jifei
where to_date(month,'yyyymm') = to_date('2011-10','yyyy-mm') and expenses = 0 ) t5
where t1.phone = t2.phone
and t2.phone = t3.phone
and t3.phone = t4.phone
and t4.phone = t5.phone
1[1].select phone from
( select phone , 1 ct from jifei
where (to_date(month,'yyyymm') = to_date('2011-06','yyyy-mm')
or to_date(month,'yyyymm') = to_date('2011-07','yyyy-mm')
or to_date(month,'yyyymm') = to_date('2011-08','yyyy-mm')
and expenses > 50 and expenses < 100 )
or ( to_date(month,'yyyymm') = to_date('2011-09','yyyy-mm')
or to_date(month,'yyyymm') = to_date('2011-10','yyyy-mm') and expenses = 0 )
group by phone having sum(ct) = 5
2. select distinct phone from (
select phone ,
substr(phone,length(phone)-3,1) fst,
substr(phone,length(phone)-2,1) sec,
substr(phone,length(phone)-1,1) thr,
substr(phone,length(phone),1) fou
from jifei
where to_date(month,'yyyymm') > todate ('201101','yyyymm') - 1/24/60/60/60
and to_date(month,'yyyymm') < todate ('201111','yyyymm') + 1/24/60/60/60
)
where (fst = sec and sec = thr and thr = fou)
or (fst = sec and thr = fou )
or (fst = thr and sec = fou )
--oracle 可以直接使用rowid，并不是没有办法
3. delete from jifei where rowid in (
select max(rowid) from jifei where to_date(month,'yyyymm') = to_date('2011-10','yyyy-mm')
group by phone,month,expenses having count(*) = 2
)
--其它数据库估计可以这样，不过用一条sql写不完
create table temp as select * from jifei where to_date(month,'yyyymm') = to_date('2011-10','yyyy-mm')
group by phone,month,expenses having count(*) = 2
)
truncate table jifei;
insert into jifei select * from temp;
drop table temp;
4. select distinct t1.phone from
(select phone,month from jifei
where to_date(month,'yyyymm') = to_date('2011-09','yyyy-mm') and expenses > 30) t1,
(select phone,month from jifei
where to_date(month,'yyyymm') = to_date('2011-10','yyyy-mm') and expenses > 30) t2
where t1.phone = t2.phone