生成一个统计表,分别对时间,区域,类型,状态进行统计,生成的表格如下:
区域1 区域2 区域3
类型1 类型2 类型3 .....
状态1 状态2 状态1 状态2 状态1 状态2 .........
8-1 3 3 5 0
8-2 ...
8-3 ...
8-4 ....
8-5
注意,时间段为连续的,且字段里相应的值并没有连续,用group by 时间不行目前我写的一条以天为一次查询的一行SQL如下:
SQL="select area,count(case when type = 'Crossings' and report_time <= to_date('" + sEndDay + "','YY-MM-DD') and (CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') or CLOSE_TIME is null) then 1 end) as Crossings,count(case when type = 'Crossings' and report_time>=to_date('" + sStartDay + "','YY-MM-DD') and report_time<=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as HCrossings,count(case when type = 'Crossings' and CLOSE_TIME>=to_date('" + sStartDay + "','YY-MM-DD') and CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as SCrossings,"
+"count(case when type = 'Encroachment' and report_time <= to_date('" + sEndDay + "','YY-MM-DD') and (CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') or CLOSE_TIME is null) then 1 end) as Encroachment,count(case when type = 'Encroachment' and report_time>=to_date('" + sStartDay + "','YY-MM-DD') and report_time<=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as HEncroachment,count(case when type = 'Encroachment' and CLOSE_TIME>=to_date('" + sStartDay + "','YY-MM-DD') and CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as SEncroachment,"
+"count(case when type = 'Erosion' and report_time <= to_date('" + sEndDay + "','YY-MM-DD') and (CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') or CLOSE_TIME is null) then 1 end) as Erosion,count(case when type = 'Erosion' and report_time>=to_date('" + sStartDay + "','YY-MM-DD') and report_time<=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as HErosion,count(case when type = 'Erosion' and CLOSE_TIME>=to_date('" + sStartDay + "','YY-MM-DD') and CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as SErosion,"
+"count(case when type = 'Exposure' and report_time <= to_date('" + sEndDay + "','YY-MM-DD') and (CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') or CLOSE_TIME is null) then 1 end) as Exposure,count(case when type = 'Exposure' and report_time>=to_date('" + sStartDay + "','YY-MM-DD') and report_time<=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as HExposure,count(case when type = 'Exposure' and CLOSE_TIME>=to_date('" + sStartDay + "','YY-MM-DD') and CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as SExposure,"
+"count(case when type = 'Others' and report_time <= to_date('" + sEndDay + "','YY-MM-DD') and (CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') or CLOSE_TIME is null) then 1 end) as Others,count(case when type = 'Others' and report_time>=to_date('" + sStartDay + "','YY-MM-DD') and report_time<=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as HOthers,count(case when type = 'Others' and CLOSE_TIME>=to_date('" + sStartDay + "','YY-MM-DD') and CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as SOthers "
+" from incident group by area order by area";
区域1 区域2 区域3
类型1 类型2 类型3 .....
状态1 状态2 状态1 状态2 状态1 状态2 .........
8-1 3 3 5 0
8-2 ...
8-3 ...
8-4 ....
8-5
注意,时间段为连续的,且字段里相应的值并没有连续,用group by 时间不行目前我写的一条以天为一次查询的一行SQL如下:
SQL="select area,count(case when type = 'Crossings' and report_time <= to_date('" + sEndDay + "','YY-MM-DD') and (CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') or CLOSE_TIME is null) then 1 end) as Crossings,count(case when type = 'Crossings' and report_time>=to_date('" + sStartDay + "','YY-MM-DD') and report_time<=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as HCrossings,count(case when type = 'Crossings' and CLOSE_TIME>=to_date('" + sStartDay + "','YY-MM-DD') and CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as SCrossings,"
+"count(case when type = 'Encroachment' and report_time <= to_date('" + sEndDay + "','YY-MM-DD') and (CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') or CLOSE_TIME is null) then 1 end) as Encroachment,count(case when type = 'Encroachment' and report_time>=to_date('" + sStartDay + "','YY-MM-DD') and report_time<=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as HEncroachment,count(case when type = 'Encroachment' and CLOSE_TIME>=to_date('" + sStartDay + "','YY-MM-DD') and CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as SEncroachment,"
+"count(case when type = 'Erosion' and report_time <= to_date('" + sEndDay + "','YY-MM-DD') and (CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') or CLOSE_TIME is null) then 1 end) as Erosion,count(case when type = 'Erosion' and report_time>=to_date('" + sStartDay + "','YY-MM-DD') and report_time<=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as HErosion,count(case when type = 'Erosion' and CLOSE_TIME>=to_date('" + sStartDay + "','YY-MM-DD') and CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as SErosion,"
+"count(case when type = 'Exposure' and report_time <= to_date('" + sEndDay + "','YY-MM-DD') and (CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') or CLOSE_TIME is null) then 1 end) as Exposure,count(case when type = 'Exposure' and report_time>=to_date('" + sStartDay + "','YY-MM-DD') and report_time<=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as HExposure,count(case when type = 'Exposure' and CLOSE_TIME>=to_date('" + sStartDay + "','YY-MM-DD') and CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as SExposure,"
+"count(case when type = 'Others' and report_time <= to_date('" + sEndDay + "','YY-MM-DD') and (CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') or CLOSE_TIME is null) then 1 end) as Others,count(case when type = 'Others' and report_time>=to_date('" + sStartDay + "','YY-MM-DD') and report_time<=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as HOthers,count(case when type = 'Others' and CLOSE_TIME>=to_date('" + sStartDay + "','YY-MM-DD') and CLOSE_TIME>=to_date('" + sEndDay + "','YY-MM-DD') then 1 end) as SOthers "
+" from incident group by area order by area";
返回的表由八次查询出的结果组成? 这可以写储存过程不?