查询表aaa中bbb在2011-8-22 至2011-8-25
中 每天10:00到11:00 内的数据 以及数目
select bbb ,count(bbb) from (
select * from aaa
where
rec_date between
trunc(to_date('2011-8-22 09:00:00','yyyy-mm-dd
hh24:mi:ss'))
and
trunc(to_date('2011-8-25 09:00:00','yyyy-mm-dd
hh24:mi:ss'))
and
to_char( aaa.bbb, 'hh24:mi:ss') between '09:00:00'
and'10:00:00')
group by bbb
如果是两个不同的表,确定其结构是否相同,使用union进行比较
select bbb ,count(bbb) from (
select * from aaa
where
rec_date between
trunc(to_date('2011-8-22 09:00:00','yyyy-mm-dd hh24:mi:ss'))
and
trunc(to_date('2011-8-25 09:00:00','yyyy-mm-dd hh24:mi:ss'))
union
select * from aaa
where
to_char( aaa.bbb, 'hh24:mi:ss')between
'09:00:00' and'10:00:00')
group by bbb
