테이블 구조
아래와 같이 DB에 저장된 상태입니다.
seq | pk_id | depth | foldername |
9 | 7 | 2 | 21A063 |
10 | 8 | 7 | UV LASER CUT SYSTEM |
11 | 9 | 8 | 00_SOFTWARE_UNIT |
12 | 10 | 9 | *01_DIGITAL_IO |
13 | 11 | 9 | *02_INDUSTRIAL_PC |
15 | 13 | 8 | 20_WORK TABLE UNIT |
17 | 14 | 9 | *03_VISION |
이걸 Tree 구조로 보면 아래 그림과 같습니다. 여기까지는 작업을 했는데
제가 원하는 건 저 DB에 저장된 21A063 이란 폴더및 하위폴더 전체를 아래와 같이 새롭게 Insert 하고 싶습니다.
seq, pk_id, depth 만 변경해서, 폴더명은 그래도..
쿼리문을 사용해서 이게 가능할까요?
Seq 는 자동증가, pk_id도 가장 마지막 번호 + 1 해서 자동 증가
depth 는 pk_id 별로 번호 부여...
seq | pk_id | depth | foldername |
18 | 15 | 2 | 21A063 |
19 | 16 | 15 | UV LASER CUT SYSTEM |
20 | 17 | 16 | 00_SOFTWARE_UNIT |
21 | 18 | 17 | *01_DIGITAL_IO |
22 | 19 | 17 | *02_INDUSTRIAL_PC |
23 | 20 | 16 | 20_WORK TABLE UNIT |
24 | 21 | 20 | *03_VISION |
-- Oracle -- WITH t AS ( SELECT 9 seq, 7 pk_id, 2 depth, '21A063' foldername FROM dual UNION ALL SELECT 10, 8, 7, 'UV LASER CUT SYSTEM' FROM dual UNION ALL SELECT 11, 9, 8, '00_SOFTWARE_UNIT' FROM dual UNION ALL SELECT 12, 10, 9, '*01_DIGITAL_IO' FROM dual UNION ALL SELECT 13, 11, 9, '*02_INDUSTRIAL_PC' FROM dual UNION ALL SELECT 15, 13, 8, '20_WORK TABLE UNIT' FROM dual UNION ALL SELECT 17, 14, 9, '*03_VISION' FROM dual ) SELECT seq , pk_id , NVL(PRIOR pk_id, depth) depth , foldername FROM (SELECT (SELECT MAX(seq ) FROM t) + ROWNUM seq , (SELECT MAX(pk_id) FROM t) + ROWNUM pk_id , pk_id old_pk_id , depth , foldername FROM t START WITH pk_id = 7 CONNECT BY PRIOR pk_id = depth ) START WITH old_pk_id = 7 CONNECT BY PRIOR old_pk_id = depth ;
-- database 버전은 미리 알려주셔야할꺼 같아요. WITH T (seq, pk_id, depth, foldername) AS ( SELECT 9 , 7, 2, '21A063' UNION ALL SELECT 10, 8, 7, ' UV LASER CUT SYSTEM' UNION ALL SELECT 11, 9, 8, ' 00_SOFTWARE_UNIT' UNION ALL SELECT 12, 10, 9, ' *01_DIGITAL_IO' UNION ALL SELECT 13, 11, 9, ' *02_INDUSTRIAL_PC' UNION ALL SELECT 15, 13, 8, ' 20_WORK TABLE UNIT' UNION ALL SELECT 17, 14, 9, ' *03_VISION' ), TT AS ( SELECT MAX(seq) OVER() + ROW_NUMBER() OVER (ORDER BY seq) seq , MAX(pk_id) OVER() + ROW_NUMBER() OVER (ORDER BY seq) pk_id , CASE WHEN foldername = '21A063' THEN depth ELSE COUNT(seq) OVER() + 1 + depth END depth , foldername FROM T ) SELECT seq, pk_id, depth, foldername FROM TT WHERE depth = 2 UNION ALL SELECT MAX(T1.seq) seq, MAX(T1.pk_id) pk_id, MAX(T3.pk_id) pk_id, T1.foldername FROM TT T1, TT T2, TT T3 WHERE T1.depth = T2.pk_id AND T2.depth = T3.depth AND T1.pk_id > T3.pk_id GROUP BY T1.foldername ORDER BY seq
-- MSSQL -- WITH t AS ( SELECT 9 seq, 7 pk_id, 2 depth, '21A063' foldername UNION ALL SELECT 10, 8, 7, 'UV LASER CUT SYSTEM' UNION ALL SELECT 11, 9, 8, '00_SOFTWARE_UNIT' UNION ALL SELECT 12, 10, 9, '*01_DIGITAL_IO' UNION ALL SELECT 13, 11, 9, '*02_INDUSTRIAL_PC' UNION ALL SELECT 15, 13, 8, '20_WORK TABLE UNIT' UNION ALL SELECT 17, 14, 9, '*03_VISION' ) , t1 AS ( -- 1. 특정 폴더 및 하위 폴더 가져오기 -- SELECT * FROM t WHERE pk_id = 7 UNION ALL SELECT a.* FROM t a , t1 b WHERE b.pk_id = a.depth ) , t2 AS ( -- 2. 새로운 번호(seq, pk_id) 부여하기 -- SELECT (SELECT MAX(seq ) FROM t) + ROW_NUMBER() OVER(ORDER BY seq) seq , (SELECT MAX(pk_id) FROM t) + ROW_NUMBER() OVER(ORDER BY seq) pk_id , pk_id old_pk_id , depth , foldername FROM t1 ) -- 3. depth 가져오기 -- SELECT a.seq , a.pk_id , ISNULL(b.pk_id, a.depth) depth , a.foldername FROM t2 a LEFT OUTER JOIN t2 b ON a.depth = b.old_pk_id ;