项目开发里遇到的一些统计分析、超限率等的一些sql

本人本月等超载率的sql:
本日：select t1.countycode,t1.count1 as allnumber,nvl(t2.count2,0) as overnumber,nvl(to_char(t2.count2/t1.count1*100,'990.00'),0)  as overpercent from (select t.countycode as countycode,count(*) as count1 from t_checkinfo t where     ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )   and trunc(t.checkdate)=trunc(sysdate) group by t.countycode) t1 left outer join   (select t.countycode ,count(*) as count2 from t_checkinfo t  where       ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )     and t.ispunished!=0 and trunc(t.checkdate)=trunc(sysdate)  group by t.countycode) t2   on  t1.countycode=t2.countycode
本周：select t1.countycode,t1.count1 as allnumber,nvl(t2.count2,0) as overnumber,nvl(to_char(t2.count2/t1.count1*100,'990.00'),0)  as overpercent from (select t.countycode as countycode,count(*) as count1 from t_checkinfo t  where     ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )   and t.checkdate >=(trunc(sysdate, 'd') + 1) and t.checkdate<=( trunc(sysdate, 'd') + 8) group by t.countycode) t1  left outer join  (select t.countycode ,count(*) as count2 from t_checkinfo t  where       ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )     and t.ispunished!=0 and t.checkdate >=(trunc(sysdate, 'd') + 1) and t.checkdate<=( trunc(sysdate, 'd') + 8) group by t.countycode) t2  on t1.countycode=t2.countycode
本月：select t1.countycode,t1.count1 as allnumber,nvl(t2.count2,0) as overnumber,nvl(to_char(t2.count2/t1.count1*100,'990.00'),0)  as overpercent from (select t.countycode as countycode,count(*) as count1 from t_checkinfo t where     ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )   and t.checkdate >=(trunc(sysdate, 'mm')) and t.checkdate<=last_day(trunc(sysdate))  group by t.countycode) t1   left outer join   (select t.countycode ,count(*) as count2 from t_checkinfo t  where       ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )     and t.ispunished!=0 and t.checkdate >=(trunc(sysdate, 'mm')) and t.checkdate<=last_day(trunc(sysdate)) group by t.countycode) t2   on t1.countycode=t2.countycode
本季：select t1.countycode,t1.count1 as allnumber,nvl(t2.count2,0) as overnumber,nvl(to_char(t2.count2/t1.count1*100,'990.00'),0)  as overpercent    from (select t.countycode as countycode,count(*) as count1 from t_checkinfo t  where     ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )   and t.checkdate >=(trunc(sysdate, 'Q')) and t.checkdate<=(add_months(trunc(sysdate, 'Q'), 3) - 1) group by t.countycode) t1   left outer join   (select t.countycode ,count(*) as count2 from t_checkinfo t   where       ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )     and t.ispunished!=0 and t.checkdate >=(trunc(sysdate, 'Q')) and t.checkdate<=(add_months(trunc(sysdate, 'Q'), 3) - 1) group by t.countycode) t2   on t1.countycode=t2.countycode
本年：select t1.countycode,t1.count1 as allnumber,nvl(t2.count2,0) as overnumber,nvl(to_char(t2.count2/t1.count1*100,'990.00'),0)  as overpercent from (select t.countycode as countycode,count(*) as count1 from t_checkinfo t  where     ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )   and t.checkdate >=trunc(sysdate, 'yyyy')  and t.checkdate<=(add_months(trunc(sysdate, 'yyyy'), 12) - 1) group by t.countycode) t1  left outer join  (select t.countycode ,count(*) as count2 from t_checkinfo t  where       ( t.countycode='140603' or t.countycode='140623' or t.countycode='140602' or t.countycode='140624' or t.countycode='140600' or t.countycode='140622' or t.countycode='140621' )    and t.ispunished!=0 and t.checkdate >=trunc(sysdate, 'yyyy')  and t.checkdate<=(add_months(trunc(sysdate, 'yyyy'), 12) - 1) group by t.countycode) t2   on t1.countycode=t2.countycode

