데이터베이스 질문과 답변

oracle decode 쿼리문 입니다. MySQL case when then 문의 형태로 바꿔주세요! 0 2 4,299

by 릿죠로 [SQL Query] decode case 변환 when then mysql [2015.09.04 21:42:08]

이번에 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')

 

 

 

by 마농 [2015.09.07 08:56:19]
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
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
;

 

by 릿죠로 [2015.09.07 10:21:16]

마농님 정말 감사합니다. ㅠㅠ

사랑합니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입