MYSQL order by 한글 영문 숫자 특수문자 조합 정렬
본문
아래의 값처럼 데이터가 들어 있고. 아래처럼 데이터를 정렬해야 합니다. ㅠ
(a.work_uid 라는 필드에 아래와 같은 값들이 들어 있습니다.)
========================
1-1
1-20
1-112
.
.
a-1
a-30
a-105
.
.
a1-1
a1-22
a1-210
.
.
k1-2
k1-2(1)
k1-2(2)
k2-1
K2-3
k2-3(1)
k2-3(2).
.
k4-37
k4-39
k4-40
k4-352
k4-374
.
.
광주전남-8
박-8
========================
위 처럼
숫자 1순위 ,
영문 2순위(대소문자 상관없이 알파벳 순으로)
한글 3순위입니다.
현재 order by 부분의 내용은 아래와 같습니다.
ORDER BY
( CASE
WHEN ascii(SUBSTRING(a.work_uid, 1, 1)) < 58 THEN 1
WHEN ASCII(SUBSTRING(a.work_uid, 1, 1)) >= 65 AND ASCII(SUBSTRING(a.work_uid, 1, 1)) <= 119 THEN 2
WHEN ASCII(SUBSTRING(a.work_uid, 1, 1)) >= 176 AND ASCII(SUBSTRING(a.work_uid, 1, 1)) <= 200 THEN 3
ELSE 4 END ), a.work_uid
위의 쿼리로 하면 숫자 1순위, 영문2순위, 한글 3순위로 정렬되어지나 숫자는 아래처럼 정렬이 됩니다.
1-1
1-13
1-2
11-1
11-11
11-2
2-1
2-15
2-2
숫자도 아래처럼 정렬이 되도록 할 수 있는 방법이 있을까요?
1-1
1-2
1-13
2-1
2-2
2-15
11-1
11-2
11-11
답변 3
select *, if(ascii(SUBSTRING(work_uid, 1, 1)) < 58,100+substring_index(work_uid, '-', 1),substring_index(work_uid, '-', 1) ) as s1, 100+substring_index(work_uid, '-', -1) as s2 from Table order by s1, s2,work_uid
정말 감사합니다.
너무 많은 시간동안 고민하고 다른 분에게도 도움받았지만 해결이 되지 않았는 답변 주신 쿼리로 해결되었습니다.
영문+숫자 조합일 경우 제대로 정렬되지 않아 답변 쿼리를 응용하여 해결하였습니다.
아래의 쿼리가 정상적인건지는 모르겠으나 제가 원하는 결과물이 나왔습니다.
select
work_uid,
if( ascii(SUBSTRING(work_uid, 1, 1)) < 58, 'a', SUBSTR(substring_index(work_uid, '-', 1), 1, 1) ) as s1,
if( ascii(SUBSTRING(work_uid, 1, 1)) < 58, 100+substring_index(work_uid, '-', 1), REVERSE( CAST( REVERSE( concat( substring_index( work_uid, '-', 1 ), '1' )) AS UNSIGNED ) ) ) as s2,
10000+substring_index(work_uid, '-', -1) as s3
from
Table
order by
s1, s2*1, s3*1, work_uid
if( ascii(SUBSTRING(work_uid, 1, 1)) < 58, 'a', SUBSTR(substring_index(work_uid, '-', 1), 1, 1) ) as s1,
REVERSE( CAST( REVERSE( concat( substring_index( work_uid, '-', 1 ), '1' )) AS UNSIGNED ) ) ) as s2,
위 2개는 이렇게 할 필요가 없는데요?
질문에서 나온 데이터 종류는 정렬에 전혀 문제 될 것이 없는데요?
k1, k2 처럼 영문자 뒤에 한자리 숫자가 아닌 k11, k12 같은 것이 있어서 문제 가 된다면 아래처럼
영문자 뒤의 숫자를 +100해주면 됩니다
select *, if(ascii(SUBSTRING(work_uid, 1, 1)) < 58,100+substring_index(work_uid, '-', 1),
concat(substring(substring_index(work_uid, '-', 1),1,1) , 100+substring(substring_index(work_uid, '-', 1),2) ) ) as s1,
100+substring_index(work_uid, '-', -1) as s2 from tmperp order by s1, s2,work_uid
s2*1, s3*1 <--이렇게 할 이유도 없습니다
+100해서 11보다 2가 먼저 나오도록 되었고 숫자형문자 다음은 영어, 한글 순으로 정렬되는데 굳이 저렇게 할 이유는 없어보입니다
reverse 이것은 어떤 경우가 있어서 이렇게한 것이지 알 수 없네요
제가 복잡한 쿼리문은 만들지 못합니다. (아직 실력이 안됩니다. ㅠㅠ)
영문+숫자인경우.. 아래처럼 정렬이 되었습니다.
c1
c11
c12
c123
c6
아래처럼 정렬이 되도록 만들어야 했습니다.
c1
c6
c11
c12
c123
다른 방법이 딱히 떠오르지 않아 알려주신 코드를 응용하여 숫자와 영문자를 분리하였습니다.
영문조합이 없는건 "a"로 지정하고,
코드로 영문 또는 한글은 첫글자만 분리 하였습니다.
숫자이면 "a"지정하고 아니면 첫글자만 추출
if( ascii(SUBSTRING(work_uid, 1, 1)) < 58, 'a', SUBSTR(substring_index(work_uid, '-', 1), 1, 1) ) as s1,
영문+숫자 조합일 경우 숫자만 분리하는 방법을 제대로 알지 못하여
아래의 방법으로 해결하게 되었던 겁니다.
REVERSE( CAST( REVERSE( concat( substring_index( work_uid, '-', 1 ), '1' )) AS UNSIGNED ) ) ) as s2,
1. k20-13
2. k20 (substring_index)
3. k201 (concat 으로 1을 붙임)
4. 102K (reverse)
5. 102 (cast)
6. 201 (reverse) (뒤에 1을 붙인것을 없앨 필요는 없었습니다.)
마지막 숫자가 0인경우 reverse하면 0이 사라지는 현상이 있더라구요.
그래서 마지막 숫자 "1"을 추가하게 되었습니다.
그래서, 영문자와 숫자를 다시 나누는 부분을 추가 하였습니다.
위에서 분리한 숫자도 막상 정렬을 해보니 숫자로 인식이 안되어서 s2 * 1을 해주게 되었습니다.
이번 계기로 인하여 @균이 님 덕분에 정말 많은 것을 알게 되었습니다.
하지만, 더 좋은 방법을 알려 주신다면 감사하는 마음으로 배우도록 하겠습니다.
정답은 아닌듯하나 위 작성하신 쿼리문 맨 뒤에
a.work_uid * 1
이거 추가해서 해보세용
혼자 테스트로 db 만들어 넣고 쿼리문 날려서 나온값임.
등록 출력
답변 감사합니다.
저도 동일한 방법으로 시도를 해보왔지만 아래와 같이 영문이 포함될 경우 제대로 정렬이 안됩니다.
k4-593
k4-621
k4-187
k4-561
k4-332
k4-371
k4-237
k2-54
k4-363
k4-493
k4-359
k4-4
k4-594
k4-198
k4-368
k4-292
k4-236
c6-61
H-241
k5-114
k5-115
k5-118
k4-583
k4-584
k4-585
근데 저걸 굳이 쿼리문에서 order by로 해야 하는 이유가 있을가요?
그냥 쿼리문 날려서 데이터 모아서 그 데이터를 정렬 하는게 빠를듯한데요..
그냥 데이터 while()문으로 돌려 배열로 필드명 담았다 가정하고
배열필드명 담을때 저 위에 코드값을 구분자가 필수로 다들어가는듯하니?
explode("-") 로 잘라서 별도로 담은다음에 배열을 재정렬 해주면 될듯한데용
$ArraData = array("k4-593","k4-621","k4-187","k4-561","k4-332","k4-371","k4-237","k2-54 ","k4-363","k4-493","k4-359","k4-4 ","k4-594","k4-198","k4-368","k4-292","k4-236","c6-61 ","H-241 ","k5-114","k5-115","k5-118","k4-583","k4-584","k4-585","1-1 ","1-2 ","1-13 ","2-1 ","2-2 ","2-15 ","11-1 ","11-2 ","11-11","광주전남-8","박-8","나-2");
$tmpDat = [];
foreach($ArraData as $key => $value){
$codeVal = explode("-", $value);
$tmpDat[$key]['code1'] = $codeVal[0];
$tmpDat[$key]['code2'] = (int)$codeVal[1];
$tmpDat[$key]['idx'] = $key;
$tmpDat[$key]['subject'] = $key." 제목제목";
}
while문 통해 배열담은 데이터다 생각할라고 tmpDat 에 담음;;
코드만 불러와 정렬
$code1 = array_column($tmpDat, 'code1');
$code2 = array_column($tmpDat, 'code2');
array_multisort($code1, SORT_ASC, $code2, SORT_ASC, $tmpDat);
재정렬된 배열 새로운거에 담아서 결과처리
$reNewArr = [];
foreach($tmpDat as $key => $val){
$val['work_uid'] = $val['code1']."-".$val['code2'];
unset($val['code1']);
unset($val['code2']);
$reNewArr[$key] = $val;
}
print_r($reNewArr);
결과
Array
(
[0] => Array
(
[idx] => 25
[subject] => 25 제목제목
[work_uid] => 1-1
)
[1] => Array
(
[idx] => 26
[subject] => 26 제목제목
[work_uid] => 1-2
)
[2] => Array
(
[idx] => 27
[subject] => 27 제목제목
[work_uid] => 1-13
)
[3] => Array
(
[idx] => 28
[subject] => 28 제목제목
[work_uid] => 2-1
)
[4] => Array
(
[idx] => 29
[subject] => 29 제목제목
[work_uid] => 2-2
)
[5] => Array
(
[idx] => 30
[subject] => 30 제목제목
[work_uid] => 2-15
)
[6] => Array
(
[idx] => 31
[subject] => 31 제목제목
[work_uid] => 11-1
)
........
)
이런식으로 해도 되지 않을가 생각해봅니다 원하시는 답변이면 좋을듯한데요~ ^^;
소중한 시간내어 답변 주셔서 정말 감사드립니다.
예시를 제가 구현하고자 하는 결과물에 적용하려 했지만 적합하지 않은것 같습니다.
하지만, 좋은 예시를 보여주셔서 많은 도움이 되었습니다.
