提供个方法参考
with tt as(
select 'abc' c1,'a1' c2,to_date('2014-03-31 13:34:58','yyyy-mm-dd hh24:mi:ss') dd from dual union all
select 'abc','a1',to_date('2014-03-31 13:34:58','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'abc','a2',to_date('2014-03-31 15:23:34','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'abc','a3',to_date('2014-03-31 18:12:11','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'abc','a4',to_date('2014-03-31 23:34:23','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'abc','b1',to_date('2014-03-31 08:34:18','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'abc','b2',to_date('2014-03-31 11:34:53','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'abc','b2',to_date('2014-03-31 11:34:53','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'abc','b2',to_date('2014-03-31 11:34:53','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'abc','b3',to_date('2014-03-31 13:34:18','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'bcd','a1',to_date('2014-03-31 10:34:38','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'bcd','a2',to_date('2014-03-31 12:34:56','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'bcd','a3',to_date('2014-03-31 13:34:48','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'bcd','a1',to_date('2014-04-01 13:34:48','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'bcd','a2',to_date('2014-04-01 15:34:48','yyyy-mm-dd hh24:mi:ss') from dual)
select c1, to_char(dd, 'yyyy-mm-dd') d1,
count(distinct case when to_char(dd,'hh24:mi:ss')>='13:00:00' and to_char(dd,'hh24:mi:ss')<='15:30:00' then c2 end) t1,
count(distinct case when to_char(dd,'hh24:mi:ss')>='16:00:00' and to_char(dd,'hh24:mi:ss')<='18:30:00' then c2 end) t2
from tt
group by c1, to_char(dd, 'yyyy-mm-dd');
with tt as(
select 'abc' c1,'a1' c2,to_date('2014-03-31 13:34:58','yyyy-mm-dd hh24:mi:ss') dd from dual union all
select 'abc','a1',to_date('2014-03-31 13:34:58','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'abc','a2',to_date('2014-03-31 15:23:34','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'abc','a3',to_date('2014-03-31 18:12:11','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'abc','a4',to_date('2014-03-31 23:34:23','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'abc','b1',to_date('2014-03-31 08:34:18','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'abc','b2',to_date('2014-03-31 11:34:53','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'abc','b2',to_date('2014-03-31 11:34:53','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'abc','b2',to_date('2014-03-31 11:34:53','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'abc','b3',to_date('2014-03-31 13:34:18','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'bcd','a1',to_date('2014-03-31 10:34:38','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'bcd','a2',to_date('2014-03-31 12:34:56','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'bcd','a3',to_date('2014-03-31 13:34:48','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'bcd','a1',to_date('2014-04-01 13:34:48','yyyy-mm-dd hh24:mi:ss') from dual union all
select 'bcd','a2',to_date('2014-04-01 15:34:48','yyyy-mm-dd hh24:mi:ss') from dual)
select c1, to_char(dd, 'yyyy-mm-dd') d1,
count(distinct case when to_char(dd,'hh24:mi:ss')>='13:00:00' and to_char(dd,'hh24:mi:ss')<='15:30:00' then c2 end) t1,
count(distinct case when to_char(dd,'hh24:mi:ss')>='16:00:00' and to_char(dd,'hh24:mi:ss')<='18:30:00' then c2 end) t2
from tt
group by c1, to_char(dd, 'yyyy-mm-dd');
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货