报表求大虾优化或解决方案

 

private String getSQL(String strDate,String endDate){
		String sql = "select t.arlncd,t.fleetcd,sum(t.flightnum) flightnum,sum(t.incountry) incountry,sum(t.international) international,"
		             +" sum(t.transport) transport,sum(t.freight) freight,sum(t.pilotnum) pilotnum,sum(t.capnum) capnum,sum(t.capflytime) capflytime,"
		             +" sum(t.fonum) fonum,sum(t.foflytime) foflytime,sum(t.groupnum) groupnum,sum(t.mttime) mttime,sum(t.motime) motime,"
		             +" sum(t.segtime) segtime,sum(t.bttime) bttime,sum(t.botime) botime"
        +" from (select substr(fd.arlncd, 1, 3) arlncd,fd.fleetcd fleetcd,0 flightNum,0 incountry,0 international,case when fd.flighttype = 'B/W' or fd.flighttype = 'C/B' or"
	  			+" fd.flighttype = 'F/H' or fd.flighttype = 'W/Z' or fd.flighttype = 'Z/P' or fd.flighttype = 'Z/X' then fd.flytime else 0 end transport,"
	  			+" case when fd.flighttype = 'H/G' or fd.flighttype = 'H/Y' then fd.flytime else 0 end freight,0 pilotNum,0 capNum,0 capFlyTime,"
	  			+" 0 foNum,0 foFlyTime,0 groupnum,0 mtTime,0 moTime,0 segTime,0 btTime,0 boTime from pilotduty pd left join flyduty fd on pd.flyduty_id = fd.id"
	  			+" where fd.flighttype in ('H/G', 'H/Y', 'B/W', 'C/B', 'F/H', 'W/Z', 'Z/P', 'Z/X') and fd.strdt between to_date('"+strDate+"','YYYY-MM-DD') and to_date('"+endDate+"','YYYY-MM-DD')" 
	  			+" union all select substr(fd.arlncd, 1, 3) arlncd, fd.fleetcd fleetcd,0 flightNum,0 incountry,0 international,"
	  			+" 0 transport,0 freight,0 pilotNum,0 capNum,sum(decode(pd.identify, 'CAP', pd.flytime, 0)) capFlyTime,0 foNum,"
	  			+" sum(decode(pd.identify, 'F/O', pd.flytime, 'S/O', pd.flytime, 0)) foFlyTime,0 groupnum,0 mtTime,0 moTime,0 segTime,0 btTime,0 boTime"
  			+" from flyduty fd left join pilotduty pd on pd.flyduty_id = fd.id where (pd.identify = 'CAP' or pd.identify = 'F/O' or pd.identify = 'S/O')"
  			+" and fd.arlncd is not null and fd.fleetcd is not null and fd.strdt between to_date('"+strDate+"','YYYY-MM-DD') and to_date('"+endDate+"','YYYY-MM-DD')" 
  			+"  group by fd.arlncd, fd.fleetcd having fd.arlncd is not null"
  			+" union all"
  			+" select p.arlcd,p.fleetcd,0 flightNum,0 incountry,0 international,0 transport,0 freight,count(*) pilotNum,0 capNum,0 capFlyTime,0 foNum,"
  			+" 0 foFlyTime,0 groupnum,0 mtTime,0 moTime,0 segTime,0 btTime,0 boTime from (select substr(f.arlncd, 1, 3) arlcd, f.fleetcd"
  			+" from flyduty f where f.arlncd is not null and f.fleetcd is not null group by f.arlncd, f.fleetcd) p left join (select"
  			+" substr(registration_base_code,-4,3) arlcd,pt.fleet from post pt"
  			+" left join subranktype s on pt.subranktype_id = s.id left join person pn on pt.person_id = pn.id where type = '注册岗位'"
  			+" and s.fd_ind = '飞行员' and pn.deleted = 0 and pt.effdt <= to_date('"+endDate+"', 'YYYY-MM-DD') and (pt.expdt is null or pt.expdt>to_date('"+strDate+"', 'YYYY-MM-DD'))"
  			+" and pt.fleet is not null and pn.registration_base_code is not null) t on t.arlcd = p.arlcd"
  			+" where t.fleet like '%' || p.fleetcd || '%' group by p.arlcd, p.fleetcd"
  			+" union all"    
  			+" select p.arlcd, p.fleetcd,0 flightNum,0 incountry,0 international,0 transport,0 freight,0 pilotNum,count(*) capNum,0 capFlyTime,"
  			+" 0 foNum,0 foFlyTime,0 groupnum,0 mtTime,0 moTime,0 segTime,0 btTime,0 boTime from (select substr(f.arlncd, 1, 3) arlcd, f.fleetcd"
  			+" from flyduty f where f.arlncd is not null and f.fleetcd is not null group by f.arlncd, f.fleetcd) p left join (select"
  			+" substr(registration_base_code,-4,3) arlcd,pt.fleet from post pt left join subranktype s on pt.subranktype_id = s.id"
  			+" left join person pn on pt.person_id = pn.id where pt.type = '注册岗位' and s.rank_cd = 'CAP' or s.rank_cd = 'CAPT' and pn.deleted = 0"
  			+" and pt.effdt <= to_date('"+endDate+"', 'YYYY-MM-DD') and (pt.expdt is null or pt.expdt > to_date('"+strDate+"', 'YYYY-MM-DD'))"
  			+" and pt.fleet is not null) t on t.arlcd = p.arlcd where t.fleet like '%' || p.fleetcd || '%' group by p.arlcd, p.fleetcd"
  			+" union all"  
  			+" select p.arlcd,p.fleetcd,0 flightNum,0 incountry,0 international,0 transport,0 freight,0 pilotNum,0 capNum,0 capFlyTime,"
  			+" count(*) foNum,0 foFlyTime,0 groupnum,0 mtTime,0 moTime,0 segTime,0 btTime,0 boTime from (select substr(f.arlncd, 1, 3) arlcd, f.fleetcd fleetcd"
  			+" from flyduty f where f.arlncd is not null and f.fleetcd is not null group by f.arlncd, f.fleetcd) p left join (select"
  			+" substr(registration_base_code,-4,3) arlcd,pt.fleet from post pt left join subranktype s on pt.subranktype_id = s.id"
  			+" left join person pn on pt.person_id = pn.id where pt.type = '注册岗位' and s.rank_cd = 'FO' and pn.deleted = 0"
  			+" and pt.effdt <= to_date('"+endDate+"', 'YYYY-MM-DD') and (pt.expdt is null or pt.expdt > to_date('"+strDate+"', 'YYYY-MM-DD')) and pt.fleet is not null) t on t.arlcd = p.arlcd"
  			+" where t.fleet like '%' || p.fleetcd || '%' group by p.arlcd, p.fleetcd"
  			+" union all"
  			+" select tp.orgunitcoding arlncd,tp.fleetcd fleetcd,0 flightNum,0 incountry,0 international,0 transport,0 freight,0 pilotNum,0 capNum,0 capFlyTime,"
  			+" 0 foNum,0 foFlyTime,count(*) groupnum,sum(tf.teachertime) mtTime,sum(tf.observerstime) moTime,0 segTime,0 btTime,0 boTime"
  			+" from trainingplan tp left join trainingproject tg on tg.id = tp.trainingproject_id left join trainingsubject ts on ts.id = tg.trainingsubject_id"
  			+" left join trainingpersoninfo tf on tf.trainingplan_id = tp.id where ts.subjecttype = '模拟机训练'"
  			+" and tf.startdate_str between '"+strDate+"' and '"+endDate+"' group by tp.orgunitcoding, tp.fleetcd"
  			+" union all"
  			+" select tp.orgunitcoding arlncd,tp.fleetcd fleetcd,0 flightNum,0 incountry,0 international,0 transport,0 freight,0 pilotNum,0 capNum,0 capFlyTime,"
  			+" 0 foNum,0 foFlyTime,0 groupnum,0 mtTime,0 moTime,0 segTime,sum(tf.teachertime) btTime,sum(tf.observerstime) boTime from trainingplan tp"
  			+" left join trainingproject tg on tg.id = tp.trainingproject_id left join trainingpersoninfo tf on tf.trainingplan_id = tp.id"
  			+" where tg.isplacetraining = '是' and tf.startdate_str between '"+strDate+"' and '"+endDate+"' group by tp.orgunitcoding, tp.fleetcd"
  			+" union all"
  			+" select fd.fleetcd fleetcd,fd.arlncd arlncd,0 flightNum,0 incountry,0 international,0 transport,0 freight,0 pilotNum,0 capNum,"
  			+" 0 capFlyTime,0 foNum,0 foFlyTime,0 groupnum,0 mtTime,0 moTime,pd.segtimes segTime,0 btTime,0 boTime from pilotduty pd"
  			+" left join flyduty fd on pd.flyduty_id = fd.id where fd.flighttype = 'K/L' and fd.strdt between to_date('"+strDate+"', 'YYYY-MM-DD') and to_date('"+endDate+"', 'YYYY-MM-DD')"
  		    +" ) t group by t.arlncd,fleetcd";
		return sql.toUpperCase();
	}