----------------------------------------------------------------------------------
统计分析sql:
1. 天统计:
    所有统计：
select t1.sitecode as sitecode,t1.sitename as sitename,to_char(t1.dateperiod,'yyyy-mm-dd') as dateperiod ,t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename,trunc(t.checkdate,'dd')  as datePeriod,count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2013-12-27','yyyy-mm-dd') and  to_date('2014-01-03','yyyy-mm-dd')  group by t.sitecode,t.sitename,trunc(t.checkdate,'dd')  ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename,trunc(t.checkdate,'dd')  as datePeriod,count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2013-12-27','yyyy-mm-dd') and  to_date('2014-01-03','yyyy-mm-dd')   and t.ifoverload=2  group by t.sitecode,t.sitename ,trunc(t.checkdate,'dd') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by sitecode  ,dateperiod desc
2.周统计：
   所有的统计：
select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  group by t.sitecode,t.sitename,to_char(t.checkdate,'iw')  ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')   and t.ifoverload=2  group by t.sitecode,t.sitename ,to_char(t.checkdate,'iw') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by sitecode  ,dateperiod desc
    某个区县的统计：
select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.countycode='140602' group by t.sitecode,t.sitename,to_char(t.checkdate,'iw')  ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.countycode='140602'  and t.ifoverload=2  group by t.sitecode,t.sitename ,to_char(t.checkdate,'iw') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by sitecode  ,dateperiod desc
    某个站点的统计：
select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.sitecode='1406021001' group by t.sitecode,t.sitename,to_char(t.checkdate,'iw')  ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.sitecode='1406021001'  and t.ifoverload=2  group by t.sitecode,t.sitename ,to_char(t.checkdate,'iw') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by sitecode  ,dateperiod desc

3. 月统计，所有：
select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'mm')  as datePeriod,count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2013-11-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  group by t.sitecode,t.sitename,to_char(t.checkdate,'mm')  ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'mm')  as datePeriod,count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2013-11-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')   and t.ifoverload=2  group by t.sitecode,t.sitename ,to_char(t.checkdate,'mm') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by sitecode  ,dateperiod desc
4. 季度统计，所有的：
select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'q')  as datePeriod,count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2013-5-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  group by t.sitecode,t.sitename,to_char(t.checkdate,'q')  ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'q')  as datePeriod,count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2013-5-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')   and t.ifoverload=2  group by t.sitecode,t.sitename ,to_char(t.checkdate,'q') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by sitecode  ,dateperiod desc
5. 年统计，，所有的：
select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'yyyy')  as datePeriod,count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2011-5-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  group by t.sitecode,t.sitename,to_char(t.checkdate,'yyyy')  ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'yyyy')  as datePeriod,count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2011-5-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')   and t.ifoverload=2  group by t.sitecode,t.sitename ,to_char(t.checkdate,'yyyy') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by sitecode  ,dateperiod desc

----------------------------------------------------------------------------------

原来：时间年对比：站点数据
select site.ctrl_site_name	as city,to_char(checkdate,'yyyy') as time,  count(t.checkid) as jiancenum,       	 
sum(decode(t.ispunished,'0',0,1)) as chaozainum,   sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight))  
as totalnum,                                               	
 round(sum(decode(t.ispunished,'0',0,1)) / count(t.checkid) ,4)   * 100 as overrun   	 from 
 t_checkinfo t , t_ctrl_site site , t_organization c where 
 t.sitecode = site.ctrl_code  and c.o_orgcode = site.region_name and t.sitecode in ('1406022001') and site.region_name = '140602' and 
 t.ifcertificate='0'  and t.overpercent >= 0.1 and t.weight >= 30.0 and (to_char(t.checkdate, 'yyyy') between '2012' and  '2013') group by 
 site.ctrl_site_name,to_char(checkdate,'yyyy') order by to_char(checkdate,'yyyy');
 
 原来：区域对比：站点数据 ： sqlsite
 select site.ctrl_site_name	as city,max(to_char(checkdate,'yyyy-mm-dd')) as time,  count(t.checkid) as jiancenum,       	 
 sum(decode(t.ispunished,'0',0,1)) as chaozainum,   sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight))  
 as totalnum,               
