t_weather_2021 select p.seq as plantSeq, p.name as plantName, p.weatherCode, p.geox, p.geoy, w.inputYmd, w.inputHour, w.temp, w.humi, w.rain, w.cloud, w.wind from t_plant p left join w on w.weatherCode = p.weatherCode and inputYmd= #{inputYmd} and w.inputHour = ( select max(inputHour) from where inputYmd=#{inputYmd} and inputHour <= #{inputHour} ) and w.inputHour = ( select max(inputHour) from where inputYmd=#{inputYmd} ) where p.plantStatus='01' and p.seq = #{plantSeq} order by p.seq desc select p.seq as plantSeq, p.name as plantName, p.geox, p.geoy, w.* from w, t_plant p where w.weatherCode = p.weatherCode and p.plantStatus='01' and inputYmd= #{inputYmd} and w.inputHour = ( select max(inputHour) from where inputYmd=#{inputYmd} and inputHour <= #{inputHour} ) and w.inputHour = ( select max(inputHour) from where inputYmd=#{inputYmd} ) and p.seq = #{plantSeq} order by p.seq desc select inputHour, count(inputHour) as count from where inputYmd = #{inputYmd} group by inputHour order by inputHour desc select p.seq as plantSeq, p.name as plantName, w.* from w, t_plant p where w.weatherCode = p.weatherCode and p.plantStatus='01' and inputYmd= #{inputYmd} and p.seq = #{plantSeq} order by w.inputHour desc select inputYmd, inputHour, count(weatherCode) as count from where inputYmd = #{inputYmd} group by inputYmd, inputHour select inputYmd, count(distinct inputHour) as inputHourCnt, count(distinct weatherCode) as weatherCodeCnt, count(inputYmd) as cnt from where inputYmd between #{startYmd} and #{endYmd} group by inputYmd select inputYmd, inputHour, count(inputYmd) as count from where inputYmd = #{inputYmd} and inputHour = ( select max(inputHour) from where inputYmd = #{inputYmd} ) select w.inputYmd, inputHour, cloud, (cloud*10) as count from w where w.weatherCode = #{weatherCode} and w.inputYmd = #{startYmd} select w.inputYmd, inputHour, cloud, ( CASE avg(cloud) >= 4 WHEN true THEN round(avg(cloud)*10) WHEN false THEN 0 END ) as count from w where w.weatherCode = #{weatherCode} and w.inputYmd between #{startYmd} and #{endYmd} group by w.inputYmd select inputHour, 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 from ( select inputYmd, inputDay, inputHour, (CASE WHEN inputDay = '01' THEN cnt ELSE 0 END) as input01, (CASE WHEN inputDay = '02' THEN cnt ELSE 0 END) as input02, (CASE WHEN inputDay = '03' THEN cnt ELSE 0 END) as input03, (CASE WHEN inputDay = '04' THEN cnt ELSE 0 END) as input04, (CASE WHEN inputDay = '05' THEN cnt ELSE 0 END) as input05, (CASE WHEN inputDay = '06' THEN cnt ELSE 0 END) as input06, (CASE WHEN inputDay = '07' THEN cnt ELSE 0 END) as input07, (CASE WHEN inputDay = '08' THEN cnt ELSE 0 END) as input08, (CASE WHEN inputDay = '09' THEN cnt ELSE 0 END) as input09, (CASE WHEN inputDay = '10' THEN cnt ELSE 0 END) as input10, (CASE WHEN inputDay = '11' THEN cnt ELSE 0 END) as input11, (CASE WHEN inputDay = '12' THEN cnt ELSE 0 END) as input12, (CASE WHEN inputDay = '13' THEN cnt ELSE 0 END) as input13, (CASE WHEN inputDay = '14' THEN cnt ELSE 0 END) as input14, (CASE WHEN inputDay = '15' THEN cnt ELSE 0 END) as input15, (CASE WHEN inputDay = '16' THEN cnt ELSE 0 END) as input16, (CASE WHEN inputDay = '17' THEN cnt ELSE 0 END) as input17, (CASE WHEN inputDay = '18' THEN cnt ELSE 0 END) as input18, (CASE WHEN inputDay = '19' THEN cnt ELSE 0 END) as input19, (CASE WHEN inputDay = '20' THEN cnt ELSE 0 END) as input20, (CASE WHEN inputDay = '21' THEN cnt ELSE 0 END) as input21, (CASE WHEN inputDay = '22' THEN cnt ELSE 0 END) as input22, (CASE WHEN inputDay = '23' THEN cnt ELSE 0 END) as input23, (CASE WHEN inputDay = '24' THEN cnt ELSE 0 END) as input24, (CASE WHEN inputDay = '25' THEN cnt ELSE 0 END) as input25, (CASE WHEN inputDay = '26' THEN cnt ELSE 0 END) as input26, (CASE WHEN inputDay = '27' THEN cnt ELSE 0 END) as input27, (CASE WHEN inputDay = '28' THEN cnt ELSE 0 END) as input28, (CASE WHEN inputDay = '29' THEN cnt ELSE 0 END) as input29, (CASE WHEN inputDay = '30' THEN cnt ELSE 0 END) as input30, (CASE WHEN inputDay = '31' THEN cnt ELSE 0 END) as input31 from ( SELECT inputYmd, substring(inputYmd, 9, 2) as inputDay, inputHour, count(weatherCode) as cnt FROM where inputYmd between #{startYmd} and #{endYmd} group by inputYmd, inputHour ) w group by inputDay, inputHour ) w group by inputHour order by inputHour asc