데이터베이스 질문과 답변
안녕하세요. 혼자서 고민해보다가 답이 떠오르지 않아 도움을 구하고자 글 씁니다.
첨부된 그림의 데이터를
| the_date | push_slct11 | push_slct15 | push_slct16 | push_slct17 | push_slct18 | ...... | push_slct_92 |
| m_succ_cnt | 15428 | 3 | 724 | 709 | 2426 | ..... | 4933 |
| m_fail_cnt | 124 | 0 | 3 | 3 | 16 | ...... | 195 |
위 표와 같은 형식으로 표현하고 싶습니다.
고수님들의 조언 부탁드립니다. 감사합니다.
ps. 가급적 테이블 여러번 SELECT / UNION ALL 은 피하고자 합니다. (tmp성 테이블 제외)
by 마농
[2020.11.24 13:21:10]
WITH t AS
(
SELECT '2020-08-23' the_date, 0 push_slct, 0 m_succ_cnt, 0 m_fail_cnt
UNION ALL SELECT '2020-08-23', 11, 15428, 124
UNION ALL SELECT '2020-08-23', 15, 3, 0
UNION ALL SELECT '2020-08-23', 16, 724, 3
UNION ALL SELECT '2020-08-23', 17, 109, 3
UNION ALL SELECT '2020-08-23', 18, 2426, 16
UNION ALL SELECT '2020-08-23', 19, 6, 1
UNION ALL SELECT '2020-08-23', 20, 3, 0
UNION ALL SELECT '2020-08-23', 21, 59, 2
UNION ALL SELECT '2020-08-23', 22, 11, 0
UNION ALL SELECT '2020-08-23', 23, 10, 0
UNION ALL SELECT '2020-08-23', 51, 213, 7
UNION ALL SELECT '2020-08-23', 92, 4933, 195
)
SELECT gb
, MIN(CASE push_slct WHEN 11 THEN cnt END) push_slct_11
, MIN(CASE push_slct WHEN 15 THEN cnt END) push_slct_15
, MIN(CASE push_slct WHEN 16 THEN cnt END) push_slct_16
, MIN(CASE push_slct WHEN 17 THEN cnt END) push_slct_17
, MIN(CASE push_slct WHEN 18 THEN cnt END) push_slct_18
, MIN(CASE push_slct WHEN 19 THEN cnt END) push_slct_19
, MIN(CASE push_slct WHEN 20 THEN cnt END) push_slct_20
, MIN(CASE push_slct WHEN 21 THEN cnt END) push_slct_21
, MIN(CASE push_slct WHEN 22 THEN cnt END) push_slct_22
, MIN(CASE push_slct WHEN 23 THEN cnt END) push_slct_23
, MIN(CASE push_slct WHEN 51 THEN cnt END) push_slct_51
, MIN(CASE push_slct WHEN 92 THEN cnt END) push_slct_92
FROM (SELECT gb
, push_slct
, CASE gb
WHEN 'm_succ_cnt' THEN m_succ_cnt
WHEN 'm_fail_cnt' THEN m_fail_cnt
END cnt
FROM t a
, (SELECT 'm_succ_cnt' gb
UNION ALL
SELECT 'm_fail_cnt'
) b
WHERE a.the_date = '2020-08-23'
) c
GROUP BY gb
ORDER BY gb DESC
;
) 버튼을 클릭하여 작성 하시면 됩니다.