오라클 쿼리 도움 요청드립니다 0 1 775

by 강쥐용 [2017.06.22 17:19:24]


해결완료        
by 마농 [2017.06.23 11:25:14]
WITH all_table AS
(
SELECT 'MK' data_type, 'A_KO_D' item_type, '20170501' m_date, 19 c_qt, null a_qt FROM dual
UNION ALL SELECT 'MK', 'B_KO_C'   , '20170501', null,   81 FROM dual
UNION ALL SELECT 'MK', 'B_KO_C'   , '20170501',   55, null FROM dual
UNION ALL SELECT 'MK', 'A_KO_AWQE', '20170501', null,  353 FROM dual
UNION ALL SELECT 'MK', 'A_KO_AWQE', '20170501',   90, null FROM dual
UNION ALL SELECT 'MK', 'M_KO_LL'  , '20170501',   10, null FROM dual
UNION ALL SELECT 'MK', 'MS_KO_CO' , '20170501', null,   19 FROM dual
UNION ALL SELECT 'MK', 'MS_KO_CO' , '20170501',   20, null FROM dual
UNION ALL SELECT 'MK', 'MX_LV_U'  , '20170501', null,    5 FROM dual
UNION ALL SELECT 'MK', 'MX_LV_U'  , '20170501',    5, null FROM dual
)
SELECT *
  FROM (SELECT data_type
             , m_date
             , NVL(item_type, 'TOTAL') item_type
             , DECODE(g, 0, c_qt, c_qt_x)||'' c_qt
             , a_qt||'' a_qt
             , ROUND(LEAST(NVL(a_qt, 0) / DECODE(g, 0, c_qt, c_qt_x), 1) * 100, 2)||'%' 달성률
          FROM (SELECT data_type, m_date, item_type
                     , SUM(c_qt) c_qt
                     , SUM(a_qt) a_qt
                     , SUM(c_qt) * SIGN(SUM(a_qt)) c_qt_x
                     , GROUPING(item_type) g
                  FROM all_table
                 WHERE data_type ='MK'
                   AND m_date    ='20170501'
                 GROUP BY data_type, m_date, ROLLUP(item_type)
                )
        )
 UNPIVOT INCLUDE NULLS (qt FOR gb IN (c_qt, a_qt, 달성률))
;

 

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