계층형 메뉴 쿼리 소트
WITH tree_query AS (
SELECT
menu_no,
menu_parent,
menu_name,
menu_depth,
menu_url,
convert(varchar(255), menu_no) sort,
convert(varchar(255), menu_name) depth_fullname
FROM seprob_menu
WHERE menu_no = '1000001'
UNION ALL
SELECT
B.menu_no,
B.menu_parent,
B.menu_name,
B.menu_depth,
B.menu_url,
convert(varchar(255), convert(nvarchar,C.sort) + ' > ' + convert(varchar(255), B.menu_no)) sort,
convert(varchar(255), convert(nvarchar,C.depth_fullname) + ' > ' + convert(varchar(255), B.menu_name)) depth_fullname
FROM seprob_menu B, tree_query C
WHERE B.menu_parent = C.menu_no
)
SELECT menu_no, menu_name, menu_depth, menu_parent, menu_url, depth_fullname FROM tree_query order by SORT
DB에 저장된 계층형 메뉴를 위와 같은 쿼리로 불러오면 아래와 같은 결과가 나옵니다.
menu_no menu_name menu_depth menu_parent menu_url depth_fullname
100001 1차메뉴 1 0 0 1차메뉴
200001 2차메뉴1 2 100001 0 1차메뉴 > 2차메뉴1
300001 3차메뉴1 3 200001 0 1차메뉴 > 2차메뉴1 > 3차메뉴1
200002 2차메뉴2 2 100001 0 1차메뉴 > 2차메뉴2
300002 3차메뉴2 3 200002 0 1차메뉴 > 2차메뉴2 > 3차메뉴2
sort 순서를
100001 > 200002 > 300002 > 200001 > 300001
혹은
200002 > 300002 > 200001 > 300001 > 100001
순으로 출력이 되게 쿼리문을 수정하려면 어떻게 하면 될까요??
댓글 달기