/********************************************************************************************
-- Title : [2k5] CTE에서의 정렬 처리
-- Reference : bol, dbrang
-- Key word : cte bom order 정렬
********************************************************************************************/
-- CTE에서 중간 트리 노드간에 정렬이 되지 않아 사용하는 팁이다.
-- 정녕 이렇게 써야 겠나...??
-- transaction 처리는 따로 하삼....
/*
-- 트리 구현
*/
USE Tempdb;
GO
DROP TABLE bom_t;
GO
CREATE TABLE bom_t
( fldr_id INT NOT NULL
, hghr_fldr_id INT NULL
, fldr_nm NVARCHAR(10) NOT NULL
, membr_cd NVARCHAR(10) NOT NULL
, note NVARCHAR(100) NULL
);
GO
INSERT INTO bom_t
SELECT 357325, NULL, 'aa','dbrang',NULL UNION ALL
SELECT 357326, NULL, 'bb','dbrang',NULL UNION ALL
SELECT 357328, NULL, 'cc','dbrang',NULL UNION ALL
SELECT 357329, NULL, 'dd','dbrang',NULL UNION ALL
SELECT 357330, 357326, 'bb1','dbrang',NULL UNION ALL
SELECT 357331, 357326, 'bb2','dbrang',NULL UNION ALL
SELECT 357332, 357328, 'cc1','dbrang',NULL UNION ALL
SELECT 357334, 357328, 'cc2','dbrang',NULL UNION ALL
SELECT 357335, 357325, 'aa1','dbrang',NULL UNION ALL
SELECT 357336, 357329, 'dd1','dbrang',NULL UNION ALL
SELECT 357337, 357330, 'bb11','dbrang',NULL UNION ALL
SELECT 357338, 357330, 'bb12','dbrang',NULL UNION ALL
SELECT 357339, 357330, 'bb13','dbrang',NULL UNION ALL
SELECT 357345, 357338, 'bbb1','dbrang',NULL UNION ALL
SELECT 357346, 357338, 'bbb2','dbrang',NULL;
GO
SELECT * FROM bom_t;
GO
WITH FolderTreeCte(fldr_id, hghr_fldr_id, fldr_nm, LEVEL, sort)
AS (SELECT fldr_id
, hghr_fldr_id
, CAST(N'↑ ' + fldr_nm AS NVARCHAR(1000))
, 2
, CAST(fldr_nm + cast(fldr_id as nvarchar(10)) AS NVARCHAR(1000))
FROM bom_t
WHERE membr_cd = N'dbrang'
AND hghr_fldr_id IS NULL
UNION ALL
SELECT a.fldr_id
, a.hghr_fldr_id
, CAST(REPLICATE(N'↑ ', c.LEVEL) + a.fldr_nm AS NVARCHAR(1000))
, c.LEVEL + 1
, CAST(c.sort + N' | '+ a.fldr_nm + CAST(a.fldr_id AS NVARCHAR(10)) AS NVARCHAR(1000))
/*a.fldr_nm만 가지고 정렬하면 특정 폴더를 루트로 복사시 정렬이 안되어 a.fldr_id를 붙였다.*/
FROM bom_t a
INNER JOIN FolderTreeCte c
ON a.hghr_fldr_id = c.fldr_id
)
SELECT fldr_id, hghr_fldr_id, fldr_nm, LEVEL, sort
FROM FolderTreeCte
ORDER BY sort;
/*
재귀 쿼리 "FolderTreeCte"의 열 "sort"에 있는 앵커 부분과 재귀 부분 간의 유형이 일치하지 않습니다.
라는 에러는 CAST( AS NVARCHAR(1000))으로 해결봤다.
즉, 앵커와 재귀간의 테이터 타입이나 크기가 안 맞으면 나오는 에러라는 얘기징...ㅡ,.ㅡ
*/
GO
/*
-- 철인 28호(정00과장)가 VARBINARY()를 써서 정렬한 예--
*/
WITH srvy_item(id, lvl, prnt_id, [desc], seq)
AS
(
SELECT
id
, lvl = 0
, prnt_id
, [desc]
, seq = CAST(sort_ord AS VARBINARY(MAX))
FROM sch_xset.membr_jnng_srvy_item
WHERE prnt_id IS NULL
UNION ALL
SELECT
item.id
, lvl= (srvy_item.lvl + 1)
, item.prnt_id
, item.[desc]
, seq = CONVERT(VARBINARY(MAX), srvy_item.seq + CAST(item.sort_ord AS BINARY(4)))
FROM sch_xset.membr_jnng_srvy_item AS item
INNER JOIN srvy_item
ON item.prnt_id = srvy_item.id
)
SELECT
display = CONVERT(NVARCHAR(MAX), REPLICATE(N' ', lvl) + CASE WHEN lvl > 0 THEN N'└' ELSE '' END + [desc])
, id
, lvl = CONVERT(TINYINT ,lvl)
, prnt_id
, [desc]
, seq
FROM srvy_item
/*
-- 트리 복사 구현
-- 이동은 부모 폴더 아이디만 간단히 변경하면 되니깡...
*/
DECLARE @membr_cd NVARCHAR(20), @fldr_id INT, @mv_fldr_id INT; --프로시저 변수
DECLARE @max_fldr_id INT;
DECLARE @tmp_rslt TABLE
( seq INT
, fldr_id INT
, hghr_fldr_id INT
, fldr_nm NVARCHAR(1000)
, LEVEL INT
, sort NVARCHAR(1000)
);
SELECT @membr_cd = 'dbrang', @fldr_id = 357338, @mv_fldr_id = 357328;
SELECT @max_fldr_id = MAX(fldr_id) FROM bom_t WITH (XLOCK, TABLOCK);
WITH FolderTreeCte(fldr_id, hghr_fldr_id, fldr_nm, LEVEL, sort)
AS (SELECT fldr_id
, hghr_fldr_id
, CAST(fldr_nm AS NVARCHAR(1000))
, 2
, CAST(fldr_nm AS NVARCHAR(1000))
FROM bom_t
WHERE fldr_id = @fldr_id
UNION ALL
SELECT a.fldr_id
, a.hghr_fldr_id
, CAST(a.fldr_nm AS NVARCHAR(1000))
, c.LEVEL + 1
, CAST(c.sort + N' | '+ a.fldr_nm AS NVARCHAR(1000))
FROM bom_t a
INNER JOIN FolderTreeCte c
ON a.hghr_fldr_id = c.fldr_id
)
INSERT INTO @tmp_rslt
SELECT ROW_NUMBER() OVER(ORDER BY sort) "seq",
fldr_id, hghr_fldr_id, fldr_nm, LEVEL, sort
FROM FolderTreeCte
ORDER BY sort;
-- 추출된 트리 구조에서 self join으로 자식 폴더만 추출한후
-- 원본과 outer join으로 최종 결과셋을 추출한다.
INSERT INTO bom_t
SELECT @max_fldr_id + c.seq "new_fldr_id"
, ISNULL(@max_fldr_id + u.seq, @mv_fldr_id) "new_hghr_fldr_id"
/*최상위는 left join에서 null값이 된다. 그래서 null 부분에 옮겨갈 폴더 ID로 치환.*/
, c.fldr_nm "fldr_nm"
, @membr_cd "membr_cd"
, NULL "note"
--, c.fldr_id "old_fldr_id"
--, c.hghr_fldr_id "old_hghr_fldr_id"
FROM @tmp_rslt c
LEFT OUTER JOIN (SELECT a.seq, b.fldr_id, b.hghr_fldr_id
FROM @tmp_rslt a
INNER JOIN @tmp_rslt b
ON a.fldr_id = b.hghr_fldr_id
) u
ON c.fldr_id = u.fldr_id;
GO