select d.*, m.pcsMaker, m.batteryMaker, p.name as plantName, p.supplyPower from ( select d.* , s1.chargePower as y1ChargePower, s1.dischargePower as y1DischargePower from t_pmsdata_statistic_hour d left join t_pmsdata_statistic_day s1 on d.plantSeq = s1.plantSeq and d.pcsIdx = s1.pcsIdx and s1.plantSeq = #{plantSeq} and s1.pcsIdx = #{pcsIdx} and s1.inputDate = date_format(date_add(#{inputDate}, INTERVAL -1 DAY), '%Y-%m-%d') where 1=1 and d.plantSeq = #{plantSeq} and d.pcsIdx = #{pcsIdx} and d.inputDate = #{inputDate} and d.inputHour = #{inputHour} and d.seq = #{seq} ) d, t_pms m, t_plant p where d.plantSeq = m.plantSeq and d.plantSeq = p.seq order by d.plantSeq desc, d.inputHour asc, d.pcsIdx asc order by d.plantSeq desc, d.inputHour desc, d.pcsIdx asc select d.*, d.pvPower as todayPvPower, m.pcsMaker, m.batteryMaker, p.name as plantName, p.supplyPower from ( select d.*, m.chargePower as y1ChargePower, m.dischargePower as y1DischargePower from t_pmsdata_statistic_day d left join t_pmsdata_statistic_month m on d.plantSeq = m.plantSeq and d.pcsIdx = m.pcsIdx and m.inputDate = concat(date_format(date_add(#{inputDate}, INTERVAL -1 MONTH), '%Y-%m'), '-01') and m.plantSeq = #{plantSeq} and m.pcsIdx = #{pcsIdx} where 1=1 and d.plantSeq = #{plantSeq} and d.inputYear = #{inputYear} and d.inputMonth = #{inputMonth} and d.inputDate = #{inputDate} and d.pcsIdx = #{pcsIdx} and d.inputDate between #{str1} and #{str2} ) d, t_pms m, t_plant p where d.plantSeq = m.plantSeq and d.plantSeq = p.seq order by d.inputYear asc, d.inputMonth asc, d.inputDay asc, d.pcsIdx asc order by d.inputYear desc, d.inputMonth desc, d.inputDay desc, d.pcsIdx asc select d.*, m.pcsMaker, m.batteryMaker, p.name as plantName, p.supplyPower from ( select d1.*, d2.inputDate as inputDateB1 from t_pmsdata_statistic_month d1 left join t_pmsdata_statistic_month d2 on d1.plantSeq = d2.plantSeq and d1.pcsIdx = d2.pcsIdx and d1.inputDate = date_add(d2.inputDate, INTERVAL 1 MONTH) and d2.plantSeq = #{plantSeq} and d2.pcsIdx = #{pcsIdx} where 1=1 and d1.plantSeq = #{plantSeq} and d1.pcsIdx = #{pcsIdx} and d1.inputYear = #{inputYear} and d1.inputMonth = #{inputMonth} ) d, t_pms m, t_plant p where d.plantSeq = m.plantSeq and d.plantSeq = p.seq order by d.inputYear asc, d.inputMonth asc, d.pcsIdx asc order by d.inputYear desc, d.inputMonth desc, d.pcsIdx asc select p.seq as plantSeq, p.plantName, p.plantStatus, p.supplyPower, p.pmsStatus, p.pcsMaker, p.pcsQuantity, d.seq, d.pcsIdx, d.timetableSeq, d.inputDate, d.inputHour, d.inputMinute, d.chargePower, d.dischargePower, d.pvPower, (p.pcsVolume * p.pcsQuantity) as supplyPcsPower, (p.batteryVolume * p.batteryQuantity) as supplyBatteryPower, d.data1, d.data2, d.data3, d.data4, d.data5, d.data6, d.data7, d.data8, d.data9, d.data10, d.data11, d.data12, d.data13, d.data14, d.data15, d.data16, d.data17, d.data18, d.data19, d.data20, d.data21, d.data22, d.data23, d.data24, d.data25, d.data26, d.data27, d.data28, d.data29, d.data30, d.data31, d.data32, d.data33, d.data51, d.data52, d.data53, d.data54, d.data55, d.data56, d.data57, d.data58, d.data59, d.data60, d.data61, d.data62, d.data63, d.data64, d.data65, d.data66, d.data67, d.data68, d.data69, d.data70, d.createDatetime, d.count from ( select p.seq, p.name as plantName, p.plantStatus, p.supplyPower, pm.pmsStatus, pm.pcsMaker, pm.pcsQuantity, pm.pcsVolume, pm.batteryQuantity, pm.batteryVolume from t_plant p, t_pms pm where p.seq = pm.plantSeq and p.plantStatus = #{plantStatus} and pm.pmsStatus = #{pmsStatus} and p.name like concat('%', #{plantName}, '%') and p.seq = #{plantSeq} ) p left join ( select * from t_pmsdata_statistic_day where pcsIdx=0 and inputDate = #{inputDate} and plantSeq = #{plantSeq} ) d on p.seq = d.plantSeq order by d.data15 desc order by d.pvPower/p.supplyPower desc select p.seq as plantSeq, p.name, m.pcsQuantity, s.* from t_plant p, t_pms m left join ( select s.plantSeq as seq, s.inputdate, s.pcsIdx, max(input00) as input00, max(input01) as input01, max(input02) as input02, max(input03) as input03, max(input04) as input04, max(input05) as input05, max(input06) as input06, max(input07) as input07, max(input08) as input08, max(input09) as input09, max(input10) as input10, max(input11) as input11, max(input12) as input12, max(input13) as input13, max(input14) as input14, max(input15) as input15, max(input16) as input16, max(input17) as input17, max(input18) as input18, max(input19) as input19, max(input20) as input20, max(input21) as input21, max(input22) as input22, max(input23) as input23, count(plantSeq) as total from ( select s.plantSeq, s.inputdate, s.inputHour, s.pcsIdx, (CASE WHEN inputHour = '00' THEN 1 ELSE 0 END) as input00, (CASE WHEN inputHour = '01' THEN 1 ELSE 0 END) as input01, (CASE WHEN inputHour = '02' THEN 1 ELSE 0 END) as input02, (CASE WHEN inputHour = '03' THEN 1 ELSE 0 END) as input03, (CASE WHEN inputHour = '04' THEN 1 ELSE 0 END) as input04, (CASE WHEN inputHour = '05' THEN 1 ELSE 0 END) as input05, (CASE WHEN inputHour = '06' THEN 1 ELSE 0 END) as input06, (CASE WHEN inputHour = '07' THEN 1 ELSE 0 END) as input07, (CASE WHEN inputHour = '08' THEN 1 ELSE 0 END) as input08, (CASE WHEN inputHour = '09' THEN 1 ELSE 0 END) as input09, (CASE WHEN inputHour = '10' THEN 1 ELSE 0 END) as input10, (CASE WHEN inputHour = '11' THEN 1 ELSE 0 END) as input11, (CASE WHEN inputHour = '12' THEN 1 ELSE 0 END) as input12, (CASE WHEN inputHour = '13' THEN 1 ELSE 0 END) as input13, (CASE WHEN inputHour = '14' THEN 1 ELSE 0 END) as input14, (CASE WHEN inputHour = '15' THEN 1 ELSE 0 END) as input15, (CASE WHEN inputHour = '16' THEN 1 ELSE 0 END) as input16, (CASE WHEN inputHour = '17' THEN 1 ELSE 0 END) as input17, (CASE WHEN inputHour = '18' THEN 1 ELSE 0 END) as input18, (CASE WHEN inputHour = '19' THEN 1 ELSE 0 END) as input19, (CASE WHEN inputHour = '20' THEN 1 ELSE 0 END) as input20, (CASE WHEN inputHour = '21' THEN 1 ELSE 0 END) as input21, (CASE WHEN inputHour = '22' THEN 1 ELSE 0 END) as input22, (CASE WHEN inputHour = '23' THEN 1 ELSE 0 END) as input23 from t_pmsdata_statistic_hour s where s.inputDate=#{inputDate} and s.plantSeq = #{plantSeq} and s.pcsIdx=#{pcsIdx} group by s.plantSeq, s.pcsIdx, s.inputDate, s.inputHour ) s group by s.plantSeq, s.pcsIdx ) s on m.plantSeq = s.seq where m.pmsStatus='01' and p.plantStatus='01' and p.seq = m.plantSeq and m.plantSeq = #{plantSeq} order by m.plantSeq desc, s.pcsIdx asc, s.inputDate asc select p.seq as plantSeq, p.name, m.pcsQuantity, s.* from t_plant p, t_pms m left join ( select s.plantSeq as seq, s.pcsIdx, s.inputYear, s.inputMonth, s.inputDay, max(input01) as input01, max(input02) as input02, max(input03) as input03, max(input04) as input04, max(input05) as input05, max(input06) as input06, max(input07) as input07, max(input08) as input08, max(input09) as input09, max(input10) as input10, max(input11) as input11, max(input12) as input12, max(input13) as input13, max(input14) as input14, max(input15) as input15, max(input16) as input16, max(input17) as input17, max(input18) as input18, max(input19) as input19, max(input20) as input20, max(input21) as input21, max(input22) as input22, max(input23) as input23, max(input24) as input24, max(input25) as input25, max(input26) as input26, max(input27) as input27, max(input28) as input28, max(input29) as input29, max(input30) as input30, max(input31) as input31, sum(total) as total from ( select s.plantSeq, s.pcsIdx, s.inputYear, s.inputMonth, s.inputDay, s.seq, (CASE WHEN inputDay = '01' THEN cnt END) as input01, (CASE WHEN inputDay = '02' THEN cnt END) as input02, (CASE WHEN inputDay = '03' THEN cnt END) as input03, (CASE WHEN inputDay = '04' THEN cnt END) as input04, (CASE WHEN inputDay = '05' THEN cnt END) as input05, (CASE WHEN inputDay = '06' THEN cnt END) as input06, (CASE WHEN inputDay = '07' THEN cnt END) as input07, (CASE WHEN inputDay = '08' THEN cnt END) as input08, (CASE WHEN inputDay = '09' THEN cnt END) as input09, (CASE WHEN inputDay = '10' THEN cnt END) as input10, (CASE WHEN inputDay = '11' THEN cnt END) as input11, (CASE WHEN inputDay = '12' THEN cnt END) as input12, (CASE WHEN inputDay = '13' THEN cnt END) as input13, (CASE WHEN inputDay = '14' THEN cnt END) as input14, (CASE WHEN inputDay = '15' THEN cnt END) as input15, (CASE WHEN inputDay = '16' THEN cnt END) as input16, (CASE WHEN inputDay = '17' THEN cnt END) as input17, (CASE WHEN inputDay = '18' THEN cnt END) as input18, (CASE WHEN inputDay = '19' THEN cnt END) as input19, (CASE WHEN inputDay = '20' THEN cnt END) as input20, (CASE WHEN inputDay = '21' THEN cnt END) as input21, (CASE WHEN inputDay = '22' THEN cnt END) as input22, (CASE WHEN inputDay = '23' THEN cnt END) as input23, (CASE WHEN inputDay = '24' THEN cnt END) as input24, (CASE WHEN inputDay = '25' THEN cnt END) as input25, (CASE WHEN inputDay = '26' THEN cnt END) as input26, (CASE WHEN inputDay = '27' THEN cnt END) as input27, (CASE WHEN inputDay = '28' THEN cnt END) as input28, (CASE WHEN inputDay = '29' THEN cnt END) as input29, (CASE WHEN inputDay = '30' THEN cnt END) as input30, (CASE WHEN inputDay = '31' THEN cnt END) as input31, sum(s.cnt) total from ( select s.plantSeq, s.pcsIdx, s.inputDate, substr(s.inputDate, 1, 4) as inputYear, substr(s.inputDate, 6, 2) as inputMonth, substr(s.inputDate, 9) as inputDay, s.seq, count(s.seq) as cnt from t_pmsdata_statistic_hour s where inputDate like concat(#{inputYear}, '-', #{inputMonth}, '-__') and pcsIdx=#{pcsIdx} group by s.plantSeq, s.inputDate order by plantSeq asc ) s group by s.plantSeq, s.inputDate ) s group by s.plantSeq, s.pcsIdx ) s on m.plantSeq = s.seq where m.pmsStatus='01' and p.plantStatus='01' and p.seq = m.plantSeq and m.plantSeq = #{plantSeq} order by m.plantSeq desc, s.pcsIdx asc, s.inputDay asc select p.seq as plantSeq, p.name, m.pcsQuantity, s.* from t_plant p, t_pms m left join ( select s.plantSeq as seq, s.pcsIdx, s.inputYear, s.inputMonth, s.inputDay, max(input01) as input01, max(input02) as input02, max(input03) as input03, max(input04) as input04, max(input05) as input05, max(input06) as input06, max(input07) as input07, max(input08) as input08, max(input09) as input09, max(input10) as input10, max(input11) as input11, max(input12) as input12, max(input13) as input13, max(input14) as input14, max(input15) as input15, max(input16) as input16, max(input17) as input17, max(input18) as input18, max(input19) as input19, max(input20) as input20, max(input21) as input21, max(input22) as input22, max(input23) as input23, max(input24) as input24, max(input25) as input25, max(input26) as input26, max(input27) as input27, max(input28) as input28, max(input29) as input29, max(input30) as input30, max(input31) as input31, count(seq) as total from ( select s.plantSeq, s.pcsIdx, s.inputYear, s.inputMonth, s.inputDay, s.seq, (CASE WHEN inputDay = '01' THEN 1 ELSE 0 END) as input01, (CASE WHEN inputDay = '02' THEN 1 ELSE 0 END) as input02, (CASE WHEN inputDay = '03' THEN 1 ELSE 0 END) as input03, (CASE WHEN inputDay = '04' THEN 1 ELSE 0 END) as input04, (CASE WHEN inputDay = '05' THEN 1 ELSE 0 END) as input05, (CASE WHEN inputDay = '06' THEN 1 ELSE 0 END) as input06, (CASE WHEN inputDay = '07' THEN 1 ELSE 0 END) as input07, (CASE WHEN inputDay = '08' THEN 1 ELSE 0 END) as input08, (CASE WHEN inputDay = '09' THEN 1 ELSE 0 END) as input09, (CASE WHEN inputDay = '10' THEN 1 ELSE 0 END) as input10, (CASE WHEN inputDay = '11' THEN 1 ELSE 0 END) as input11, (CASE WHEN inputDay = '12' THEN 1 ELSE 0 END) as input12, (CASE WHEN inputDay = '13' THEN 1 ELSE 0 END) as input13, (CASE WHEN inputDay = '14' THEN 1 ELSE 0 END) as input14, (CASE WHEN inputDay = '15' THEN 1 ELSE 0 END) as input15, (CASE WHEN inputDay = '16' THEN 1 ELSE 0 END) as input16, (CASE WHEN inputDay = '17' THEN 1 ELSE 0 END) as input17, (CASE WHEN inputDay = '18' THEN 1 ELSE 0 END) as input18, (CASE WHEN inputDay = '19' THEN 1 ELSE 0 END) as input19, (CASE WHEN inputDay = '20' THEN 1 ELSE 0 END) as input20, (CASE WHEN inputDay = '21' THEN 1 ELSE 0 END) as input21, (CASE WHEN inputDay = '22' THEN 1 ELSE 0 END) as input22, (CASE WHEN inputDay = '23' THEN 1 ELSE 0 END) as input23, (CASE WHEN inputDay = '24' THEN 1 ELSE 0 END) as input24, (CASE WHEN inputDay = '25' THEN 1 ELSE 0 END) as input25, (CASE WHEN inputDay = '26' THEN 1 ELSE 0 END) as input26, (CASE WHEN inputDay = '27' THEN 1 ELSE 0 END) as input27, (CASE WHEN inputDay = '28' THEN 1 ELSE 0 END) as input28, (CASE WHEN inputDay = '29' THEN 1 ELSE 0 END) as input29, (CASE WHEN inputDay = '30' THEN 1 ELSE 0 END) as input30, (CASE WHEN inputDay = '31' THEN 1 ELSE 0 END) as input31 from t_pmsdata_statistic_day s where inputYear = #{inputYear} and inputMonth=#{inputMonth} and s.plantSeq = #{plantSeq} and s.pcsIdx=#{pcsIdx} ) s group by s.plantSeq, s.pcsIdx ) s on m.plantSeq = s.seq where m.pmsStatus='01' and p.plantStatus='01' and p.seq = m.plantSeq and m.plantSeq = #{plantSeq} order by m.plantSeq desc, s.pcsIdx asc, s.inputDay asc select inputHour, createDatetime, count(seq) as count from t_pmsdata_statistic_hour where inputDate=#{inputDate} and inputHour = (select max(inputHour) from t_pmsdata_statistic_hour where inputDate = #{inputDate}) and pcsIdx=0 select inputMonth, inputDay, createDatetime, count(seq) as count from t_pmsdata_statistic_day where inputDate=#{inputDate} and pcsIdx=0; select inputHour, createDatetime, count(seq) from t_pmsdata_statistic_hour where inputDate=#{inputDate} and inputHour = (select max(inputHour) from t_pmsdata_statistic_hour where inputDate = #{inputDate}) and pcsIdx=0