데이터베이스 질문과 답변

SQL 조언부탁드립니다. 0 5 1,002

by zzhozang [2015.11.11 21:00:13]

안녕하세요 

평소 많은 도움 받고 있습니다. 감사합니다.

아래 테이블에서 <결과값> 형태로 데이터를 조회하는게 가능할까요?

조언 부탁드립니다.;;

감사합니다.

 

=======================================

SELECT '20151108' AS HSS_DD, '33' AS DUTY_ID, '1' AS DUTY, 'A' AS SHT FROM DUAL UNION ALL
SELECT '20151108', '33', '2', 'B' FROM DUAL UNION ALL
SELECT '20151108', '33', '3', 'C' FROM DUAL UNION ALL
SELECT '20151109', '43', '1', 'A' FROM DUAL UNION ALL
SELECT '20151109', '43', '2', 'B' FROM DUAL UNION ALL
SELECT '20151109', '43', '3', 'C' FROM DUAL UNION ALL
SELECT '20151110', '33', '1', 'A' FROM DUAL UNION ALL
SELECT '20151110', '33', '2', 'B' FROM DUAL UNION ALL
SELECT '20151110', '33', '3', 'C' FROM DUAL UNION ALL
SELECT '20151111', '43', '1', 'A' FROM DUAL UNION ALL
SELECT '20151111', '43', '2', 'B' FROM DUAL UNION ALL
SELECT '20151111', '43', '3', 'C' FROM DUAL UNION ALL
SELECT '20151111', '43', 'D', 'D' FROM DUAL UNION ALL
SELECT '20151112', '33', '1', 'A' FROM DUAL UNION ALL
SELECT '20151112', '33', '2', 'B' FROM DUAL UNION ALL
SELECT '20151112', '33', '3', 'C' FROM DUAL UNION ALL
SELECT '20151113', '33', '1', 'A' FROM DUAL UNION ALL
SELECT '20151113', '33', '2', 'B' FROM DUAL UNION ALL
SELECT '20151113', '33', '3', 'C' FROM DUAL UNION ALL
SELECT '20151114', '43', '1', 'A' FROM DUAL UNION ALL
SELECT '20151114', '43', '2', 'B' FROM DUAL UNION ALL
SELECT '20151114', '43', '3', 'C' FROM DUAL UNION ALL
SELECT '20151114', '43', 'D', 'D' FROM DUAL

<테이블>

HSS_DD DUTY_ID DUTY SHT
20151108 33 1 A
20151108 33 2 B
20151108 33 3 C
20151109 43 1 A
20151109 43 2 B
20151109 43 3 C
20151110 33 1 A
20151110 33 2 B
20151110 33 3 C
20151111 43 1 A
20151111 43 2 B
20151111 43 3 C
20151111 43 D D
20151112 33 1 A
20151112 33 2 B
20151112 33 3 C
20151113 33 1 A
20151113 33 2 B
20151113 33 3 C
20151114 43 1 A
20151114 43 2 B
20151114 43 3 C
20151114 43 D D

 

<결과값>

SUN MON TUE WED THU FRI SAT
33 43 33 43 33 33 43
A A A A A A A
B B B B B B B
C C C C C C C
　 　 　 D 　 　 D
by swlee710 [2015.11.12 08:51:31] 
  select min(case when gb = 1 then DUTY_ID end) sun
       , min(case when gb = 2 then DUTY_ID end) mon
       , min(case when gb = 3 then DUTY_ID end) tue
       , min(case when gb = 4 then DUTY_ID end) wen
       , min(case when gb = 5 then DUTY_ID end) thu
       , min(case when gb = 6 then DUTY_ID end) fri
       , min(case when gb = 7 then DUTY_ID end) sat
from (
select hss_dd,duty_id,duty,sht,to_char(to_date(hss_dd,'yyyymmdd'),'d') gb
from t)aa
group by substr(HSS_DD,1,6)
union all
 select min(case when gb = 1 then sht end) sun
       , min(case when gb = 2 then sht end) mon
       , min(case when gb = 3 then sht end) tue
       , min(case when gb = 4 then sht end) wen
       , min(case when gb = 5 then sht end) thu
       , min(case when gb = 6 then sht end) fri
       , min(case when gb = 7 then sht end) sat
from (
select hss_dd,duty_id,duty,sht,to_char(to_date(hss_dd,'yyyymmdd'),'d') gb
from t)aa
group by duty
order by sun;

 

