쿼리 초보적인질문드립니다. 0 5 679

by 아빠파파 [MySQL] [2017.07.25 10:52:34]


지금 데이터는   

      A     |   B   |    C

--------------------------

20170101 |  1000

20170101 |  1100

20170101 |  1200

20170101 |  1300

이렇게 있습니다.. 제가 뿌려주고 싶은거는요

      A     |   B    |     C

--------------------------------

20170101 |  0900   |     N

20170101 |  1000   |     Y

20170101 |  1100   |     Y

20170101 |  1200   |     Y

20170101 |  1300   |     Y

20170101 |  1400   |     N

20170101 |  1500   |     N

20170101 |  1600   |     N

20170101 |  1700   |     N

20170101 |  1800   |     N

 

이렇게 뿌려주고 싶은데요.

초보적인 질문 죄송합니다 ㅠㅜ

by 마농 [2017.07.25 11:16:35]
WITH t AS
(
SELECT '20170101' dt, '1000' tm FROM dual
UNION ALL SELECT '20170101', '1100' FROM dual
UNION ALL SELECT '20170101', '1200' FROM dual
UNION ALL SELECT '20170101', '1300' FROM dual
)
SELECT a.dt
     , a.tm
     , NVL2(b.tm, 'Y', 'N') yn
  FROM (SELECT '20170101' dt
             , LPAD((LEVEL + 8), 2, '0') || '00' tm
          FROM dual
         CONNECT BY LEVEL <= 10
        ) a
  LEFT OUTER JOIN t b
    ON a.dt = b.dt
   AND a.tm = b.tm
 ORDER BY tm
;

 


by 아빠파파 [2017.07.25 11:25:31]

 

MySQL 에서는 안돌아가요~마농님~


by 우리집아찌 [2017.07.25 11:25:43]

헉 b칼럼이 시간이었구나  저걸 어떻게 알았데요 


by 마농 [2017.07.25 11:30:04]
SELECT a.dt
     , b.tm
     , CASE WHEN c.tm IS NULL THEN 'N' ELSE 'Y' END yn
  FROM (SELECT '20170101' dt) a
 CROSS JOIN
       (SELECT '0900' tm
        UNION ALL SELECT '1000'
        UNION ALL SELECT '1100'
        UNION ALL SELECT '1200'
        UNION ALL SELECT '1300'
        UNION ALL SELECT '1400'
        UNION ALL SELECT '1500'
        UNION ALL SELECT '1600'
        UNION ALL SELECT '1700'
        UNION ALL SELECT '1800'
        ) b
  LEFT OUTER JOIN
       (-- data_table --
        SELECT '20170101' dt, '1000' tm
        UNION ALL SELECT '20170101', '1100'
        UNION ALL SELECT '20170101', '1200'
        UNION ALL SELECT '20170101', '1300'
        ) c
    ON a.dt = c.dt
   AND b.tm = c.tm
 ORDER BY dt, tm
;

 


by 아빠파파 [2017.07.25 12:02:14]

감사합니다 마농님~^^좋은하루되세요

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