增加一个表a,将各个时间01,02,...24作为记录(共24条)插入其中然后,做外连接查询,或者将下面的子查询
select a.hr,nvl(count(id),0) from test ,
(select '01' hr from dual
union all
select '02' hr from dual
...
union all
select '24' hr from dual
) a
where a.hr = to_char(my_time,'HH24')(+)
group by a.hr
select a.hr,nvl(count(id),0) from test ,
(select '01' hr from dual
union all
select '02' hr from dual
...
union all
select '24' hr from dual
) a
where a.hr = to_char(my_time,'HH24')(+)
group by a.hr
select hr, sum(count_id) from
(
select to_char(my_time,'HH24') hr,nvl(count(id),0) count_id from test group by to_char(my_time,'HH24')
union all
select '01' hr, 0 count_id from dual
union all
select '02' hr, 0 count_id from dual
...
union all
select '24 hr, 0 count_id from dual
)
group by hr
select * from
( select * from a, b where a.no=b.no(+)) c, d
where c.id=d.id and .......
code name
00 0时至1时
01 1时至2时
02 2时至3时
......
test表
id,my_time
1 2002-01-01 00:23
2 2002-01-05 05:45
3 2002-12-23 06:30请问怎么加外联接??我的sql:
select cst.code,min(ct.name),nvl(count(t.id),0)
from test t,code_time ct
where to_char(t.my_time,'HH24')=ct.code
group by ct.codeto_char(acd_time,'HH24')(+)是不对的!!