select p.*, d.timetableSeq, d.data15 as data15Curr, d.pvPower as pvPowerCurr from ( select p.seq as plantSeq, p.name as plantName, p.supplyPower, min(d.data15) as data15Min, max(d.data15) as data15Max, min(d.data32) as data32, max(d.data33) as data33, min(d.data16) as data16, min(d.pvPower) as pvPowerMin, max(d.pvPower) as pvPowerMax, round(avg(d.pvPower)) as pvPowerAvg, min(d.data20) as data20Min, max(d.data20) as data20Max, min(d.data21) as data21Min, max(d.data21) as data21Max, min(d.data22) as data22Min, max(d.data22) as data22Max, min(d.data23) as data23Min, max(d.data23) as data23Max, max(d.timetableSeq) as timetableSeq, min(d.chargePower) as chargeMin, max(d.chargePower) as chargeMax, min(d.dischargePower) as dischargeMin, max(d.dischargePower) as dischargeMax, sum(d.pvPower) as pvPowerSum, (select count(t.seq) from t_timetable_pms t where t.inputDate = #{startDate} ) as pmsCnt, (select count(k.seq) from t_kesco_data k where k.plantSeq = p.seq and k.inputDate = #{startDate} ) as kescoCnt, (select count(k.seq) from t_kesco_data k where k.plantSeq = p.seq and k.inputDate = #{startDate} and k.sendStatus = '00' ) as kescoFailCnt, (select count(m.seq) from t_pmsdata_${searchYear} m where m.plantSeq = p.seq and m.pcsIdx = 0 and m.inputDate = #{startDate} and (m.data25 > 0 or m.data62 > 0 or m.data63 > 0 or m.data64 > 0 or m.data25 > 0 or m.data62 > 0 or m.data63 > 0 or m.data64 > 0) ) as pmsFailCnt from t_plant p, t_pmsdata_${searchYear} d where p.seq = d.plantSeq and d.inputDate = #{startDate} and d.pcsIdx = #{pcsIdx} group by p.seq ) p, t_pmsdata_${searchYear} d where p.plantSeq = d.plantSeq and d.inputDate = #{startDate} and d.timetableSeq = p.timetableSeq and d.pcsIdx = #{pcsIdx} and d.plantSeq = #{plantSeq} group by p.plantSeq asc order by p.plantSeq desc select p.*, d.data15 as data15Curr, d.pvPower as pvPowerCurr, m.batteryVolume as supplyBatteryPower from ( select p.seq as plantSeq, p.name as plantName, p.supplyPower, min(d.data15) as data15Min, max(d.data15) as data15Max, min(d.data32) as data32, max(d.data33) as data33, min(d.data16) as data16Min, max(d.data16) as data16Max, min(d.pvPower) as pvPowerMin, max(d.pvPower) as pvPowerMax, round(avg(d.pvPower)) as pvPowerAvg, min(d.data20) as data20Min, max(d.data20) as data20Max, min(d.data21) as data21Min, max(d.data21) as data21Max, min(d.data22) as data22Min, max(d.data22) as data22Max, min(d.data23) as data23Min, max(d.data23) as data23Max, max(d.inputHour) as inputHour, min(d.chargePower) as chargeMin, max(d.chargePower) as chargeMax, min(d.dischargePower) as dischargeMin, max(d.dischargePower) as dischargeMax, sum(d.pvPower) as pvPowerSum, (select count(t.seq) from t_timetable_pms t where t.inputDate = #{endDate} ) as pmsCnt, (select count(k.seq) from t_kesco_data k where k.plantSeq = p.seq and k.inputDate = #{endDate} ) as kescoCnt, (select count(k.seq) from t_kesco_data k where k.plantSeq = p.seq and k.inputDate = #{endDate} and k.sendStatus = '00' ) as kescoFailCnt, (select count(m.seq) from t_pmsdata_${searchYear} m where m.plantSeq = p.seq and m.pcsIdx = 0 and m.inputDate = #{endDate} and (m.data25 > 0 or m.data62 > 0 or m.data63 > 0 or m.data64 > 0 or m.data25 > 0 or m.data62 > 0 or m.data63 > 0 or m.data64 > 0) ) as pmsFailCnt from t_plant p, t_pmsdata_statistic_hour d where p.seq = d.plantSeq and d.inputDate = #{endDate} and d.pcsIdx = #{pcsIdx} group by p.seq ) p, t_pmsdata_${searchYear} d, t_pms m where p.plantSeq = d.plantSeq and d.inputDate = #{endDate} and d.inputHour = p.inputHour and d.pcsIdx = #{pcsIdx} and p.plantSeq = m.plantSeq and d.plantSeq = #{plantSeq} group by p.plantSeq order by p.plantSeq desc select p.*, m.batteryVolume as supplyBatteryPower from ( select p.seq as plantSeq, p.name as plantName, d.pcsIdx, p.supplyPower, min(d.data15) as data15Min, max(d.data15) as data15Max, round(avg(d.data15)) as data15Avg, min(d.data32) as data32Min, min(d.data33) as data33Min, max(d.data32) as data32Max, max(d.data33) as data33Max, min(d.data16) as data16Min, max(d.data16) as data16Max, min(d.pvPower) as pvPowerMin, max(d.pvPower) as pvPowerMax, round(avg(d.pvPower)) as pvPowerAvg, (select count(t.seq) from t_timetable_pms t where t.inputDate between #{startDate} and #{endDate} ) as pmsCnt, (select count(k.seq) from t_kesco_data k where k.plantSeq = p.seq and k.inputDate between #{startDate} and #{endDate} ) as kescoCnt, (select count(k.seq) from t_kesco_data k where k.plantSeq = p.seq and k.inputDate between #{startDate} and #{endDate} and k.sendStatus = '00' ) as kescoFailCnt, (select count(m.seq) from t_pmsdata_${searchYear} m where m.plantSeq = p.seq and m.pcsIdx = #{pcsIdx} and m.inputDate between #{startDate} and #{endDate} and (m.data25 > 0 or m.data62 > 0 or m.data63 > 0 or m.data64 > 0 or m.data25 > 0 or m.data62 > 0 or m.data63 > 0 or m.data64 > 0) ) as pmsFailCnt from t_plant p, t_pmsdata_statistic_day d where p.seq = d.plantSeq and d.inputDate between #{startDate} and #{endDate} and d.pcsIdx = #{pcsIdx} group by p.seq ) p, t_pms m where p.plantSeq = m.plantSeq and m.plantSeq = #{plantSeq} group by p.plantSeq order by p.plantSeq desc select p.name as plantName, d.* from ( select plantSeq, CAST(SUM(CASE WHEN typeCode ='10' THEN modelCode END ) AS CHAR(2)) as device10, CAST(SUM(CASE WHEN typeCode ='20' THEN modelCode END ) AS CHAR(2)) as device20, CAST(SUM(CASE WHEN typeCode ='30' THEN modelCode END ) AS CHAR(2)) as device30, SUM(CASE WHEN typeCode ='30' THEN quantity ELSE 0 END ) as device30Quantity, CAST(SUM(CASE WHEN typeCode ='40' THEN modelCode END ) AS CHAR(2)) as device40, SUM(CASE WHEN typeCode ='40' THEN quantity ELSE 0 END ) as device40Quantity from t_device group by plantSeq ) d, t_plant p where d.plantSeq = p.seq and p.plantStatus='01' and p.seq = #{plantSeq} and p.name like concat('%', #{plantName}, '%') select table_name as tableName, round(sum(data_length)/1024/1024) as sizeData, round(sum(index_length)/1024/1024) as sizeIndex from information_schema.tables where table_schema='emswdb' and table_name like 't_%' group by table_name order by table_name asc