by momo [SQLServer] SQLServer MSSQL [2020.06.15 00:34:24]
안녕하세요. mssql로 계층형 쿼리를 작성하면서 잘 안되는 부분이 있어서 질문드립니다.
with Tree(CTG_CD, CTG_NM, CTG_LNK, PNT_CTG_CD, CTG_SEQ, CTG_STEP, LEVEL, depth_fullname) as (
SELECT CTG_CD, CTG_NM, CTG_LNK, PNT_CTG_CD, CTG_SEQ, CTG_STEP, 1 as LEVEL, convert(varchar(255), CTG_NM) depth_fullname
FROM TCM_CTG_M
WHERE PNT_CTG_CD IN (SELECT CTG_CD
FROM TCM_CTG_M
WHERE PNT_CTG_CD = 'FRT001002001')
AND USE_YN = 'Y'
UNION ALL
SELECT A.CTG_CD, A.CTG_NM, A.CTG_LNK, A.PNT_CTG_CD, A.CTG_SEQ, A.CTG_STEP, LEVEL+1 as LEVEL
, convert(varchar(255), convert(nvarchar,B.depth_fullname) + ' > ' + convert(varchar(255), A.CTG_NM)) depth_fullname
FROM TCM_CTG_M A
INNER JOIN Tree B ON A.PNT_CTG_CD = B.CTG_CD
WHERE USE_YN = 'Y'
)
SELECT T.CTG_CD as ctgCd, T.CTG_NM as ctgNm, T.PNT_CTG_CD as pCtgCd
, T.CTG_lnk, t.CTG_STEP
, ISNULL(CNT.CNT_SN,'0') as contentsNum, CNT.CNT_LNK as contentsLnk,'FRT001002001' as mnuCd
, CNT.CNT_TYPE as cntType, CNT.QCK_LNK_YN as qckLnk, CNT.CNT_TMP_TYPE as cntTmpType
, LEVEL, depth_fullname
FROM Tree T LEFT OUTER JOIN TCT_CNT_M CNT ON T.CTG_LNK = CNT.CNT_SN
ORDER BY T.CTG_CD, T.CTG_SEQ
OPTION (MaxRecursion 3)
현재 이러한 쿼리로 계층형 쿼리를 작성하여 웹페이지에서 활용하고 있으며, 해당 쿼리를 사용했을 때의 결과는 아래와 같습니다.
CTG001001 근태 CTG001 1 2 1 /contentsDetail FRT001002001 CNT Y A 1 근태
CTG001002 전배 CTG001 2 0 NULL FRT001002001 NULL NULL NULL 1 전배
CTG001002001 테스트 CTG001002 28 3 28 /contentsDetail FRT001002001 CNT N A 2 전배 > 테스트
CTG001002002 테스트2 CTG001002 3 0 NULL FRT001002001 NULL NULL NULL 2 전배 > 테스트2
CTG001003 직급 하향 CTG001 2 0 NULL FRT001002001 NULL NULL NULL 1 직급 하향
CTG001003001 테스트입니다 CTG001003 3 0 NULL FRT001002001 NULL NULL NULL 2 직급 하향 > 테스트입니다
CTG001004 휴직 CTG001 2 0 NULL FRT001002001 NULL NULL NULL 1 휴직
CTG001004001 휴직 CTG001004 3 0 NULL FRT001002001 NULL NULL NULL 2 휴직 > 휴직
CTG001005 근로 시간 단축 CTG001 2 0 NULL FRT001002001 NULL NULL NULL 1 근로 시간 단축
CTG001005001 근로 시간 단축 CTG001005 3 0 NULL FRT001002001 NULL NULL NULL 2 근로 시간 단축 > 근로 시간 단축
쿼리를 통해 해당 컬럼의 depth name과 level까지는 구했는데, 하위계층의 개수를 구해 상위 계층 옆에다가 표시하려고 하는곳에서 계속 막히고 있습니다. 예를 들어 전배라는 상위계층에 테스트, 테스트2라는 하위계층이 존재하면 2개가 존재하는 것이므로
CTG001002 전배 CTG001 2 0 NULL FRT001002001 NULL NULL NULL 1 전배 2
CTG001002001 테스트 CTG001002 28 3 28 /contentsDetail FRT001002001 CNT N A 2 전배 > 테스트 0
CTG001002002 테스트2 CTG001002 3 0 NULL FRT001002001 NULL NULL NULL 2 전배 > 테스트2 0
이렇게 표현해보고 싶습니다. 어떻게 쿼리를 짜야 갯수를 표현할 수 있을까요?
GROUP BY COUNT는 생각을 못하고 있었습니다. 하루종일 고민했었는데 덕분에 해결했습니다. 너무 감사드립니다!!
with Tree(CTG_CD, CTG_NM, CTG_LNK, PNT_CTG_CD, CTG_SEQ, CTG_STEP, LEVEL, depth_fullname) as (
SELECT CTG_CD, CTG_NM, CTG_LNK, PNT_CTG_CD, CTG_SEQ, CTG_STEP, 1 as LEVEL, convert(varchar(255), CTG_NM) depth_fullname
FROM TCM_CTG_M
WHERE PNT_CTG_CD IN (SELECT CTG_CD
FROM TCM_CTG_M
WHERE PNT_CTG_CD = 'FRT001002001')
AND USE_YN = 'Y'
UNION ALL
SELECT A.CTG_CD, A.CTG_NM, A.CTG_LNK, A.PNT_CTG_CD, A.CTG_SEQ, A.CTG_STEP, LEVEL+1 as LEVEL
, convert(varchar(255), convert(nvarchar,B.depth_fullname) + ' > ' + convert(varchar(255), A.CTG_NM)) depth_fullname
FROM TCM_CTG_M A
INNER JOIN Tree B ON A.PNT_CTG_CD = B.CTG_CD
WHERE USE_YN = 'Y'
)
SELECT T.CTG_CD as ctgCd, T.CTG_NM as ctgNm, T.PNT_CTG_CD as pCtgCd
, T.CTG_lnk, t.CTG_STEP
, ISNULL(CNT.CNT_SN,'0') as contentsNum, CNT.CNT_LNK as contentsLnk,'FRT001002001' as mnuCd
, CNT.CNT_TYPE as cntType, CNT.QCK_LNK_YN as qckLnk, CNT.CNT_TMP_TYPE as cntTmpType
, T.LEVEL, depth_fullname, COUNT(CTG.PNT_CTG_CD) lowCount
FROM Tree T LEFT OUTER JOIN TCT_CNT_M CNT ON T.CTG_LNK = CNT.CNT_SN
LEFT OUTER JOIN TCM_CTG_M CTG ON T.CTG_CD = CTG.PNT_CTG_CD
GROUP BY T.CTG_CD, T.CTG_NM, T.PNT_CTG_CD
, T.CTG_lnk, t.CTG_STEP
, ISNULL(CNT.CNT_SN,'0'), CNT.CNT_LNK
, CNT.CNT_TYPE, CNT.QCK_LNK_YN, CNT.CNT_TMP_TYPE
, T.LEVEL, depth_fullname
ORDER BY T.CTG_CD
OPTION (MaxRecursion 3)