表中记录了手机欠费的情况,有三个字段
mobile_no(手机号码),date(欠费年月),money(当月欠费金额)
如:
13302710113 200307 122
现在要统计连续三个月都欠费的手机号码,以及欠费总金额
能直接利用Sql语句得到上面的结果吗?
mobile_no(手机号码),date(欠费年月),money(当月欠费金额)
如:
13302710113 200307 122
现在要统计连续三个月都欠费的手机号码,以及欠费总金额
能直接利用Sql语句得到上面的结果吗?
select mobile_no
from t
where count(*)>=3
group by mobile_no
;select mobile_no
from t
where date='200307' and mobile_no in (select mobile_no
from t
where date='200306' and mobile_no in select mobile_no
from t
where date='200305' and mobile_no in))
;
from t
where date='200307' and mobile_no in (select mobile_no
from t
where date='200306' and mobile_no in (select mobile_no
from t
where date='200305' and mobile_no in))
;
from t
where date='200307' and mobile_no in (select mobile_no
from t
where date='200306' and mobile_no in (select mobile_no
from t
where date='200305'))
;
select
a.mobile_no as 手机号码,(a.money+b.money+c.money) as 欠费总额
from
table as a,table as b,table as c
where
a.mobile_no=b.mobile_no and a.mobile_no=c.mobile_no
and a.date='200307',b.date='200306',c.date='200305'
select
a.mobile_no as 手机号码,(a.money+b.money+c.money) as 欠费总额
from
table as a,table as b,table as c
where
a.mobile_no=b.mobile_no and a.mobile_no=c.mobile_no
and a.date='200307' and b.date='200306' and c.date='200305'
提点建议:
把数据库按主关键字:手几号码,次关键字:前费年月升序排序
用存储过程或编写Delphi程序从数据开头逐条判断
用三个变量,一个记录手机号码num,一个记录欠费年月date,欠费的月数n
按如下步骤完成:
while not 表单.eof do
begin
if num=手机号码 then
if n<3
begin
if date-欠费年月=1个月 then
begin
n=n+1
if n=3 then
记录该手机号//再用一个临时表单
end
end
else
begin
n=1
num=手机号
date=欠费年月
end
到下一条纪录
end
我已经编写了一段程序,完成了上述功能
非常感谢大家对我的热情帮助!