项目开发里碰到的一些统计分析、超限率等的一些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
----------------------------------------------------------------------------------
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
-------------------------------------------------------------------------------
---按照站点，一段时间内的站点总统计
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