데이터베이스 질문과 답변
SELECT '70784256' acd, '2594493' cd, '2594493' pcd, 0 depth, NULL seq union all SELECT '70779557' acd, '2594251' cd, '2594251' pcd, 0 depth, NULL seq union all SELECT '70779556' acd, '2594252' cd, '2594251' pcd, 1 depth, 3 seq union all SELECT '70779555' acd, '2594253' cd, '2594251' pcd, 1 depth, 2 seq union all SELECT '70779554' acd, '2594254' cd, '2594251' pcd, 1 depth, 1 seq union all SELECT '70779457' acd, '2594247' cd, '2594247' pcd, 0 depth, NULL seq union all SELECT '70779456' acd, '2594248' cd, '2594247' pcd, 1 depth, 3 seq union all SELECT '70779455' acd, '2594249' cd, '2594247' pcd, 1 depth, 2 seq union all SELECT '70779454' acd, '2594250' cd, '2594247' pcd, 1 depth, 1 seq union all SELECT '70769662' acd, '2593687' cd, '2593687' pcd, 0 depth, NULL seq union all SELECT '70769661' acd, '2593688' cd, '2593687' pcd, 1 depth, 3 seq union all SELECT '70769660' acd, '2593689' cd, '2593687' pcd, 1 depth, 2 seq union all SELECT '70769659' acd, '2593690' cd, '2593687' pcd, 1 depth, 1 seq union all SELECT '70769562' acd, '2593683' cd, '2593683' pcd, 0 depth, NULL seq union all SELECT '70769561' acd, '2593684' cd, '2593683' pcd, 1 depth, 3 seq union all SELECT '70769560' acd, '2593685' cd, '2593683' pcd, 1 depth, 2 seq union all SELECT '70769559' acd, '2593686' cd, '2593683' pcd, 1 depth, 1 seq union all SELECT '70766963' acd, '2593527' cd, '2593527' pcd, 0 depth, NULL seq union all SELECT '70766262' acd, '2593491' cd, '2593491' pcd, 0 depth, NULL seq union all SELECT '70766262' acd, '2593503' cd, '2593491' pcd, 1 depth, 2 seq union all SELECT '70766261' acd, '2593507' cd, '2593503' pcd, 2 depth, 1 seq union all SELECT '70766260' acd, '2593506' cd, '2593503' pcd, 2 depth, 2 seq union all SELECT '70766259' acd, '2593505' cd, '2593503' pcd, 2 depth, 3 seq union all SELECT '70766258' acd, '2593504' cd, '2593503' pcd, 2 depth, 4 seq union all SELECT '70766257' acd, '2593499' cd, '2593491' pcd, 1 depth, 1 seq union all SELECT '70766256' acd, '2593502' cd, '2593499' pcd, 2 depth, 3 seq union all SELECT '70766255' acd, '2593501' cd, '2593499' pcd, 2 depth, 1 seq union all SELECT '70766254' acd, '2593500' cd, '2593499' pcd, 2 depth, 2 seq union all SELECT '70766253' acd, '2593496' cd, '2593491' pcd, 1 depth, 4 seq union all SELECT '70766252' acd, '2593498' cd, '2593496' pcd, 2 depth, 1 seq union all SELECT '70766251' acd, '2593497' cd, '2593496' pcd, 2 depth, 2 seq union all SELECT '70766250' acd, '2593493' cd, '2593491' pcd, 1 depth, 3 seq union all SELECT '70766249' acd, '2593495' cd, '2593493' pcd, 2 depth, 1 seq union all SELECT '70766248' acd, '2593494' cd, '2593493' pcd, 2 depth, 2 seq union all SELECT '70766247' acd, '2593492' cd, '2593491' pcd, 1 depth, 5 seq union all SELECT '70764666' acd, '2593287' cd, '2593287' pcd, 0 depth, NULL seq union all SELECT '70764666' acd, '2593290' cd, '2593287' pcd, 1 depth, 1 seq union all SELECT '70764655' acd, '2593288' cd, '2593287' pcd, 1 depth, 2 seq union all SELECT '70764654' acd, '2593289' cd, '2593288' pcd, 2 depth, 1 seq union all SELECT '70764666' acd, '2593301' cd, '2593290' pcd, 2 depth, 1 seq union all SELECT '70764665' acd, '2593300' cd, '2593290' pcd, 2 depth, 2 seq union all SELECT '70764664' acd, '2593299' cd, '2593290' pcd, 2 depth, 3 seq union all SELECT '70764663' acd, '2593298' cd, '2593290' pcd, 2 depth, 4 seq union all SELECT '70764662' acd, '2593297' cd, '2593290' pcd, 2 depth, 5 seq union all SELECT '70764661' acd, '2593296' cd, '2593290' pcd, 2 depth, 6 seq union all SELECT '70764660' acd, '2593295' cd, '2593290' pcd, 2 depth, 7 seq union all SELECT '70764659' acd, '2593294' cd, '2593290' pcd, 2 depth, 8 seq union all SELECT '70764658' acd, '2593293' cd, '2593290' pcd, 2 depth, 9 seq union all SELECT '70764657' acd, '2593292' cd, '2593290' pcd, 2 depth, 10 seq union all SELECT '70764656' acd, '2593291' cd, '2593290' pcd, 2 depth, 11 seq union all SELECT '70762771' acd, '2593173' cd, '2593173' pcd, 0 depth, NULL seq
위같은 데이터가 있습니다.
정렬을 하고싶은데 방법이 도무지 안떠올라서 질문드립니다.
0 depth 에서는 순서는 acd desc 순서구요 그 아래 하위들은 seq 순으로 정렬하고 싶은데...
부탁드립니다!!
by 마농
[2020.02.25 11:22:58]
WITH t AS
(
SELECT '70784256' acd, '2594493' cd, '2594493' pcd, 0 depth, NULL seq
UNION ALL SELECT '70779557', '2594251', '2594251', 0, NULL
UNION ALL SELECT '70779556', '2594252', '2594251', 1, 3
UNION ALL SELECT '70779555', '2594253', '2594251', 1, 2
UNION ALL SELECT '70779554', '2594254', '2594251', 1, 1
UNION ALL SELECT '70779457', '2594247', '2594247', 0, NULL
UNION ALL SELECT '70779456', '2594248', '2594247', 1, 3
UNION ALL SELECT '70779455', '2594249', '2594247', 1, 2
UNION ALL SELECT '70779454', '2594250', '2594247', 1, 1
UNION ALL SELECT '70769662', '2593687', '2593687', 0, NULL
UNION ALL SELECT '70769661', '2593688', '2593687', 1, 3
UNION ALL SELECT '70769660', '2593689', '2593687', 1, 2
UNION ALL SELECT '70769659', '2593690', '2593687', 1, 1
UNION ALL SELECT '70769562', '2593683', '2593683', 0, NULL
UNION ALL SELECT '70769561', '2593684', '2593683', 1, 3
UNION ALL SELECT '70769560', '2593685', '2593683', 1, 2
UNION ALL SELECT '70769559', '2593686', '2593683', 1, 1
UNION ALL SELECT '70766963', '2593527', '2593527', 0, NULL
UNION ALL SELECT '70766262', '2593491', '2593491', 0, NULL
UNION ALL SELECT '70766262', '2593503', '2593491', 1, 2
UNION ALL SELECT '70766261', '2593507', '2593503', 2, 1
UNION ALL SELECT '70766260', '2593506', '2593503', 2, 2
UNION ALL SELECT '70766259', '2593505', '2593503', 2, 3
UNION ALL SELECT '70766258', '2593504', '2593503', 2, 4
UNION ALL SELECT '70766257', '2593499', '2593491', 1, 1
UNION ALL SELECT '70766256', '2593502', '2593499', 2, 3
UNION ALL SELECT '70766255', '2593501', '2593499', 2, 1
UNION ALL SELECT '70766254', '2593500', '2593499', 2, 2
UNION ALL SELECT '70766253', '2593496', '2593491', 1, 4
UNION ALL SELECT '70766252', '2593498', '2593496', 2, 1
UNION ALL SELECT '70766251', '2593497', '2593496', 2, 2
UNION ALL SELECT '70766250', '2593493', '2593491', 1, 3
UNION ALL SELECT '70766249', '2593495', '2593493', 2, 1
UNION ALL SELECT '70766248', '2593494', '2593493', 2, 2
UNION ALL SELECT '70766247', '2593492', '2593491', 1, 5
UNION ALL SELECT '70764666', '2593287', '2593287', 0, NULL
UNION ALL SELECT '70764666', '2593290', '2593287', 1, 1
UNION ALL SELECT '70764655', '2593288', '2593287', 1, 2
UNION ALL SELECT '70764654', '2593289', '2593288', 2, 1
UNION ALL SELECT '70764666', '2593301', '2593290', 2, 1
UNION ALL SELECT '70764665', '2593300', '2593290', 2, 2
UNION ALL SELECT '70764664', '2593299', '2593290', 2, 3
UNION ALL SELECT '70764663', '2593298', '2593290', 2, 4
UNION ALL SELECT '70764662', '2593297', '2593290', 2, 5
UNION ALL SELECT '70764661', '2593296', '2593290', 2, 6
UNION ALL SELECT '70764660', '2593295', '2593290', 2, 7
UNION ALL SELECT '70764659', '2593294', '2593290', 2, 8
UNION ALL SELECT '70764658', '2593293', '2593290', 2, 9
UNION ALL SELECT '70764657', '2593292', '2593290', 2, 10
UNION ALL SELECT '70764656', '2593291', '2593290', 2, 11
UNION ALL SELECT '70762771', '2593173', '2593173', 0, NULL
)
, t1 AS
(
SELECT acd, cd, pcd, depth, seq
, acd root_acd
, CAST('' AS VARCHAR(99)) x
FROM t
WHERE depth = 0
UNION ALL
SELECT c.acd, c.cd, c.pcd, c.depth, c.seq
, p.root_acd
, CAST(CONCAT(p.x, LEFT(CONCAT('00', c.seq), 3)) AS VARCHAR(99)) x
FROM t1 p
INNER JOIN t c
ON p.cd = c.pcd
WHERE c.depth != 0
)
SELECT *
FROM t1
ORDER BY root_acd DESC, x
OPTION (MAXRECURSION 0)
;
) 버튼을 클릭하여 작성 하시면 됩니다.