데이터베이스 질문과 답변
쿼리 질문드립니다.
10
by 날자~! [2018.02.22 09:07:13]
아래와 같이 데이터가 있을떄
각 행별로 중복된 숫자를 보여주는 컬럼을
쿼리로 어떻게 구현할 수 있을까요?
(아래 h열을 구성하고 싶은겁니다)
* 중복된 수가 없으면 null, 0이 중복되면 0으로 표기
a b c d e f g
---------------
6 7 8 9 0 8 3
0 5 1 5 8 3 1
2 7 9 5 9 6 6
[결과]
a b c d e f g h
-----------------
6 7 8 9 0 8 3 (8)
0 5 1 5 8 3 1 (5,1)
2 7 9 5 9 6 6 (9,6)
by 우리집아찌
[2018.02.22 09:37:50]
3개행 과 a~g까지 고정인가요?
by 날자~!
[2018.02.22 09:44:34]
행은 늘어날수 있고
열은 고정입니다.
by 마농
[2018.02.22 09:44:44]
a, b, c, d, e, f, g 외에 식별자(PK) 컬럼은 없나요?
by 날자~!
[2018.02.22 09:45:28]
네 따로 없습니다 ㅠ
by 마농
[2018.02.22 09:53:28]
WITH t(a, b, c, d, e, f, g) AS
(
SELECT 6, 7, 8, 9, 0, 8, 3 FROM dual UNION ALL
SELECT 0, 5, 1, 5, 8, 3, 1 FROM dual UNION ALL
SELECT 2, 7, 9, 5, 9, 6, 6 FROM dual
)
SELECT rn
, MIN(DECODE(gb, 'A', v)) a
, MIN(DECODE(gb, 'B', v)) b
, MIN(DECODE(gb, 'C', v)) c
, MIN(DECODE(gb, 'D', v)) d
, MIN(DECODE(gb, 'E', v)) e
, MIN(DECODE(gb, 'F', v)) f
, MIN(DECODE(gb, 'G', v)) g
, LISTAGG(h, ',') WITHIN GROUP(ORDER BY gb) h
FROM (SELECT rn, gb, v
, DECODE(ROW_NUMBER() OVER(PARTITION BY rn, v ORDER BY gb), 2, v) h
FROM (SELECT ROWNUM rn, a, b, c, d, e, f, g FROM t)
UNPIVOT (v FOR gb IN (a, b, c, d, e, f, g))
)
GROUP BY rn
ORDER BY rn
;
by 날자~!
[2018.02.22 10:00:40]
감사합니다. 공부할게 많네요 ㅠㅠ
by 우리집아찌
[2018.02.22 10:35:43]
배울게 아직 많네요.. ㅎㅎ
by 우리집아찌
[2018.02.22 10:34:14]
-- 간단하게는 실패... 그냥 무식하게..
WITH T ( A , B , C , D , E , F , G ) AS (
SELECT '6' , '7' , '8' , '9' , '0' , '8' , '3' FROM DUAL UNION ALL
SELECT '0' , '5' , '1' , '5' , '8' , '3' , '1' FROM DUAL UNION ALL
SELECT '2' , '7' , '9' , '5' , '9' , '6' , '6' FROM DUAL
), T2 AS (
SELECT GB , LN , V
FROM ( SELECT ROWNUM GB , A , B , C , D ,E , F , G
FROM T
)
UNPIVOT ( V FOR LN IN ( A AS 1 , B AS 2 , C AS 3 , D AS 4 ,E AS 5 , F AS 6 , G AS 7 ))
)
SELECT *
FROM (SELECT GB , LN , V FROM T2
UNION ALL
SELECT GB , 99 , LISTAGG(V,',') WITHIN GROUP(ORDER BY ST) LIST_V
FROM (SELECT GB ,MIN(LN) ST, V FROM T2 GROUP BY GB , V HAVING COUNT(*) > 1)
GROUP BY GB
)
PIVOT ( MIN(V) FOR LN IN ( 1 A , 2 B , 3 C , 4 D , 5 E , 6 F , 7 G , 99 H ))
ORDER BY GB
by 마농
[2018.02.22 10:48:04]
ROW_NUMBER() = 2 로 하면 순서가 꼬일 가능성이 있네요.(4번행 9,6 이 나와야 하는데 6,9 가 나옴)
ROW_NUMBER() = 1 AND COUNT(*) > 1 로 변경했습니다.
WITH t(a, b, c, d, e, f, g) AS
(
SELECT 6, 7, 8, 9, 0, 8, 3 FROM dual UNION ALL
SELECT 0, 5, 1, 5, 8, 3, 1 FROM dual UNION ALL
SELECT 2, 7, 9, 5, 9, 6, 6 FROM dual UNION ALL
SELECT 2, 7, 9, 6, 6, 6, 9 FROM dual
)
SELECT rn
, MIN(DECODE(gb, 'A', v)) a
, MIN(DECODE(gb, 'B', v)) b
, MIN(DECODE(gb, 'C', v)) c
, MIN(DECODE(gb, 'D', v)) d
, MIN(DECODE(gb, 'E', v)) e
, MIN(DECODE(gb, 'F', v)) f
, MIN(DECODE(gb, 'G', v)) g
, LISTAGG(h, ',') WITHIN GROUP(ORDER BY gb) h
FROM (SELECT rn, gb, v
, CASE WHEN ROW_NUMBER() OVER(PARTITION BY rn, v ORDER BY gb) = 1
AND COUNT(*) OVER(PARTITION BY rn, v) > 1
THEN v END h
FROM (SELECT ROWNUM rn, a, b, c, d, e, f, g FROM t)
UNPIVOT (v FOR gb IN (a, b, c, d, e, f, g))
)
GROUP BY rn
ORDER BY rn
;
by 날자~!
[2018.02.22 11:33:38]
네. 답변 감사합니다~!!
) 버튼을 클릭하여 작성 하시면 됩니다.