데이터베이스 질문과 답변
이번에 mysql용 쿼리를 짜면서 decode가 안되어서 case when then 문의로 변환하려 했으나 자꾸 실패하여서
도움 요청 드립니다. decode ==>> case when then 문의 형태로 변환 부탁드립니다!!
select
count(decode(to_char(trnsctn_dt, 'HH'), '01', 0)) "01:00",
count(decode(to_char(trnsctn_dt, 'HH'), '02', 0)) "02:00",
count(decode(to_char(trnsctn_dt, 'HH'), '03', 0)) "03:00",
count(decode(to_char(trnsctn_dt, 'HH'), '04', 0)) "04:00",
count(decode(to_char(trnsctn_dt, 'HH'), '05', 0)) "05:00",
count(decode(to_char(trnsctn_dt, 'HH'), '06', 0)) "06:00",
count(decode(to_char(trnsctn_dt, 'HH'), '07', 0)) "07:00",
count(decode(to_char(trnsctn_dt, 'HH'), '08', 0)) "08:00",
count(decode(to_char(trnsctn_dt, 'HH'), '09', 0)) "09:00",
count(decode(to_char(trnsctn_dt, 'HH'), '10', 0)) "10:00",
count(decode(to_char(trnsctn_dt, 'HH'), '11', 0)) "11:00",
count(decode(to_char(trnsctn_dt, 'HH'), '12', 0)) "12:00",
count(decode(to_char(trnsctn_dt, 'HH'), '13', 0)) "13:00",
count(decode(to_char(trnsctn_dt, 'HH'), '14', 0)) "14:00",
count(decode(to_char(trnsctn_dt, 'HH'), '15', 0)) "15:00",
count(decode(to_char(trnsctn_dt, 'HH'), '16', 0)) "16:00",
count(decode(to_char(trnsctn_dt, 'HH'), '17', 0)) "17:00",
count(decode(to_char(trnsctn_dt, 'HH'), '18', 0)) "18:00",
count(decode(to_char(trnsctn_dt, 'HH'), '19', 0)) "19:00",
count(decode(to_char(trnsctn_dt, 'HH'), '20', 0)) "20:00",
count(decode(to_char(trnsctn_dt, 'HH'), '21', 0)) "21:00",
count(decode(to_char(trnsctn_dt, 'HH'), '22', 0)) "22:00",
count(decode(to_char(trnsctn_dt, 'HH'), '23', 0)) "23:00",
count(decode(to_char(trnsctn_dt, 'HH'), '24', 0)) "24:00"
from tb_trnsctn
where trnsctn_dt >= TO_DATE (to_char(sysdate, 'YYYYMMDD') || '000000', 'YYYYMMDDHH24MISS')
AND trnsctn_dt <= TO_DATE (to_char(sysdate, 'YYYYMMDD') || '235959', 'YYYYMMDDHH24MISS')
SELECT IFNULL(MIN(CASE hh WHEN '00' THEN cnt END), 0) "00:00" , IFNULL(MIN(CASE hh WHEN '01' THEN cnt END), 0) "01:00" , IFNULL(MIN(CASE hh WHEN '02' THEN cnt END), 0) "02:00" , IFNULL(MIN(CASE hh WHEN '03' THEN cnt END), 0) "03:00" , IFNULL(MIN(CASE hh WHEN '04' THEN cnt END), 0) "04:00" , IFNULL(MIN(CASE hh WHEN '05' THEN cnt END), 0) "05:00" , IFNULL(MIN(CASE hh WHEN '06' THEN cnt END), 0) "06:00" , IFNULL(MIN(CASE hh WHEN '07' THEN cnt END), 0) "07:00" , IFNULL(MIN(CASE hh WHEN '08' THEN cnt END), 0) "08:00" , IFNULL(MIN(CASE hh WHEN '09' THEN cnt END), 0) "09:00" , IFNULL(MIN(CASE hh WHEN '10' THEN cnt END), 0) "10:00" , IFNULL(MIN(CASE hh WHEN '11' THEN cnt END), 0) "11:00" , IFNULL(MIN(CASE hh WHEN '12' THEN cnt END), 0) "12:00" , IFNULL(MIN(CASE hh WHEN '13' THEN cnt END), 0) "13:00" , IFNULL(MIN(CASE hh WHEN '14' THEN cnt END), 0) "14:00" , IFNULL(MIN(CASE hh WHEN '15' THEN cnt END), 0) "15:00" , IFNULL(MIN(CASE hh WHEN '16' THEN cnt END), 0) "16:00" , IFNULL(MIN(CASE hh WHEN '17' THEN cnt END), 0) "17:00" , IFNULL(MIN(CASE hh WHEN '18' THEN cnt END), 0) "18:00" , IFNULL(MIN(CASE hh WHEN '19' THEN cnt END), 0) "19:00" , IFNULL(MIN(CASE hh WHEN '20' THEN cnt END), 0) "20:00" , IFNULL(MIN(CASE hh WHEN '21' THEN cnt END), 0) "21:00" , IFNULL(MIN(CASE hh WHEN '22' THEN cnt END), 0) "22:00" , IFNULL(MIN(CASE hh WHEN '23' THEN cnt END), 0) "23:00" FROM (SELECT DATE_FORMAT(trnsctn_dt, '%H') hh , COUNT(*) cnt FROM tb_trnsctn WHERE trnsctn_dt >= CURDATE() AND trnsctn_dt < DATE_ADD(CURDATE(), INTERVAL 1 DAY) GROUP BY DATE_FORMAT(trnsctn_dt, '%H') ) a ;
마농님 정말 감사합니다. ㅠㅠ
사랑합니다.
) 버튼을 클릭하여 작성 하시면 됩니다.