by swlee710 [2015.11.12 09:02:35] 
select min(case when gb = 1 and lv = 1 then duty_id 
                 when gb = 1 and lv in (2,3,4) then sht end) sun
        ,min(case when gb = 2 and lv = 1 then duty_id 
                  when gb = 2 and lv in (2,3,4) then sht end) mon
        ,min(case when gb = 3 and lv = 1 then duty_id 
                  when gb = 3 and lv in (2,3,4) then sht end) tue
        ,min(case when gb = 4 and lv = 1 then duty_id 
                  when gb = 4 and lv in (2,3,4) then sht end) wen
        ,min(case when gb = 5 and lv = 1 then duty_id 
                  when gb = 5 and lv in (2,3,4) then sht end) thu
        ,min(case when gb = 6 and lv = 1 then duty_id 
                  when gb = 6 and lv in (2,3,4) then sht end) fri
        ,min(case when gb = 7 and lv = 1 then duty_id 
                  when gb = 7 and lv in (2,3,4) then sht end) sat
 from (
 select hss_dd,duty_id,duty,sht,to_char(to_date(hss_dd,'yyyymmdd'),'d') gb,lv
 from t,(select level lv from dual connect by level <= 4)) aa
 group by case when lv = 1 then substr(HSS_DD,1,6) when lv in (2,3,4) then duty end
 order by sun;

 

by 마농 [2015.11.12 09:07:54] 
SELECT sun, mon, tue, wed, thu, fri, sat
  FROM (SELECT TO_CHAR(TO_DATE(hss_dd, 'yyyymmdd'), 'd') d
             , NVL(duty, '0') duty
             , NVL(sht, duty_id) sht
          FROM t
         WHERE hss_dd >= TO_CHAR(TRUNC(sysdate, 'd')    , 'yyyymmdd')
           AND hss_dd <= TO_CHAR(TRUNC(sysdate, 'd') + 6, 'yyyymmdd')
         GROUP BY TO_CHAR(TO_DATE(hss_dd, 'yyyymmdd'), 'd')
             , duty_id
             , ROLLUP((duty, sht))
        )
 PIVOT (MIN(sht) FOR d IN
       ('1' sun, '2' mon, '3' tue, '4' wed, '5' thu, '6' fri, '7' sat) )
 ORDER BY duty
;

 

by zzhozang [2015.11.12 09:32:46]

감사합니다.

꾸역꾸역 만들긴 했었는데 제가 작성한거 보다 쿼리 길이가 반은 줄었네요..

by 창조의날개 [2015.11.12 09:33:22] 

WITH T1 AS(
      SELECT '20151108' AS HSS_DD, '33' AS DUTY_ID, '1' AS DUTY, 'A' AS SHT FROM DUAL UNION ALL
      SELECT '20151108', '33', '2', 'B' FROM DUAL UNION ALL
      SELECT '20151108', '33', '3', 'C' FROM DUAL UNION ALL
      SELECT '20151109', '43', '1', 'A' FROM DUAL UNION ALL
      SELECT '20151109', '43', '2', 'B' FROM DUAL UNION ALL
      SELECT '20151109', '43', '3', 'C' FROM DUAL UNION ALL
      SELECT '20151110', '33', '1', 'A' FROM DUAL UNION ALL
      SELECT '20151110', '33', '2', 'B' FROM DUAL UNION ALL
      SELECT '20151110', '33', '3', 'C' FROM DUAL UNION ALL
      SELECT '20151111', '43', '1', 'A' FROM DUAL UNION ALL
      SELECT '20151111', '43', '2', 'B' FROM DUAL UNION ALL
      SELECT '20151111', '43', '3', 'C' FROM DUAL UNION ALL
      SELECT '20151111', '43', 'D', 'D' FROM DUAL UNION ALL
      SELECT '20151112', '33', '1', 'A' FROM DUAL UNION ALL
      SELECT '20151112', '33', '2', 'B' FROM DUAL UNION ALL
      SELECT '20151112', '33', '3', 'C' FROM DUAL UNION ALL
      SELECT '20151113', '33', '1', 'A' FROM DUAL UNION ALL
      SELECT '20151113', '33', '2', 'B' FROM DUAL UNION ALL
      SELECT '20151113', '33', '3', 'C' FROM DUAL UNION ALL
      SELECT '20151114', '43', '1', 'A' FROM DUAL UNION ALL
      SELECT '20151114', '43', '2', 'B' FROM DUAL UNION ALL
      SELECT '20151114', '43', '3', 'C' FROM DUAL UNION ALL
      SELECT '20151114', '43', 'D', 'D' FROM DUAL
)
, T2 AS (
      SELECT HSS_DD, DUTY_ID, DUTY, SHT
           , TO_CHAR(TO_DATE(HSS_DD,'YYYYMMDD'),'D') DY
      FROM T1
)
SELECT '0' DUTY, MAX(SUN) SUN, MAX(MON) MON, MAX(TUE) TUE, MAX(WED) WED, MAX(THU) THU, MAX(FRI) FRI, MAX(SAT) SAT
FROM T2
PIVOT ( MAX(DUTY_ID) FOR DY IN(1 SUN,2	MON,3	TUE,4	WED,5	THU,6	FRI,7	SAT) )
UNION ALL
SELECT DUTY, MAX(SUN), MAX(MON), MAX(TUE), MAX(WED), MAX(THU), MAX(FRI), MAX(SAT)
FROM T2
PIVOT ( MAX(SHT) FOR DY IN(1 SUN,2	MON,3	TUE,4	WED,5	THU,6	FRI,7	SAT) )
GROUP BY DUTY
ORDER BY DUTY
;

 

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