round(sum(decode(t.ispunished,'0',0,1)) / count(t.checkid) ,4)   * 100 as overrun   	 from 
t_checkinfo t , t_ctrl_site site , t_organization c where 
t.sitecode = site.ctrl_code and c.o_orgcode = site.region_name and t.sitecode in ('1406021001') and site.region_name = '140602' and 
t.ifcertificate='0'  and t.weight >= 10.0 and (to_char(t.chkIntimestamp, 'yyyy-mm-dd') between '2014-01-05' and  '2014-01-07' 
or to_char(t.chkouttimestamp, 'yyyy-mm-dd') between '2014-01-05' and  '2014-01-07') group by site.ctrl_site_name
 
 原来：时间年对比：检测车数据
 select site.ctrl_site_name	as city,to_char(checkdate,'yyyy') as time,  count(t.checkid) as jiancenum,       	 
 sum(decode(t.ispunished,'0',0,1)) as chaozainum,   sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight))  
 as totalnum,                                          
 round(sum(decode(t.ispunished,'0',0,1)) / count(t.checkid) ,4)   * 100 as overrun   	 from 
 t_checkinfo t , t_ctrl_site site , t_organization c where 
 t.sitecode = site.ctrl_code  and c.o_orgcode = site.region_name and t.sitecode in ('1406022001') and site.region_name = '140602' and 
