有个表 YINZHOU.CASE_M_DETAIL
表字段是 报警时间 报警点(某个派出所) 案件类型(如抢劫)
a_slsj a_jjdw A_AJLB1
2006-12-30 15:44:00 1 a
2006-12-30 15:00:00 2 b
2006-12-30 14:30:00 4 c
2006-12-30 20:44:00 3 c
2006-12-30 15:55:00 1 a
2006-12-30 20:44:00 1 d 要求得出 2006-12-30 各小时段里 那个报警点 报警数最多,及最多那个报警数
如故同一时间最大报警数相同有多个,那随即取一个 结果如下 时间段 报警点 报警数
15 1 2
14 4 1
20 3 1
------------------------------------------------------------------------select * from (
select g_a_slsj,a_jjdw ,count(*) from (
select * from (
select (to_char(a_slsj,'hh24')) as g_a_slsj,a_jjdw,a_slsj from YINZHOU.CASE_M_DETAIL a where to_date(to_char(a_slsj,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date('2006-12-30 ','YYYY-MM-DD ')
)t where
exists( select 1 from YINZHOU.CASE_M_DETAIL b where to_date(to_char(a_slsj,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date('2006-12-30 ','YYYY-MM-DD ') and t.g_a_slsj = to_char(a_slsj,'hh24')
and t.a_jjdw in
(select a_jjdw from YINZHOU.CASE_M_DETAIL b where to_char(a_slsj,'hh24') = t.g_a_slsj and to_date(to_char(a_slsj,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date('2006-12-30 ','YYYY-MM-DD ')
group by b.a_jjdw
having count(*)=(select max(count(*)) from YINZHOU.CASE_M_DETAIL a where (to_char(a_slsj,'hh24')) = t.g_a_slsj and to_date(to_char(a_slsj,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date('2006-12-30 ','YYYY-MM-DD ')
group by a.a_jjdw ) )
)
) tab group by g_a_slsj,a_jjdw
) tt where a_jjdw = (select a_jjdw from (
select g_a_slsj,a_jjdw ,count(*) from (
select * from (
select (to_char(a_slsj,'hh24')) as g_a_slsj,a_jjdw,a_slsj from YINZHOU.CASE_M_DETAIL a where to_date(to_char(a_slsj,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date('2006-12-30 ','YYYY-MM-DD ')
)t where
exists( select 1 from YINZHOU.CASE_M_DETAIL b where to_date(to_char(a_slsj,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date('2006-12-30 ','YYYY-MM-DD ') and t.g_a_slsj = to_char(a_slsj,'hh24')
and t.a_jjdw in
(select a_jjdw from YINZHOU.CASE_M_DETAIL b where to_char(a_slsj,'hh24') = t.g_a_slsj and to_date(to_char(a_slsj,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date('2006-12-30 ','YYYY-MM-DD ')
group by b.a_jjdw
having count(*)=(select max(count(*)) from YINZHOU.CASE_M_DETAIL a where (to_char(a_slsj,'hh24')) = t.g_a_slsj and to_date(to_char(a_slsj,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date('2006-12-30 ','YYYY-MM-DD ')
group by a.a_jjdw ) )
)
) tab group by g_a_slsj,a_jjdw
) d where rownum<2 and g_a_slsj = tt.g_a_slsj)
order by g_a_slsj
表字段是 报警时间 报警点(某个派出所) 案件类型(如抢劫)
a_slsj a_jjdw A_AJLB1
2006-12-30 15:44:00 1 a
2006-12-30 15:00:00 2 b
2006-12-30 14:30:00 4 c
2006-12-30 20:44:00 3 c
2006-12-30 15:55:00 1 a
2006-12-30 20:44:00 1 d 要求得出 2006-12-30 各小时段里 那个报警点 报警数最多,及最多那个报警数
如故同一时间最大报警数相同有多个,那随即取一个 结果如下 时间段 报警点 报警数
15 1 2
14 4 1
20 3 1
------------------------------------------------------------------------select * from (
select g_a_slsj,a_jjdw ,count(*) from (
select * from (
select (to_char(a_slsj,'hh24')) as g_a_slsj,a_jjdw,a_slsj from YINZHOU.CASE_M_DETAIL a where to_date(to_char(a_slsj,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date('2006-12-30 ','YYYY-MM-DD ')
)t where
exists( select 1 from YINZHOU.CASE_M_DETAIL b where to_date(to_char(a_slsj,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date('2006-12-30 ','YYYY-MM-DD ') and t.g_a_slsj = to_char(a_slsj,'hh24')
and t.a_jjdw in
(select a_jjdw from YINZHOU.CASE_M_DETAIL b where to_char(a_slsj,'hh24') = t.g_a_slsj and to_date(to_char(a_slsj,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date('2006-12-30 ','YYYY-MM-DD ')
group by b.a_jjdw
having count(*)=(select max(count(*)) from YINZHOU.CASE_M_DETAIL a where (to_char(a_slsj,'hh24')) = t.g_a_slsj and to_date(to_char(a_slsj,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date('2006-12-30 ','YYYY-MM-DD ')
group by a.a_jjdw ) )
)
) tab group by g_a_slsj,a_jjdw
) tt where a_jjdw = (select a_jjdw from (
select g_a_slsj,a_jjdw ,count(*) from (
select * from (
select (to_char(a_slsj,'hh24')) as g_a_slsj,a_jjdw,a_slsj from YINZHOU.CASE_M_DETAIL a where to_date(to_char(a_slsj,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date('2006-12-30 ','YYYY-MM-DD ')
)t where
exists( select 1 from YINZHOU.CASE_M_DETAIL b where to_date(to_char(a_slsj,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date('2006-12-30 ','YYYY-MM-DD ') and t.g_a_slsj = to_char(a_slsj,'hh24')
and t.a_jjdw in
(select a_jjdw from YINZHOU.CASE_M_DETAIL b where to_char(a_slsj,'hh24') = t.g_a_slsj and to_date(to_char(a_slsj,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date('2006-12-30 ','YYYY-MM-DD ')
group by b.a_jjdw
having count(*)=(select max(count(*)) from YINZHOU.CASE_M_DETAIL a where (to_char(a_slsj,'hh24')) = t.g_a_slsj and to_date(to_char(a_slsj,'yyyy-mm-dd'),'yyyy-mm-dd')=to_date('2006-12-30 ','YYYY-MM-DD ')
group by a.a_jjdw ) )
)
) tab group by g_a_slsj,a_jjdw
) d where rownum<2 and g_a_slsj = tt.g_a_slsj)
order by g_a_slsj
所以,楼主还是到Oracle版去问吧,那还是有很多N人的.
我觉得主要解决方法是合理使用索引。