1. CTE 이용
with menuTree(mnu_uid, mnu_name, mnu_directory, mnu_level, mnu_order, mnu_state, mnu_parentUid, sort)
as
( select M.mnu_uid
, M.mnu_name
, M.mnu_directory
, 0 as mnu_level
, M.mnu_order
, M.mnu_state
, M.mnu_parentUId
, CONVERT(VARBINARY(5), M.mnu_order)
from IBC_Menu M
where mnu_parentUid is null
union all
select M.mnu_uid
, M.mnu_name
, M.mnu_directory
, mnu_level + 1 as mnu_level
, M.mnu_order
, M.mnu_state
, M.mnu_parentUId
, CONVERT(VARBINARY(5), sort + CONVERT(BINARY(1), M.mnu_order))
from IBC_Menu M
inner join menuTree T on M.mnu_parentUid = T.mnu_uid
)
select *
from menuTree
order by sort
mnu_uid mnu_name mnu_directory mnu_level mnu_order mnu_state mnu_parentUid sort
----------- -------------------- -------------------- ----------- --------- --------- ------------- ------------
1 홈 main 0 1 1 NULL 0x01
2 생방송 onair 1 1 1 1 0x0101
3 생방송 onair 2 1 1 2 0x010101
4 생방송 일정 onair 2 2 1 2 0x010102
5 VOD vod 1 2 1 1 0x0102
6 김천소식 vod 2 1 1 5 0x010201
7 시정홍보 vod 2 2 1 5 0x010202
8 교양강좌 vod 2 3 1 5 0x010203
9 명소탐방 vod 2 4 1 5 0x010204
10 김천의명산물 vod 2 5 1 5 0x010205
11 공연예술정보 vod 2 6 1 5 0x010206
12 참여공간 part 1 3 1 1 0x0103
(12개 행 적용됨)
2. 참고
varbinary 데이타는 convert 되는 소스 data 크기에 따라 자동으로 증가 한다.
int -> 4byte 증가
tinyint -> 1byte 증가
3. varbinary 데이타간 더하기
select convert(varbinary(4), 1) + convert(varbinary(4), 1)------------------
0x0000000100000001
(1개 행 적용됨)
4. 다른 셈플 (가공된 컬럼은 데이터형이 일치해야 해서 CAST 했다.)
WITH MENU_TREE
AS
(
SELECT M.MENU_UID
, M.MENU_NAME
, M.MENU_DESC
--, M.MENU_SORT_ORDER
, M.MENU_ISENABLED
, M.MENU_CRTE_DT
, M.MENU_MDFY_DT
, M.MENU_CRTE_USER_ID
, M.MENU_MDFY_USER_ID
, 1 AS MENU_LEVEL
, CAST(RIGHT('0000' + CAST(M.MENU_SORT_ORDER AS VARCHAR), 4) AS VARCHAR(100)) AS MENU_SORT_ORDER
, CAST(M.MENU_NAME AS NVARCHAR(100)) AS MENU_PATH
FROM BLOG.BLOG_MENU M
WHERE M.MENU_UP_UID IS NULL
UNION ALL
SELECT M.MENU_UID
, M.MENU_NAME
, M.MENU_DESC
--, M.MENU_SORT_ORDER
, M.MENU_ISENABLED
, M.MENU_CRTE_DT
, M.MENU_MDFY_DT
, M.MENU_CRTE_USER_ID
, M.MENU_MDFY_USER_ID
, T.MENU_LEVEL + 1 AS MENU_LEVEL
, CAST(T.MENU_SORT_ORDER + RIGHT('0000' + CAST(M.MENU_SORT_ORDER AS VARCHAR), 4) AS VARCHAR(100)) AS MENU_SORT_ORDER
, CAST(T.MENU_PATH + ' > ' + M.MENU_NAME AS NVARCHAR(100)) AS MENU_PATH
FROM BLOG.BLOG_MENU M
JOIN MENU_TREE T ON T.MENU_UID = M.MENU_UP_UID
)
SELECT M.*
FROM MENU_TREE M
ORDER BY M.MENU_SORT_ORDER
참고로
SELECT CAST(문자열 AS VARCHAR)
처럼 VARCHAR 의 Byte사이즈 지정하지 않으면 VARCHAR(30) 이 default이다.
SELECT LEN('안녕하세요안녕하세요안녕하세요안녕하세요안녕하세요안녕하세요안녕하세요안녕하세요')
, CAST('안녕하세요안녕하세요안녕하세요안녕하세요안녕하세요안녕하세요안녕하세요안녕하세요' AS VARCHAR)
, CAST('안녕하세요안녕하세요안녕하세요안녕하세요안녕하세요안녕하세요안녕하세요안녕하세요' AS NVARCHAR)
----------- ------------------------------ ------------------------------
40 안녕하세요안녕하세요안녕하세요 안녕하세요안녕하세요안녕하세요안녕하세요안녕하세요안녕하세요
(1개 행 적용됨)