t.ifcertificate='0'  and t.overpercent >= 0.1 and t.weight >= 30.0 and (to_char(t.checkdate, 'yyyy') between '2012' and  '2013') group by 
site.ctrl_site_name,to_char(checkdate,'yyyy') order by to_char(checkdate,'yyyy') 
 
 原来：区域天对比：站点数据
 select site.ctrl_site_name	as city,max(to_char(checkdate,'yyyy-mm-dd')) as time,  count(t.checkid) as jiancenum,       	
 sum(decode(t.ispunished,'0',0,1)) as chaozainum,   sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight))  
 as totalnum,                                               	
 round(sum(decode(t.ispunished,'0',0,1)) / count(t.checkid) ,4)   * 100 as overrun   	 from 
 t_checkinfo t , t_ctrl_site site , t_organization c where 
 t.sitecode = site.ctrl_code and c.o_orgcode = site.region_name and t.sitecode in ('1406021001') and site.region_name = '140602' and 
 t.ifcertificate='0'  and t.weight >= 10.0 and 
 (to_char(t.chkIntimestamp, 'yyyy-mm-dd') between '2014-01-05' and  '2014-01-07' or to_char(t.chkouttimestamp, 'yyyy-mm-dd') between '2014-01-05' and  '2014-01-07') 
 group by site.ctrl_site_name
 
 天统计，所有统计(鲁最新SQL语句)
 select t1.sitecode as sitecode,t1.sitename as sitename,to_char(t1.dateperiod,'yyyy-mm-dd') as dateperiod ,t1.allnum as allnumber,
 nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  
 (select t.sitecode as sitecode,t.sitename as sitename,trunc(t.checkdate,'dd')  as datePeriod,count(*) as allnum from t_checkinfo t  where   
 t.checkdate between to_date('2013-12-27','yyyy-mm-dd') and  to_date('2014-01-03','yyyy-mm-dd')  group by 
 t.sitecode,t.sitename,trunc(t.checkdate,'dd')  ) t1  
 left outer join  
 (select t.sitecode as sitecode,t.sitename as sitename,trunc(t.checkdate,'dd')  as datePeriod,count(*) as overnum from t_checkinfo t   where   
 t.checkdate between to_date('2013-12-27','yyyy-mm-dd') and  to_date('2014-01-03','yyyy-mm-dd')   and t.ifoverload=2  group by 
 t.sitecode,t.sitename ,trunc(t.checkdate,'dd') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by 
 sitecode  ,dateperiod desc
 
 鲁，周，所有
 select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,
 nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  
 (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as allnum from t_checkinfo t  where   
 t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  group by 
 t.sitecode,t.sitename,to_char(t.checkdate,'iw')  ) t1 
 left outer join  
 (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as overnum from t_checkinfo t   where  
 t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')   and t.ifoverload=2  group by 
 t.sitecode,t.sitename ,to_char(t.checkdate,'iw') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by 
 sitecode  ,dateperiod desc
 
鲁，周，某区县
 select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,
 nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  
 (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as allnum from t_checkinfo t  where   
 t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.countycode='140602' group by 
 t.sitecode,t.sitename,to_char(t.checkdate,'iw')  ) t1  
 left outer join  
 (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as overnum from t_checkinfo t   where   
 t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.countycode='140602'  and t.ifoverload=2  group by 
 t.sitecode,t.sitename ,to_char(t.checkdate,'iw') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by 
 sitecode  ,dateperiod desc
 
 鲁，周，某站点
 select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,
 nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  
 (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as allnum from t_checkinfo t  where  
 t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.sitecode='1406021001' group by 
 t.sitecode,t.sitename,to_char(t.checkdate,'iw')  ) t1  
 left outer join  
 (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'iw')  as datePeriod,count(*) as overnum from t_checkinfo t   where   
 t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.sitecode='1406021001'  and t.ifoverload=2  group by 
 t.sitecode,t.sitename ,to_char(t.checkdate,'iw') ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by 
 sitecode  ,dateperiod desc
 
 鲁，季度，所有
 select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,
 nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  
 (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'q')  as datePeriod,count(*) as allnum from t_checkinfo t  where   
 t.checkdate between to_date('2013-5-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  group by 
 t.sitecode,t.sitename,to_char(t.checkdate,'q')  ) t1  
 left outer join
 (select t.sitecode as sitecode,t.sitename as sitename,to_char(t.checkdate,'q')  as datePeriod,count(*) as overnum from t_checkinfo t   where   
 t.checkdate between to_date('2013-5-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')   and t.ifoverload=2  group by 
 t.sitecode,t.sitename ,to_char(t.checkdate,'q') ) t2 on 
 t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by sitecode  ,dateperiod desc
 
 
 
 //按区域查询代码示例，现在有问题，稍后需要重新检查错误（这次对了）
  select t1.sitecode as sitecode,t1.sitename as sitename,t1.dateperiod  as dateperiod ,t1.allnum as allnumber,
 nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  
 (select t.sitecode as sitecode,t.sitename as sitename,max(to_char(checkdate,'yyyy-mm-dd')) as datePeriod,count(*) as allnum from t_checkinfo t  where  
 t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.sitecode='1406021001' group by 
 t.sitecode,t.sitename  ) t1  
 left outer join  
 (select t.sitecode as sitecode,t.sitename as sitename,max(to_char(checkdate,'yyyy-mm-dd')) as datePeriod,count(*) as overnum from t_checkinfo t   where   
 t.checkdate between to_date('2013-12-1','yyyy-mm-dd') and  to_date('2014-1-1','yyyy-mm-dd')  and t.sitecode='1406021001'  and t.ifoverload=2  group by 
 t.sitecode,t.sitename ) t2 
on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename;
 
 
 
 测试程序里生成的sql语句
 select t1.sitename as sitename,t1.dateperiod as dateperiod ,  t1.allnum as allnumber,  nvl(t2.overnum,0) as overnumber,  
t1.alloverweightnum as alloverweight,  nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0) as overpercent,  t1.sitecode as sitecode  from 
(select t.sitecode as sitecode,t.sitename as sitename, to_char(t.checkdate,'yyyy')  as datePeriod, count(*) as allnum, 
sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight)) as alloverweightnum from 
t_checkinfo t  where 1=1  and t.sitecode in ('1406021001') and to_char(t.checkdate, 'yyyy') between '2012' and  '2013'  
group by t.sitecode,t.sitename, to_char(t.checkdate,'yyyy')   ) t1 
 left outer join  
 (select t.sitecode as sitecode,t.sitename as sitename, to_char(t.checkdate,'yyyy')   as datePeriod,
 count(*) as overnum, sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight)) as
  alloverweightnum from t_checkinfo t   where 1=1  and t.sitecode in ('1406021001')  and  t.ifoverload=2  and 
  to_char(t.checkdate, 'yyyy') between '2012' and  '2013'  
  group by t.sitecode,t.sitename ,  to_char(t.checkdate,'yyyy')  ) t2 on 
  t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by  sitecode  ,dateperiod desc
  
  
