데이터베이스 질문과 답변
아래 쿼리에서 결과가 제대로 나오질 않습니다.
WITH
TB_STUDENT(NAME) AS
(SELECT 'A' FROM DUAL
UNION ALL
SELECT 'B' FROM DUAL
),
TB_CLASS(NAME,CLASS) AS
(SELECT 'A','수학' FROM DUAL
UNION ALL
SELECT 'A','과학' FROM DUAL
UNION ALL
SELECT 'A','체육' FROM DUAL
UNION ALL
SELECT 'A','물리' FROM DUAL)
SELECT A.NAME,RTRIM(LISTAGG(B.CLASS || ',') WITHIN GROUP(ORDER BY B.CLASS),',') CLASS
FROM TB_STUDENT A,TB_CLASS B
WHERE A.NAME = B.NAME
GROUP BY A.NAME
저는
A 수학,과학,체육,물리
B
이렇게 결과를 표시하고 싶은데, B는 TB_CLASS 테이블에 데이터가 없어서 나오질 않습니다.
어떻게 해결할 수 있을까요
부탁드립니다.
by 우리집아찌
[2018.09.20 16:16:23]
WITH
TB_STUDENT(NAME) AS
(SELECT 'A' FROM DUAL
UNION ALL
SELECT 'B' FROM DUAL
),
TB_CLASS(NAME,CLASS) AS
(SELECT 'A','수학' FROM DUAL
UNION ALL
SELECT 'A','과학' FROM DUAL
UNION ALL
SELECT 'A','체육' FROM DUAL
UNION ALL
SELECT 'A','물리' FROM DUAL)
SELECT A.NAME,RTRIM(LISTAGG(B.CLASS || ',') WITHIN GROUP(ORDER BY B.CLASS),',') CLASS
FROM TB_STUDENT A LEFT OUTER JOIN TB_CLASS B
ON A.NAME = B.NAME
GROUP BY A.NAME
by 마농
[2018.09.20 16:41:11]
SELECT a.name
, LISTAGG(b.class, ',') WITHIN GROUP(ORDER BY b.class) class
FROM tb_student a
LEFT OUTER JOIN tb_class b
ON a.name = b.name
GROUP BY a.name
ORDER BY a.name
;
) 버튼을 클릭하여 작성 하시면 됩니다.