이렇게 하면 첨부한 사진처럼 결과값이 나오는데요
원하는 결과 값은
전체 사용테이블 현행화 미현행화 미사용테이블 현행화 미현행화 미파악 현행화 미현행화
10 2 2 0 5 3 2 3 0 3
이런 모습입니다. 어떻게 해야될까요?
소스입니다.
SELECT A.GUBUN AS 구분 , COUNT(A.CUR_YN) 전체건수 , COUNT(CASE WHEN A.CUR_YN = 'Y' THEN 1 END ) 현행화 , COUNT(CASE WHEN A.CUR_YN = 'N' THEN 1 END ) 미현행화 , COUNT(DISTINCT CASE WHEN A.USE_YN = 'Y' THEN A.TB_PHY_NAME END) AS "사용테이블" , COUNT(DISTINCT CASE WHEN A.USE_YN = 'N' THEN A.TB_PHY_NAME END) AS "미사용테이블" , COUNT(DISTINCT CASE WHEN A.USE_YN = 'X' THEN A.TB_PHY_NAME END) AS "미파악테이블" FROM ( SELECT A.GUBUN , A.USE_YN , A.TB_PHY_NAME , MIN(CASE WHEN B.TB_PHY_NAME IS NOT NULL THEN 'Y' ELSE 'N' END ) AS CUR_YN -- 현행화 여부 ----------------------------------------------------------- FROM TB_DB_LIST A LEFT OUTER JOIN TB_ERD_LIST B ON A.TB_PHY_NAME = B.TB_PHY_NAME AND A.COL_PHY_NAME = B.COL_PHY_NAME AND A.COL_TYPE = B.COL_TYPE AND A.PK_YN = B.PK_YN GROUP BY A.TB_PHY_NAME , A.GUBUN , A.USE_YN ) A GROUP BY A.GUBUN ;
SELECT gubun AS 구분 , COUNT(*) AS 전체건수 , COUNT(CASE WHEN use_yn = 'Y' THEN 1 END) AS 사용_소계 , COUNT(CASE WHEN use_yn = 'Y' AND cur_yn = 'Y' THEN 1 END) AS 사용_현행화 , COUNT(CASE WHEN use_yn = 'Y' AND cur_yn = 'N' THEN 1 END) AS 사용_미현행화 , COUNT(CASE WHEN use_yn = 'N' THEN 1 END) AS 미사용_소계 , COUNT(CASE WHEN use_yn = 'N' AND cur_yn = 'Y' THEN 1 END) AS 미사용_현행화 , COUNT(CASE WHEN use_yn = 'N' AND cur_yn = 'N' THEN 1 END) AS 미사용_미현행화 , COUNT(CASE WHEN use_yn = 'X' THEN 1 END) AS 미파악_소계 , COUNT(CASE WHEN use_yn = 'X' AND cur_yn = 'Y' THEN 1 END) AS 미파악_현행화 , COUNT(CASE WHEN use_yn = 'X' AND cur_yn = 'N' THEN 1 END) AS 미파악_미현행화 FROM (SELECT a.gubun , a.tb_phy_name , a.use_yn , NVL2(MIN(b.tb_phy_name), 'Y', 'N') cur_yn AS -- 현행화 여부 FROM tb_db_list a LEFT OUTER JOIN tb_erd_list b ON a.tb_phy_name = b.tb_phy_name AND a.col_phy_name = b.col_phy_name AND a.col_type = b.col_type AND a.pk_yn = b.pk_yn GROUP BY a.gubun , a.tb_phy_name , a.use_yn ) a GROUP BY a.gubun ;