本日：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----------------------------------------------------------------------------------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 desc2.周统计： 所有的统计：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 desc3. 月统计，所有：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 desc4. 季度统计，所有的：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 desc5. 年统计，，所有的：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 生成图形imgTypeselect 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 overpercentselect 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 ----------------------------------------------------------------------------------按照站点，一段时间内的站点总统计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
详细解决方案
项目开发里碰到的一些统计分析、超限率等的一些sql
热度：55 发布时间：2016-05-05 11:48:28.0