database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
Oracle
Informix
Sybase
ㆍMS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
MS-SQL Q&A 6991 게시물 읽기
No. 6991
CTE 정렬 좀 봐주세요..ㅠㅠ
작성자
테르
작성일
2016-06-24 09:39
조회수
3,567

안녕하세요.. MSSQL(2012)은 처음 사용해보는데 해결이 잘 안되서 전문가 분들께 질문좀 드립니다..ㅠㅠ

 

화면에서 메뉴 관리를 하려고 합니다. 메뉴 중간에 다른 메뉴가 생겨 들어올수도 있기 때문에 정렬값을 두려고 합니다.

 

그런데 정렬을 어떻게 해야 할지 모르겠습니다.

 

아래는 관련 내용입니다.

 

CREATE TABLE TB_MENU

(

MENU_ID int NOT NULL PRIMARY KEY,

UPPER_MENU_ID int NOT NULL ,

MENU_NM varchar(50) NOT NULL,

SORT_NO int NOT NULL

);

 

INSERT INTO TB_MENU(MENU_ID, UPPER_MENU_ID, MENU_NM, SORT_NO) VALUES(1, 0, '메뉴 1', 10);

INSERT INTO TB_MENU(MENU_ID, UPPER_MENU_ID, MENU_NM, SORT_NO) VALUES(2, 1, '메뉴 1-1', 99);

INSERT INTO TB_MENU(MENU_ID, UPPER_MENU_ID, MENU_NM, SORT_NO) VALUES(3, 1, '메뉴 1-2', 99);

INSERT INTO TB_MENU(MENU_ID, UPPER_MENU_ID, MENU_NM, SORT_NO) VALUES(4, 0, '메뉴 3', 99);

INSERT INTO TB_MENU(MENU_ID, UPPER_MENU_ID, MENU_NM, SORT_NO) VALUES(5, 4, '메뉴 3-2', 99);

INSERT INTO TB_MENU(MENU_ID, UPPER_MENU_ID, MENU_NM, SORT_NO) VALUES(6, 4, '메뉴 3-1', 70);

INSERT INTO TB_MENU(MENU_ID, UPPER_MENU_ID, MENU_NM, SORT_NO) VALUES(7, 0, '메뉴 2', 50);

 

[원하는 결과]

메뉴 1

메뉴 1-1

메뉴 1-2

메뉴 2

메뉴 3

메뉴 3-1

메뉴 3-2

 

검색 하여 아래와 같이 쿼리를 만들었는데 정렬에서 막힙니다.

WITH RECURSIVE_MENU(MENU_ID, UPPER_MENU_ID, MENU_NM, SORT_NO, LEVEL) AS (

SELECT MENU_ID

, UPPER_MENU_ID

, MENU_NM

, SORT_NO

, 1

FROM TB_MENU

WHERE UPPER_MENU_ID = 0

UNION ALL

SELECT A.MENU_ID

, A.UPPER_MENU_ID

, A.MENU_NM

, A.SORT_NO

, B.LEVEL + 1

FROM TB_MENU A, RECURSIVE_MENU B

WHERE A.UPPER_MENU_ID = B.MENU_ID

)

SELECT MENU_ID

, UPPER_MENU_ID

, MENU_NM

, SORT_NO

, LEVEL

FROM RECURSIVE_MENU

 

- 동일한 메뉴 레벨에서는 SORT_NO 가 1순위, SORT_NO가 같을 경우는 MENU_ID 등록 순(2순위)으로 나오게 하고 싶습니다.

 

도움 좀 부탁 드리겠습니다.ㅠㅠ

 

이 글에 대한 댓글이 총 3건 있습니다.

;with tblA(MENU_ID,UPPER_MENU_ID,MENU_NM,SORT_NO,UPPER_MENU_NM,SEQ,UPPER_SEQ,LEVL) As
(
      Select a.*
            ,Convert(varchar(max),'')
            ,Convert(Integer,1)
            ,Convert(Integer,ROW_NUMBER() Over (Order By a.MENU_NM Asc))
            ,1
        From TB_MENU a
       Where a.UPPER_MENU_ID = 0
      
   Union All     

      Select a.*
            ,Convert(varchar(max),b.MENU_NM)
            ,Convert(Integer,ROW_NUMBER() Over (Partition By b.MENU_NM Order By a.MENU_NM Asc))
            ,b.UPPER_SEQ
            ,b.LEVL + 1
        From TB_MENU a
                       Inner Join tblA b
                                         On a.UPPER_MENU_ID = b.MENU_ID
                                           
       Where a.UPPER_MENU_ID <> 0  
)
,tblB(MENU_ID,UPPER_MENU_ID,MENU_NM,SORT_NO,UPPER_MENU_NM,SEQ,UPPER_SEQ,LEVL,SERIAL) As
(
      Select a.*
            ,  Convert(varchar(max),a.UPPER_SEQ)
             + Replicate('1',a.LEVL-1)
             + Convert(varchar(max),a.SEQ)
       From tblA a
)
      Select a.*
        From tblB a
    Order By a.SERIAL ASC
 

 

