데이터베이스를 사랑하는 사람들의 모임 데이터베이스 사랑넷

쿼리 질문 드립니다.

작성자

이기자(k3i2)

작성일

2018-03-20 07:24

조회수

5,667

WITH setdat AS
(
SELECT 1   uid
     , '' sday1 , '2' sday2 , '2' sday3 , '3' sday4 , '3' sday5
     , '3' sday6 , '4' sday7 , '4' sday8 , '4' sday9 , '4' sday10
     , '7' sday11, '7' sday12, '7' sday13, '7' sday14, '7' sday15
     , '7' sday16, '7' sday17, '8' sday18, '8' sday19, '8' sday20
     , '8' sday21, '8' sday22, '8' sday23, '8' sday24, '8' sday25
     , '' sday26, '' sday27, '' sday28, '' sday29, '' sday30
     , null sday31
)
SELECT uid
     , sday1 , sday2 , sday3 , sday4 , sday5
     , sday6 , sday7 , sday8 , sday9 , sday10
     , sday11, sday12, sday13, sday14, sday15
     , sday16, sday17, sday18, sday19, sday20
     , sday21, sday22, sday23, sday24, sday25
     , sday26, sday27, sday28, sday29, sday30
     , sday31
     , LENGTH(x) - LENGTH(REPLACE(x, '2', '')) cnt_2
     , LENGTH(x) - LENGTH(REPLACE(x, '3', '')) cnt_3
     , LENGTH(x) - LENGTH(REPLACE(x, '4', '')) cnt_4
     , LENGTH(x) - LENGTH(REPLACE(x, '7', '')) cnt_7
     , LENGTH(x) - LENGTH(REPLACE(x, '8', '')) cnt_8
     , 31 - LENGTH(x) cnt_null
FROM (SELECT uid
             , sday1 , sday2 , sday3 , sday4 , sday5
             , sday6 , sday7 , sday8 , sday9 , sday10
             , sday11, sday12, sday13, sday14, sday15
             , sday16, sday17, sday18, sday19, sday20
             , sday21, sday22, sday23, sday24, sday25
             , sday26, sday27, sday28, sday29, sday30
             , sday31
             , CONCAT(''
             , sday1 , sday2 , sday3 , sday4 , sday5
             , sday6 , sday7 , sday8 , sday9 , sday10
             , sday11, sday12, sday13, sday14, sday15
             , sday16, sday17, sday18, sday19, sday20
             , sday21, sday22, sday23, sday24, sday25
             , sday26, sday27, sday28, sday29, sday30
             , sday31
             ) x
          FROM setdat
        ) a
;