有个表 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本人分数比较少有拉以后在加
表字段是 报警时间 报警点(某个派出所) 案件类型(如抢劫)
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 g_a_slsj,a_ajlb1 ,count(*) from (
select * from (
select (to_char(a_slsj,'hh24')) as g_a_slsj,A_AJLB1,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_AJLB1 in
(select A_AJLB1 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_AJLB1
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_AJLB1 ) )
)
) tab group by g_a_slsj,a_ajlb1
) tt where a_ajlb1 = (select a_ajlb1 from (
select g_a_slsj,a_ajlb1 ,count(*) from (
select * from (
select (to_char(a_slsj,'hh24')) as g_a_slsj,A_AJLB1,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_AJLB1 in
(select A_AJLB1 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_AJLB1
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_AJLB1 ) )
)
) tab group by g_a_slsj,a_ajlb1
) d where rownum<2 and g_a_slsj = tt.g_a_slsj)
order by g_a_slsj
SELECT HH, S, COUNT(*)
FROM (SELECT TO_CHAR(a_slsj, 'hh24') HH,
S.a_jjdw S,
S.A_AJLB1 W
FROM CASE_M_DETAIL S
WHERE TRUNC(a_slsj) = TRUNC(SYSDATE))
GROUP BY HH, S
ORDER BY 3 DESC;
FROM (SELECT a_slsj
,a_jjdw
,a_ajlb1
,row_number() over(PARTITION BY a_slsj ORDER BY a_ajlb1 DESC) rn
FROM (SELECT to_char(a_slsj, 'HH') a_slsj
,a_jjdw
,COUNT(a_ajlb1) a_ajlb1
FROM yinzhou.case_m_detail
WHERE trunc(a_slsj) = to_date('2006-12-30', 'yyyy-mm-dd')
GROUP BY to_char(a_slsj, 'HH'), a_jjdw))
WHERE rn = 1
(
select to_char(in_date,'hh24') as a_slsj,a_jjdw,count(a_ajlb1) as ajlb_count,
row_number() over(partition by to_char(a_slsj,'hh24') order by count(a_ajlb1) desc) rn
from case_m_detail
group by to_char(a_slsj,'hh24'),a_jjdw
)
where rn=1
/