최한영(terry0515)님이 2016-06-24 11:45에 작성한 댓글입니다.

WITH tb_menu(menu_id, upper_menu_id, menu_nm, sort_no) AS
(
          SELECT 1, 0, '메뉴 1'  , 10
UNION ALL SELECT 2, 1, '메뉴 1-1', 99
UNION ALL SELECT 3, 1, '메뉴 1-2', 99
UNION ALL SELECT 4, 0, '메뉴 3'  , 99
UNION ALL SELECT 5, 4, '메뉴 3-2', 99
UNION ALL SELECT 6, 4, '메뉴 3-1', 70
UNION ALL SELECT 7, 0, '메뉴 2'  , 50
)
, recursive_menu AS
(
SELECT menu_id, upper_menu_id, menu_nm, sort_no
     , 1 lv
     , CAST(RIGHT(CONCAT('0', sort_no), 2) AS VARCHAR) sort_str
  FROM tb_menu
 WHERE upper_menu_id = 0
 UNION ALL
SELECT c.menu_id, c.upper_menu_id, c.menu_nm, c.sort_no
     , p.lv + 1 lv
     , CAST(
       CONCAT(p.sort_str, RIGHT(CONCAT('0', c.sort_no), 2))
       AS VARCHAR) sort_str
  FROM recursive_menu p
 INNER JOIN tb_menu c
    ON p.menu_id = c.upper_menu_id
)
SELECT *
  FROM recursive_menu
 ORDER BY sort_str, menu_id
;

마농(manon94)님이 2016-07-05 10:24에 작성한 댓글입니다.
이 댓글은 2016-07-05 10:24에 마지막으로 수정되었습니다.

 -- 소트를 하나더 추가해 봤습니다...

 
WITH RECURSIVE_MENU(MENU_ID, UPPER_MENU_ID, MENU_NM, SORT_NO, SORT_NO2, LEVEL) AS (
SELECT MENU_ID
, UPPER_MENU_ID
, MENU_NM
, SORT_NO
, 0 SORT_NO2
, 1
 
FROM #TB_MENU
WHERE UPPER_MENU_ID = 0
 
UNION ALL
 
SELECT A.MENU_ID
, A.UPPER_MENU_ID
, A.MENU_NM
, B.SORT_NO   AS SORT_NO
, A.SORT_NO   AS SORT_NO2
, B.LEVEL + 1
FROM #TB_MENU A, RECURSIVE_MENU B
WHERE A.UPPER_MENU_ID = B.MENU_ID
 
)
 
SELECT MENU_ID
, UPPER_MENU_ID
, MENU_NM
, SORT_NO
, SORT_NO2
, LEVEL
FROM RECURSIVE_MENU
ORDER BY SORT_NO , SORT_NO2, MENU_ID
 
 
플러스3000님이 2016-07-08 10:28에 작성한 댓글입니다. Edit
[Top]
No.
제목
작성자
작성일
조회
6994PROCEDURE 만들때 USE
김우성
2016-07-19
3373
6993트리거를 실 업무에서 사용하지 않는 이유는 뭔가요? [2]
morningtak
2016-07-18
3564
6992SQL 2008 R2 스탠다드 라이센스 관련 [1]
윤병호
2016-07-04
3466
6991CTE 정렬 좀 봐주세요..ㅠㅠ [3]
테르
2016-06-24
3567
6990float 형식의 소수점값에 between을 사용하여 결과값을 가져오고 싶습니다. [1]
둥둥이
2016-06-16
4064
6989쿼리 질문드립니다. [2]
boco11
2016-06-10
3532
6986MS-SQL 컨버전
강형우
2016-06-01
3549
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.046초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다