티스토리 뷰

Dev/MSSQL

CTE(Comman Table Expression)

jami 2013. 7. 23. 09:32

계층구조 쿼리에 사용.

참조 : http://msdn.microsoft.com/en-us/library/ms186243%28v=sql.105%29.aspx


sample

    WITH CTE_AE_CODE_H AS (
       SELECT *
            , 0 AS LEVEL
            , CAST(RANK AS DECIMAL(20)) AS SORT --소팅(문자열로 변환 후 소팅해야 함)
            , CAST(CODE_NAME AS VARCHAR(1000)) AS CODE_PATH
       FROM IAMS.DBO.AE_CODE_H
       WHERE 1 = 1
         AND UP_CODE_ID = '*'
         --AND UP_CODE_ID = 'ROOT_ASSET_TYPE'
      
       UNION ALL
       SELECT A.*
            , R.LEVEL + 1 AS LEVEL
            , CAST(R.SORT * 1000 + A.RANK AS DECIMAL(20)) AS SORT --소팅(문자열로 변환 후 소팅해야 함)
            , CAST(R.CODE_PATH+' > '+A.CODE_NAME AS VARCHAR(1000)) AS CODE_PATH
       FROM IAMS.DBO.AE_CODE_H A
       INNER JOIN CTE_AE_CODE_H R ON A.UP_CODE_ID = R.CODE_ID
    )
    SELECT UP_CODE_ID
         , CODE_ID
         , CODE_PATH
         , CASE WHEN LEVEL = 0 THEN '' ELSE REPLICATE(' ', LEVEL) + '└' END + CODE_NAME
      FROM CTE_AE_CODE_H
     WHERE 1 = 1
       --AND LEVEL = 2
     ORDER BY CAST(SORT AS VARCHAR(100)) ASC
    ;


'Dev > MSSQL' 카테고리의 다른 글

[MSSQL] truncate 권한부여  (0) 2013.07.23
댓글
최근에 올라온 글
최근에 달린 댓글
Total
Today
Yesterday
«   2024/03   »
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31
글 보관함