本人本月等超载率的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
分享到:
相关推荐
SQLMonitor2.4.3.6是一款用于实时监控SQL Server数据库性能的专业工具,它能够帮助DBA(数据库管理员)及开发人员对SQL Server环境进行深入分析,确保数据库系统的稳定运行和高效性能。在这个版本中,可能包含了一...
DataFrame提供了丰富的API,使得数据分析师和开发人员能够使用类似SQL的语法来操纵和查询数据,大大简化了数据分析工作。 在工业生产环境中,实时监测和统计分析数据对于优化生产流程、提高生产效率至关重要。传统...
5. **报警与通知**:当数据库出现异常情况,如CPU过高、连接数超限等,SQLTracker可以设置阈值报警,通过邮件或其他方式通知DBA,以便及时处理。 6. **历史记录与报告**:SQLTracker能够记录数据库的历史运行数据,...
### 运用midas Building进行超限分析基本流程指导书 #### 1. 运用midas进行超限分析基本流程简介 在超限分析的过程中,midas Building/Gen 软件扮演着重要的角色。根据给定的指导书内容,我们可以看到一个清晰的...
本教程将详述如何将Excel数据导入到SQL Server数据库,以供Myeclipse等开发工具直接使用,这对于初学者来说是一个很好的实践案例。 首先,理解Excel的基本操作是必要的。Excel是一款功能强大的电子表格软件,广泛...
《C#+SQL网上选课系统设计》是一款基于ASP.NET技术和SQL数据库开发的Web应用程序,它提供了全面的网上选课功能,适用于毕业设计、课程设计以及编程爱好者的学习实践。该系统的核心在于结合C#语言的强大编程能力和SQL...
项目报告通常会包含系统概述、需求分析、系统设计、实现过程、测试结果和总结等部分。答辩PPT则用来展示项目的主要特点、技术亮点和创新点,以便于向评审老师或同学们清晰地阐述项目内容。 总的来说,"JSP+SQL网上...
ASP+SQL网上选课管理系统是一款基于ASP技术和SQL数据库的毕业设计项目,主要针对高校学生选课过程中的需求而开发。这个系统提供了全面的功能,包括学生选课、教师排课、课程管理、成绩查询等,旨在优化教育管理流程...
5. **错误处理与验证**:在导入过程中,可能会遇到数据类型不匹配、字段长度不足等问题。通过设置错误处理规则,可以决定如何处理这些问题,如忽略、替换或停止导入。导入后,应验证数据的完整性和一致性,确保所有...
本文针对神华集团乌达矿业有限责任公司五虎山煤矿开采现状,在不考虑设备故障的情况下,找出导致瓦斯超限的基本事件,运用事故树分析方法,评价各个基本事件的结构重要度,并且根据结构重要度的不同分别采取预防措施,防止...
由于煤矿综采工作面回采工艺、回采方法以及通风方式不合理,很容易造成工作面上隅角瓦斯超限,若不采取合理有效的处理方法,不仅影响着整个工作面回采效率,而且很容易发生瓦斯事故。以西山煤电集团屯兰矿12501工作面为...
超限学习机(Extreme Learning Machine,ELM)是一种在机器学习领域中被广泛研究和应用的高效算法。它尤其在神经网络模型的训练过程中展现出了卓越的性能,尤其是在单隐藏层前馈神经网络(Single Hidden Layer ...
本项目为基于Java开发的不停车公路超限超载检测系统设计源码,包含144个文件,其中Java源文件118个,XML配置文件23个,Markdown文档2个,YAML文件1个。
超限战.exe超限战.exe
在分析提供的文件"超限数据分析.pdf"的部分内容时,我们可以观察到一系列的数据点,这些数据点似乎代表某种测量或观测的结果。数据格式看起来是固定的,包含时间戳、数值以及可能的标识符。每个数据点由多个子项组成...
《ASP+SQL网上选课管理系统》是一个典型的基于Web的教育信息化解决方案,主要应用...通过学习和理解这个系统的源代码,开发者可以深入掌握ASP编程和SQL数据库应用,对于提升自身技能和参与类似项目开发有着极大的帮助。
剪力墙结构超限高层建筑弹塑性时程分析的知识点可以分为以下几个方面: 1. 超限高层建筑的发展背景与挑战 随着我国国民经济的快速发展和城市人口的剧增,土地资源变得日益紧张,地价的上涨促使高层建筑的需求大幅...