초보자의 쿼리 질문입니다. 0 2 1,125

by 손님 [2008.04.17 20:24:33]


-- 목차부문
SELECT   ’0’, IN_OR_OUT_CD,
         DECODE (SUBSTR (CLASS_MARK, 1, 2), ’31’, ’S’, ’T’) AS STATIS_TYPE,
         COUNT (C.CNO) CONTENTS_JONGSU
    FROM S_CATALOGUE C, S_COPY_VOL A
   WHERE (C.CNO) IN (SELECT DISTINCT CNO
                                FROM S_BOOK_CONTENTS
                               WHERE REG_DATE >= ’19000101’
                                 AND REG_DATE <= ’20080415’)
     AND A.BOOK_STATE_CD IN (’1’, ’2’, ’3’, ’4’, ’5’)
     AND PUBLIC_YN <> ’6’
     AND C.CNO = A.CNO
     AND (C.MAT_TYPE = ’B’ OR C.MAT_TYPE = ’AB’)
GROUP BY ’0’,
         C.IN_OR_OUT_CD,
         DECODE (SUBSTR (C.CLASS_MARK, 1, 2), ’31’, ’S’, ’T’)  

-- 기사부문
SELECT   ’0’, IN_OR_OUT_CD,
         DECODE (SUBSTR (CLASS_MARK, 1, 2), ’31’, ’S’, ’T’) AS STATIS_TYPE,
         COUNT (C.CNO) ARTICLE_JONGSU 
    FROM S_CATALOGUE C, S_COPY_VOL A
   WHERE (C.CNO) IN (SELECT DISTINCT CNO
                                FROM S_BOOK_ARTICLE
                               WHERE REG_DATE >= ’19000101’
                                 AND REG_DATE <= ’20080415’)
     AND A.BOOK_STATE_CD IN (’1’, ’2’, ’3’, ’4’, ’5’)
     AND PUBLIC_YN <> ’6’
     AND C.CNO = A.CNO
     AND (C.MAT_TYPE = ’B’ OR C.MAT_TYPE = ’AB’)
GROUP BY ’0’,
         C.IN_OR_OUT_CD,
         DECODE (SUBSTR (C.CLASS_MARK, 1, 2), ’31’, ’S’, ’T’)

쿼리를 작성했습니다.

각각의 결과값은 ’0’ / IN_OR_OUT_CD / STATIS_TYPE / JONGSU 순으로 나옵니다.

그런데 결과값을 이 두쿼리가 합쳐져서..

’0’ /  IN_OR_OUT_CD / STATIS_TYPE / CONTENTS_JONGSU / ARTICLE_JONGSU

이렇게 나오게 하고 싶습니다.

어떤 방법이 있는지요? 머리가 나쁜건지? 간단할것 같으면서도.. 안떠오릅니다..

by 마농 [2008.04.20 08:30:57]
SELECT '0'
, in_or_out_cd
, statis_type
, SUM(contents_jongsu) contents_jongsu
, SUM(article_jongsu) article_jongsu
FROM
(
SELECT c.in_or_out_cd
, DECODE(SUBSTR(c.class_mark,1,2),'31','S','T') AS statis_type
, COUNT(*) contents_jongsu
, 0 article_jongsu
FROM ...
WHERE ...
GROUP BY c.in_or_out_cd
, DECODE(SUBSTR(c.class_mark,1,2),'31','S','T')
UNION ALL
SELECT c.in_or_out_cd
, DECODE(SUBSTR(c.class_mark,1,2),'31','S','T') AS statis_type
, 0 contents_jongsu
, COUNT(*) article_jongsu
FROM ...
WHERE ...
GROUP BY c.in_or_out_cd
, DECODE(SUBSTR(c.class_mark,1,2),'31','S','T')
)
GROUP BY in_or_out_cd, statis_type

by 장현수 [2008.04.22 09:43:00]
감사합니다.. ^^*
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입