to_char(t.checkdate, 'yyyy') between '2012' and  '2013';

select t.overweight,t.checkdate from t_checkinfo t where t.overweight is not null and t.overweight!=0 and t.ifoverload=0 order by t.checkdate;

---------------------------------------------------------------------------------

select t.*, t.rowid from t_checkinfo t
对比类型compareType  按什么时间对比compareNum  开始时间结束时间  市区city  站点类型stationType  站点stations  分析内容compareContent
总吨数weight  超载率outOverPercent  生成图形imgType

select site.ctrl_site_name	as city,to_char(checkdate,'yyyy') as time,  count(t.checkid) as jiancenum,       	 sum(decode(t.ispunished,'0',0,1)) as chaozainum,   sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight))  as totalnum,                                               	 round(sum(decode(t.ispunished,'0',0,1)) / count(t.checkid) ,4)   * 100 as overrun   	 from t_checkinfo t , t_ctrl_site site , t_organization c where t.sitecode = site.ctrl_code  and c.o_orgcode = site.region_name and t.sitecode in ('1406022001') and site.region_name = '140602' and t.ifcertificate='0'  and t.overpercent >= 0.1 and t.weight >= 30.0 and (to_char(t.checkdate, 'yyyy') between '2012' and  '2013') group by site.ctrl_site_name,to_char(checkdate,'yyyy') order by to_char(checkdate,'yyyy') 


select site.ctrl_site_name	as city,to_char(checkdate,'yyyy') as time,  count(t.checkid) as jiancenum,       	 
sum(decode(t.ispunished,'0',0,1)) as chaozainum,   sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight))  
as totalnum,                                               	
 round(sum(decode(t.ispunished,'0',0,1)) / count(t.checkid) ,4)   * 100 as overrun   	 from 
 t_checkinfo t , t_ctrl_site site , t_organization c where 
 t.sitecode = site.ctrl_code  and c.o_orgcode = site.region_name and t.sitecode in ('1406022001') and site.region_name = '140602' and 
 t.ifcertificate='0'  and t.overpercent >= 0.1 and t.weight >= 30.0 and (to_char(t.checkdate, 'yyyy') between '2012' and  '2013') group by 
 site.ctrl_site_name,to_char(checkdate,'yyyy') order by to_char(checkdate,'yyyy') 
 
t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent

select t1.sitename as sitename,t1.dateperiod as dateperiod ,  t1.allnum as allnumber,  nvl(t2.overnum,0) as overnumber,  
t1.alloverweightnum as alloverweight,  nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0) as overpercent,  t1.sitecode as sitecode  from 
(select t.sitecode as sitecode,t.sitename as sitename, to_char(t.checkdate,'yyyy')  as datePeriod, count(*) as allnum, 
sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight)) as alloverweightnum from 
t_checkinfo t  where 1=1  and t.sitecode in ('1406021001') and t.checkdate between to_date('2012','yyyy-mm-dd') and  to_date('2013','yyyy-mm-dd')  
group by t.sitecode,t.sitename, to_char(t.checkdate,'yyyy')   ) t1 
 left outer join  
 (select t.sitecode as sitecode,t.sitename as sitename, to_char(t.checkdate,'yyyy')   as datePeriod,
 count(*) as overnum, sum(decode(t.overweight,'0.00', t.overweight, '.00', t.overweight,'','0', t.overweight)) as
  alloverweightnum from t_checkinfo t   where 1=1  and t.sitecode in ('1406021001')  and  t.ifoverload=2  and 
  t.checkdate between to_date('2012','yyyy-mm-dd') and  to_date('2013','yyyy-mm-dd')  
  group by t.sitecode,t.sitename ,  to_char(t.checkdate,'yyyy')  ) t2 on 
  t1.sitecode=t2.sitecode and t1.sitename=t2.sitename and t1.dateperiod=t2.dateperiod  order by  sitecode  ,dateperiod desc 



