데이터베이스 질문과 답변
by kshap [2015.01.30 08:41:04]
현재 MS-SQL을 사용하고 있습니다.
table1에는 파일명과 그파일의 파일버전이 입력되어 있습니다.
name version
a 5.1.235.0
a 5.1.235.1
b 5.3.254.1
b 5.3.254
c 5.2.1
d 4.235.1.1
이런식으로 되어있습니다.
지금 각 파일명마다 version을 최신순으로 정렬시켜야 하는데 데이터타입이 nchar여서 그대로 정렬시키면
문자로 정렬을 시켜버립니다. 그래서 '.'을 기준으로 각각의 숫자를 비교하려고 합니다.
그래서 아래와 같은 방식으로 정렬을 시키려고 했는데
문제는 각 version들이 '.'의 개수도 다르고 '.'이 언제 또 생길지 모른다는 점과 마지막엔 '.'없어서
마지막 숫자를 어디까지 자를 것인지 기준이 없어져 버립니다.
또 '.'이 길어질수록 쿼리를 생각해내기가 너무 복잡해져버립니다.
혹시 정렬 시킬 수 있는 다른 방법이나 있을까요...? 다른 방법이 없어서 아래 쿼리와 같이
쓸수밖에 없다면.. '.'개수가 다른건 어떻게 해결하고 마지막 숫자는 어떻게 잘라내서 비교를 해야 할까요...
select name
, version,
--첫문자부터 처음나오는 '.'전까지
, cast(SUBSTRINGversion, 1, CHARINDEX('.', version)-1) as int) as 첫번째
--처음 나오는 '.' 다음 숫자부터 두번째 나오는 '.' 전 숫자까지 자르기
, cast(substring(version, CHARINDEX('.', version)+1, (charindex('.', version, charindex('.', version)+1)) - (CHARINDEX('.', version)+1)) as int) as 두번째
from table1
order by 첫번째 desc, 두번째 desc
파일명 버전
a 5.3.9
a 5.3.8
a 5.3.29
a 5.3.28.1
이렇게 있다고하면 desc로 정렬시 결과는
5.3.29
5.3.28.1
5.3.9
5.3.8
버전 구분은 보통 major.minor.build.revision 정도로 . 개수에 한계가 있지 않은지요?
무한대를 가정해야 하나요? 아니면 최대 제한이 있을까요?
현재는 '.'이 가장 많은게 4개까지 붙은게 있습니다만..
더 생길수도 있다고 봐야할거 같습니다.
일단 무한대로 생각하고 접근해야 할거같습니다
WITH t AS
(
SELECT 'a' name, '5.1.235.0' version
UNION ALL SELECT 'a', '5.1.235.1'
UNION ALL SELECT 'b', '5.3.254.1'
UNION ALL SELECT 'b', '5.3.254'
UNION ALL SELECT 'c', '5.2.1'
UNION ALL SELECT 'd', '4.235.1.1'
UNION ALL SELECT 'e', '5.3.9'
UNION ALL SELECT 'e', '5.3.8'
UNION ALL SELECT 'e', '5.3.29'
UNION ALL SELECT 'e', '5.3.28.1'
UNION ALL SELECT 'f', '1.1.1.1.1.1.1.2'
UNION ALL SELECT 'f', '1.1.1.1.1.1.1.10'
)
, t1 AS
(
SELECT name, version
, 1 lv
, 1 s
, CHARINDEX('.', version+'.', 1) e
FROM t
UNION ALL
SELECT name, version
, lv + 1 lv
, e + 1 s
, CHARINDEX('.', version+'.', e + 1) e
FROM t1
WHERE CHARINDEX('.', version+'.', e + 1) > 0
)
SELECT name, version
FROM t a
ORDER BY name
, (SELECT '.' + RIGHT('00' + SUBSTRING(version, s, e - s), 3)
FROM t1
WHERE name = a.name
AND version = a.version
ORDER BY lv
FOR XML PATH('')
) DESC
;
WITH t AS()WITH t AS()
일단 이 쿼리의 성능은 보장 못하겠구요.
그러나 결과가 틀리게 나오는건 왜일까요?
- 조건을 걸어 데이터량을 줄여서 해보세요.
- 작성하신 쿼리를 올려줘 보세요.
- 뒤죽박죽 나오는 부분의 자료를 보여주세요.
희안하게... 데이터를 직접써서 union all 하면
결과가 맞게 나오는데 데이터가 들어있는 테이블을 기술해 놓으면
정렬이 흐트러집니다.... 하....
일단 조건으로 특정 파일 이름을 주어서 해보았습니다.
뒤죽박죽까지는 아닌데 먼가 파악할 수 없는 정렬로 나오게 되었습니다.
a 5.3.25
a 5.3.25
a 5.3.26
a 5.3.27
a 5.3.2
a 5.3.3
a 5.3.4
a 5.3.5
a 5.3.6
a 5.3.7
a 5.3.9
a 5.3.10
a 5.3.11
a 5.3.13
a 5.3.14
a 5.3.15
a 5.3.16
a 5.3.17
a 5.3.18
a 5.3.19
a 5.3.20
a 5.3.21
a 5.3.22
a 5.3.23
a 5.3.24
이와 같은 결과가 나오게 되었습니다.
맞는결과로는
a 5.3.27
a 5.3.26
a 5.3.25
a 5.3.25
a 5.3.24
이러한 결과가 나와야 하는데 계속 보고 있지만 잘파악이 안되네요...
자료 중복이 있네요.
자료 중복으로 인한 정렬 흐트러짐이 발생한 거네요.
중복되지 않는다고 생각하고 작성한 쿼리입니다.
그러나 중복되지 않은 자료들이 역순정렬이 아닌 순서대로 정렬된건은 이해가 안가는 결과네요.
주신 예시로 Union All 해서 쿼리 돌려봐도 위와 같은 결과가 나오지는 않습니다.
혹시 버전에 공백이 포함된 건 아닌지?
ORDER BY 절에 사용된 구문을 Select 절로 올려서 결과를 확인해 보세요.
음... 데이터 타입이 varchar 가 아니라 char 네요 ㅡ,.ㅡa
뒤에 공백이 있는 듯...
--작성한 쿼리입니다
WITH t AS
(
select filename name, fileversion as version from table1 where filename = 'abc.dll'
)
, t1 AS
(
SELECT name, version
, 1 lv
, 1 s
, CHARINDEX('.', version+'.', 1) e
FROM t
UNION ALL
SELECT name, version
, lv + 1 lv
, e + 1 s
, CHARINDEX('.', version+'.', e + 1) e
FROM t1
WHERE CHARINDEX('.', version+'.', e + 1) > 0
)
SELECT name, version
FROM t a
ORDER BY name
, (SELECT '.' + RIGHT('00' + SUBSTRING(version, s, e - s), 3)
FROM t1
WHERE name = a.name
AND version = a.version
ORDER BY lv
FOR XML PATH('')
) DESC
WITH t AS
(
SELECT 'a' name, '5.3.25 ' version
UNION ALL SELECT 'a', '5.3.25 '
UNION ALL SELECT 'a', '5.3.26 '
UNION ALL SELECT 'a', '5.3.27 '
UNION ALL SELECT 'a', '5.3.2 '
UNION ALL SELECT 'a', '5.3.3 '
UNION ALL SELECT 'a', '5.3.4 '
UNION ALL SELECT 'a', '5.3.5 '
UNION ALL SELECT 'a', '5.3.6 '
UNION ALL SELECT 'a', '5.3.7 '
UNION ALL SELECT 'a', '5.3.9 '
UNION ALL SELECT 'a', '5.3.10 '
UNION ALL SELECT 'a', '5.3.11 '
UNION ALL SELECT 'a', '5.3.13 '
UNION ALL SELECT 'a', '5.3.14 '
UNION ALL SELECT 'a', '5.3.15 '
UNION ALL SELECT 'a', '5.3.16 '
UNION ALL SELECT 'a', '5.3.17 '
UNION ALL SELECT 'a', '5.3.18 '
UNION ALL SELECT 'a', '5.3.19 '
UNION ALL SELECT 'a', '5.3.20 '
UNION ALL SELECT 'a', '5.3.21 '
UNION ALL SELECT 'a', '5.3.22 '
UNION ALL SELECT 'a', '5.3.23 '
UNION ALL SELECT 'a', '5.3.24 '
)
, t1 AS
(
SELECT name, version
, 1 lv
, 1 s
, CHARINDEX('.', RTRIM(version)+'.', 1) e -- 공백제거
FROM t
UNION ALL
SELECT name, version
, lv + 1 lv
, e + 1 s
, CHARINDEX('.', RTRIM(version)+'.', e + 1) e
FROM t1
WHERE CHARINDEX('.', RTRIM(version)+'.', e + 1) > 0
)
SELECT name, version
FROM t a
ORDER BY name
, (SELECT '.' + RIGHT('00' + SUBSTRING(version, s, e - s), 3)
FROM t1
WHERE name = a.name
AND version = a.version
GROUP BY lv, version, s, e -- 중복제거
ORDER BY lv
FOR XML PATH('')
) DESC
;
와...... 정말 많이 배웁니다.........
저는 언제 이렇게 쿼리가 머리속에 그려질지.....
저도 어서 초보에서 벗어나서 누구에게 도움이 되었으면 좋겠네요
고맙습니다!!!
WITH t AS
(
SELECT 'a' name, '5.3.25 ' version
UNION ALL SELECT 'a', '5.3.25 '
UNION ALL SELECT 'a', '5.3.26 '
UNION ALL SELECT 'a', '5.3.27 '
UNION ALL SELECT 'a', '5.3.2 '
UNION ALL SELECT 'a', '5.3.3 '
UNION ALL SELECT 'a', '5.3.4 '
UNION ALL SELECT 'a', '5.3.5 '
UNION ALL SELECT 'a', '5.3.6 '
UNION ALL SELECT 'a', '5.3.7 '
UNION ALL SELECT 'a', '5.3.9 '
UNION ALL SELECT 'a', '5.3.10 '
UNION ALL SELECT 'a', '5.3.11 '
UNION ALL SELECT 'a', '5.3.13 '
UNION ALL SELECT 'a', '5.3.14 '
UNION ALL SELECT 'a', '5.3.15 '
UNION ALL SELECT 'a', '5.3.16 '
UNION ALL SELECT 'a', '5.3.17 '
UNION ALL SELECT 'a', '5.3.18 '
UNION ALL SELECT 'a', '5.3.19 '
UNION ALL SELECT 'a', '5.3.20 '
UNION ALL SELECT 'a', '5.3.21 '
UNION ALL SELECT 'a', '5.3.22 '
UNION ALL SELECT 'a', '5.3.23 '
UNION ALL SELECT 'a', '5.3.24 '
)
SELECT name, version
FROM (SELECT name, version
, CHARINDEX('.', v, 1
) p1
, CHARINDEX('.', v, 1
+ CHARINDEX('.', v, 1
)) p2
, CHARINDEX('.', v, 1
+ CHARINDEX('.', v, 1
+ CHARINDEX('.', v, 1
))) p3
, CHARINDEX('.', v, 1
+ CHARINDEX('.', v, 1
+ CHARINDEX('.', v, 1
+ CHARINDEX('.', v, 1
)))) p4
, CHARINDEX('.', v, 1
+ CHARINDEX('.', v, 1
+ CHARINDEX('.', v, 1
+ CHARINDEX('.', v, 1
+ CHARINDEX('.', v, 1
))))) p5
, CHARINDEX('.', v, 1
+ CHARINDEX('.', v, 1
+ CHARINDEX('.', v, 1
+ CHARINDEX('.', v, 1
+ CHARINDEX('.', v, 1
+ CHARINDEX('.', v, 1
)))))) p6
FROM (SELECT name, version
, RTRIM(version)+'......' v
FROM t
) a
) a
ORDER BY name
, CAST(SUBSTRING(version, 1, p1 - 1) AS INT) DESC
, CAST(SUBSTRING(version, p1 + 1, p2 - p1 - 1) AS INT) DESC
, CAST(SUBSTRING(version, p2 + 1, p3 - p2 - 1) AS INT) DESC
, CAST(SUBSTRING(version, p3 + 1, p4 - p3 - 1) AS INT) DESC
, CAST(SUBSTRING(version, p4 + 1, p5 - p4 - 1) AS INT) DESC
, CAST(SUBSTRING(version, p5 + 1, p6 - p5 - 1) AS INT) DESC
;
이 쿼리는 행복제 및 서브쿼리 통합 과정등이 없이 노가다성 문자함수 반복만으로 처리했습니다.
아까보다는 성능이 향상될 것이구요.
p7, p8 추가하시기 쉽도록 규격화 했습니다.
최대 쩜의 갯수를 정해 놓고 위처럼 사용하시면 되겠습니다.
확실히 이쿼리가 더 빠르네요!
끝까지 신경써주셔서 다시한번 감사합니다^^
) 버튼을 클릭하여 작성 하시면 됩니다.