with t as
( select 'A' as cwar, to_date('2015-03-01 08:05:27','yyyy-mm-dd hh24:mi:ss') as trdt, 3 as num from dual
union all select 'A', to_date('2015-03-01 08:25:37','yyyy-mm-dd hh24:mi:ss'), 2 from dual
union all select 'A', to_date('2015-03-01 08:33:15','yyyy-mm-dd hh24:mi:ss'), 3 from dual
union all select 'A', to_date('2015-03-01 09:04:09','yyyy-mm-dd hh24:mi:ss'), 5 from dual
union all select 'A', to_date('2015-03-01 10:17:06','yyyy-mm-dd hh24:mi:ss'), 4 from dual
union all select 'A', to_date('2015-03-01 10:22:09','yyyy-mm-dd hh24:mi:ss'), 1 from dual
union all select 'A', to_date('2015-03-01 11:39:00','yyyy-mm-dd hh24:mi:ss'), 3 from dual )
select t.cwar, b.trdt, sum(t.num) as num
from t,
( select level as rn, to_date('2015-03-01','yyyy-mm-dd') + (level - 1)/ 12 as trdt from dual connect by level <= 12 ) a,
( select level as rn, to_date('2015-03-01','yyyy-mm-dd') + (level)/ 12 as trdt from dual connect by level <= 12 ) b
where b.rn = a.rn
and t.trdt >= a.trdt
and t.trdt < b.trdt
group by t.cwar, b.trdt
order by 1,2
( select 'A' as cwar, to_date('2015-03-01 08:05:27','yyyy-mm-dd hh24:mi:ss') as trdt, 3 as num from dual
union all select 'A', to_date('2015-03-01 08:25:37','yyyy-mm-dd hh24:mi:ss'), 2 from dual
union all select 'A', to_date('2015-03-01 08:33:15','yyyy-mm-dd hh24:mi:ss'), 3 from dual
union all select 'A', to_date('2015-03-01 09:04:09','yyyy-mm-dd hh24:mi:ss'), 5 from dual
union all select 'A', to_date('2015-03-01 10:17:06','yyyy-mm-dd hh24:mi:ss'), 4 from dual
union all select 'A', to_date('2015-03-01 10:22:09','yyyy-mm-dd hh24:mi:ss'), 1 from dual
union all select 'A', to_date('2015-03-01 11:39:00','yyyy-mm-dd hh24:mi:ss'), 3 from dual )
select t.cwar, b.trdt, sum(t.num) as num
from t,
( select level as rn, to_date('2015-03-01','yyyy-mm-dd') + (level - 1)/ 12 as trdt from dual connect by level <= 12 ) a,
( select level as rn, to_date('2015-03-01','yyyy-mm-dd') + (level)/ 12 as trdt from dual connect by level <= 12 ) b
where b.rn = a.rn
and t.trdt >= a.trdt
and t.trdt < b.trdt
group by t.cwar, b.trdt
order by 1,2
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货