-------------------------------------------------------------------------------

汇总报表sql:
---按照站点，一段时间内的站点总统计
select t1.sitecode as sitecode,t1.sitename as sitename 
,t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent 
 from  (select t.sitecode as sitecode,t.sitename as sitename ,count(*) as allnum from t_checkinfo t  
 where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-07','yyyy-mm-dd')  
 and t.countycode='140602' group by t.sitecode,t.sitename  ) t1  
 left outer join 
  (select t.sitecode as sitecode,t.sitename as sitename ,count(*) as overnum from t_checkinfo t   
  where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-07','yyyy-mm-dd')  and t.countycode='140602'  
  and t.ifoverload=2  group by t.sitecode,t.sitename   ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename    order by sitecode   desc
---站点统计
不选择区县，统计一段时间内所有的站点：select t1.sitecode as sitecode,t1.sitename as sitename, t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename, count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')  group by t.sitecode,t.sitename ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename, count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')   and t.ifoverload=2  group by t.sitecode,t.sitename  ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename   order by sitecode  desc
选择区县，统计一段时间内容此区县下的站点：select t1.sitecode as sitecode,t1.sitename as sitename, t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename, count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')  and t.countycode='140622' group by t.sitecode,t.sitename ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename, count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')  and t.countycode='140622'  and t.ifoverload=2  group by t.sitecode,t.sitename  ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename   order by sitecode  desc
---区县统计：
统计所有区县：select t1.sitecode as sitecode,t1.sitename as sitename, t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select t.sitecode as sitecode,t.sitename as sitename, count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')  and t.countycode='140622' group by t.sitecode,t.sitename ) t1  left outer join  (select t.sitecode as sitecode,t.sitename as sitename, count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')  and t.countycode='140622'  and t.ifoverload=2  group by t.sitecode,t.sitename  ) t2 on t1.sitecode=t2.sitecode and t1.sitename=t2.sitename   order by sitecode  desc
统计具体区县：select t1.countycode as countycode,t1.countyname as countyname, t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select countycode as countycode,t.countyname as countyname, count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')  and t.countycode='140624' group by t.countycode,t.countyname) t1  left outer join  (select countycode as countycode,t.countyname as countyname, count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')  and t.countycode='140624'  and t.ifoverload=2  group by t.countycode,t.countyname  ) t2 on t1.countycode=t2.countycode and t1.countyname=t2.countyname   order by countycode  desc
统计具体区县，带站点编号：select t1.countycode as countycode,t1.countyname as countyname, t1.allnum as allnumber,nvl(t2.overnum,0) as overnumber,nvl(to_char(t2.overnum/t1.allnum*100,'990.00'),0)  as overpercent  from  (select countycode as countycode,t.countyname as countyname, count(*) as allnum from t_checkinfo t  where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')  and t.countycode='140624' and t.sitecode='1406242020' group by t.countycode,t.countyname) t1  left outer join  (select countycode as countycode,t.countyname as countyname, count(*) as overnum from t_checkinfo t   where   t.checkdate between to_date('2014-01-01','yyyy-mm-dd') and  to_date('2014-01-08','yyyy-mm-dd')  and t.countycode='140624' and t.sitecode='1406242020'  and t.ifoverload=2  group by t.countycode,t.countyname  ) t2 on t1.countycode=t2.countycode and t1.countyname=t2.countyname   order by countycode  desc