데이터베이스 질문과 답변
기존데이터:
201707 A
201707 B
201708 A
201709 B
보여주고자하는 데이터:
201707 B
201708 A
201709 B
이런식입니다.
월에 B데이터가 있으면 우선 무조건 보여주고
다른 A데이타는 안보여주고
만약 월에 B데이타는 없고 A데이타만 있으면
A데이타는 보여줘야합니다.
계속 머리속에서 멤돌고 있습니다.
부탁드립니다.
by 우리집아찌
[2017.09.04 11:10:21]
WITH T AS (
SELECT '201707' YYYY , 'A' GB FROM DUAL UNION ALL
SELECT '201707' , 'B' FROM DUAL UNION ALL
SELECT '201708' , 'A' FROM DUAL UNION ALL
SELECT '201709' , 'B' FROM DUAL U
)
SELECT YYYY
, NVL(MIN(DECODE(GB,'B',GB)),MIN(DECODE(GB,'A',GB))) GB
FROM T
GROUP BY YYYY
ORDER BY YYYY
by 랑에1
[2017.09.04 11:10:38]
WITH T AS ( SELECT '201707' ym, 'A' val FROM dual UNION ALL SELECT '201707' ym, 'B' val FROM dual UNION ALL SELECT '201708' ym, 'A' val FROM dual UNION ALL SELECT '201709' ym, 'B' val FROM dual ) SELECT ym, MAX(val) val FROM T GROUP BY ym ORDER BY ym
by 초보개발
[2017.09.04 11:17:46]
너무너무 감사드립니다.
by 박군two
[2017.09.04 11:26:04]
우리집아찌 님이랑 똑같이 만들어져서 다르게 만들어봤습니다. 깔끔하지가 못하네요...
WITH T AS (
SELECT '201707' YYYYMM, 'A' DATA_STRING FROM DUAL UNION ALL
SELECT '201707' YYYYMM, 'B' DATA_STRING FROM DUAL UNION ALL
SELECT '201708' YYYYMM, 'A' DATA_STRING FROM DUAL UNION ALL
SELECT '201709' YYYYMM, 'B' DATA_STRING FROM DUAL
)
SELECT TT.YYYYMM,
CASE WHEN DATA_STRING_CNT > 1 THEN REPLACE(REPLACE(DATA_STRING, ',' || 'A', ''), ',', '')
ELSE REPLACE(DATA_STRING, ',', '')
END DATA_STRING
FROM (SELECT T.YYYYMM, ','|| WM_CONCAT(T.DATA_STRING) AS DATA_STRING, COUNT(T.DATA_STRING) DATA_STRING_CNT
FROM T
GROUP BY T.YYYYMM
) TT
by 우리집아찌
[2017.09.04 11:27:57]
WM_CONCAT은 12c에서 지원하지 않아요..
by 우리집아찌
[2017.09.04 11:32:14]
/* 하나더 */
WITH T AS (
SELECT '201707' YYYY , 'A' GB FROM DUAL UNION ALL
SELECT '201707' , 'B' FROM DUAL UNION ALL
SELECT '201708' , 'A' FROM DUAL UNION ALL
SELECT '201709' , 'B' FROM DUAL U
)
SELECT A.YYYY , A.GB
FROM ( SELECT YYYY
, GB
, ROW_NUMBER() OVER(PARTITION BY YYYY ORDER BY DECODE(GB,'B',1,'C',2)) RN
FROM T ) A
WHERE RN = 1
ORDER BY A.YYYY
) 버튼을 클릭하여 작성 하시면 됩니다.