对数据库里面的数据分类统计,判断电话号码(第一位不是0归类为市话,第一位为0但是第二位不为0归类为国内长途,第一第二都为0归类为国际长途),然后根据时间(每一天结算一次)汇总该类金额。
---------------------------------------------------------------
select [日期]=convert(varchar(10), 日期, 120),
[国际长途]=sum(case when SubString(号码, 1,2 )='00'
then isnull(金额, 0) else 0 end
),
[国内长途]=sum(case when SubString(号码, 1,2 )<>'00' and SubString(号码,1, 1)='0' then isnull(金额, 0) else 0 end
),
[市话]=sum(case when SubString(号码, 1, 1)<>'0' then isnull(金额, 0) else 0 end)
from table_name
group by convert(varchar(10), 日期, 120)
---------------------------------------------------------------
select 时间,sum(decode(substr(电话号码,1,1),.金额....))//或者sqlserver的case( when )
group by 时间截取到天
---------------------------------------------
select ( case when left(号码,2) = '00' then '国际长途'
when left(号码,2) <> '00' and left(号码,1) = '0' then '国内长途'
when left(号码,1) <> '0' then '市话' end ) as [分类],
sum(金额)
from yourtable
group by ( case when left(号码,2) = '00' then '国际长途'
when left(号码,2) <> '00' and left(号码,1) = '0' then '国内长途'
when left(号码,1) <> '0' then '市话' end )