select t1.jh,
t1.TCRQ as xbrq,
t2.bj,
t2.bs,
null as yb,
t2.cw as kccw,
decode(to_char(t1.skjdds1) + '-' +to_char(t1.skjdds2) , '-',
null,
to_char(t1.skjdds1) + '-' +to_char(t1.skjdds2)) as skjd,
null as pc,
t2.scts,
t2.ycyl1,
t2.ycyl,
t2.ycql,
t2.ycql,
null as ky1,
null as ky,
null as ks,
null as xxcw,
null as dsjy
from daa01 t1 left join (select jh,
max(a.bj) as bj,
max(a.bs) as bs,
max(a.sccw)as cw,
sum(decode (mod(round((sum(scsj) * 60 + (scsj -round(scsj)) * 100) / 1440),1440) / 60 * 0.01, 0.24,
(sum(scsj) * 60 + (scsj - round(scsj)) * 100) / 1440,
(sum(scsj) * 60 + (scsj - round(scsj)) * 100) / 1440 +
mod(round((sum((scsj) * 60 + (scsj - (scsj)) * 100))), 1440) / 60) * 0.01) as scts,
sum(decode((rcyl), 0, rcyl)) +
sum(decode((rcsl), 0, rcsl)) as ycyl1,
sum(decode((round(RCYL1 -
decode(round(rcyl1 * hs / 100, 3) -
round(rcyl1 * hs / 100, 1) , 0.05,
round(rcyl1 * hs / 100, 1) + 0.1,
round(rcyl1 * hs / 100, 1)),
1)),
0,
round(RCYL1 - decode(round(rcyl1 * hs / 100, 3) -
round(rcyl1 * hs / 100, 1) , 0.05,
round(rcyl1 * hs / 100, 1) + 0.1,
round(rcyl1 * hs / 100, 1)),
1))) as ycyl,
0 + sum(decode((rcql), 0, rcql)) as ycql
from dba01 a
where to_date(a.rq, 'yyyymm') = '200909'
and jh='高40'
group by jh) t2 on t1.jh = t2.jh
where t1.jh='高40'
t1.TCRQ as xbrq,
t2.bj,
t2.bs,
null as yb,
t2.cw as kccw,
decode(to_char(t1.skjdds1) + '-' +to_char(t1.skjdds2) , '-',
null,
to_char(t1.skjdds1) + '-' +to_char(t1.skjdds2)) as skjd,
null as pc,
t2.scts,
t2.ycyl1,
t2.ycyl,
t2.ycql,
t2.ycql,
null as ky1,
null as ky,
null as ks,
null as xxcw,
null as dsjy
from daa01 t1 left join (select jh,
max(a.bj) as bj,
max(a.bs) as bs,
max(a.sccw)as cw,
sum(decode (mod(round((sum(scsj) * 60 + (scsj -round(scsj)) * 100) / 1440),1440) / 60 * 0.01, 0.24,
(sum(scsj) * 60 + (scsj - round(scsj)) * 100) / 1440,
(sum(scsj) * 60 + (scsj - round(scsj)) * 100) / 1440 +
mod(round((sum((scsj) * 60 + (scsj - (scsj)) * 100))), 1440) / 60) * 0.01) as scts,
sum(decode((rcyl), 0, rcyl)) +
sum(decode((rcsl), 0, rcsl)) as ycyl1,
sum(decode((round(RCYL1 -
decode(round(rcyl1 * hs / 100, 3) -
round(rcyl1 * hs / 100, 1) , 0.05,
round(rcyl1 * hs / 100, 1) + 0.1,
round(rcyl1 * hs / 100, 1)),
1)),
0,
round(RCYL1 - decode(round(rcyl1 * hs / 100, 3) -
round(rcyl1 * hs / 100, 1) , 0.05,
round(rcyl1 * hs / 100, 1) + 0.1,
round(rcyl1 * hs / 100, 1)),
1))) as ycyl,
0 + sum(decode((rcql), 0, rcql)) as ycql
from dba01 a
where to_date(a.rq, 'yyyymm') = '200909'
and jh='高40'
group by jh) t2 on t1.jh = t2.jh
where t1.jh='高40'
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货