mysql order by 정렬에서 오름차순 하기 힘든 부분이 있어 질문드립니다! 고수님들 도와주세요 ㅠ

Question

SELECT A.code FROM TABLE A ORDER BY A.code;정렬을 할려고 하는데 형태가 A000-1-1 과 같은 형태입니다. 하이픈으로 나뉘어 있고 Order By 에 그대로 오름차순 정렬하면 숫자 위치에 따라 정렬을 하고 있어 하이픈 사이사이 숫자를 정렬하고 있지 않습니다...... A030-1A030-8A030-13A030-17A040-1A040-2A040-2-6A040-2-10A040-2-11A040-3과 같은 형태로 정렬할 수 있는 방법이 있을까요?

KarlStyner · Answer

방법이야 많겠지만 이미 저렇게 '문자열'로 확정된 상태의 데이터를 가지고 효율적으로 정렬할 수는 없습니다.애초에 A040-2-11 같은 형태가 아니라 A040-002-0011 같이 자리수 버퍼를 충분히 두고 구조를 잡았으면 좋았을 듯 하네요.

Clickin · Answer

이건 처음부터 설계가 컬럼 세개 써서 들어가야하는 부분인데 좀 곤란하네요전부 세가지 종류를 가지는 경우도 아니고요...이런 식으로 split해서 사용하는 편이 좋다고 봅니다.SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(a.code, '-', 1), '-', -1) as code , cast(SUBSTRING_INDEX(SUBSTRING_INDEX(a.code, '-', 2), '-', -1) as SIGNED) as sub1 , CASE WHEN CHAR_LENGTH(a.code) - CHAR_LENGTH( REPLACE (a.code, '-', '') ) > 1 THEN cast(SUBSTRING_INDEX(SUBSTRING_INDEX(a.code, '-', 3), '-', -1) as SIGNED) ELSE 0 end as sub2 FROM ( select 'A030-1' as code union all select 'A030-8' as code union all select 'A030-13' as code union all select 'A030-17' as code union all select 'A040-1' as code union all select 'A040-2' as code union all select 'A040-2-6' as code union all select 'A040-2-10' as code union all select 'A040-2-11' as code union all select 'A040-3' as code ) a order by code, sub1 asc, sub2 ascfiddlehttps://dbfiddle.uk/tx8B7Gz3

오후 · Answer

CREATE TEMPORARY TABLE test_temp (order_code VARCHAR(50)); INSERT INTO test_temp VALUES ('A030-1') ,('A030-8') ,('A030-13') ,('A030-17') ,('A040-1') ,('A040-2') ,('A040-2-6') ,('A040-2-10') ,('A040-2-11') ,('A040-3') ,('A040'); SELECT order_code , (LENGTH(order_code) - LENGTH(REPLACE(order_code, '-', ''))) as dash_cnt , SUBSTRING_INDEX(SUBSTRING_INDEX(order_code, '-', 1), '-', -1) AS first_char , IF( (LENGTH(order_code) - LENGTH(REPLACE(order_code, '-', ''))) &#61; 1 , CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(order_code, '-', 2), '-', -1) AS SIGNED) , 0 ) AS second_char , IF( (LENGTH(order_code) - LENGTH(REPLACE(order_code, '-', ''))) &#61; 2 , CAST(SUBSTRING_INDEX(SUBSTRING_INDEX(order_code, '-', 3), '-', -1) AS SIGNED ) , 0 ) AS third_char FROM test_temp ORDER BY first_char , second_char, third_char;

Olla2 · Answer

ORDER BY 안에서 자르셔서 두 개의 정렬을 정의하시면 될 것 같습니다.-- ORACLE SELECT A.CODE FROM TABLE A ORDER BY SUBSTR(CODE, 1, INSTR(CODE, '-') - 1) ASC -- '-' 기준으로 앞글자 , TO_NUMBER( SUBSTR(CODE, INSTR(CODE, '-') &#43; 1) ) ASC -- '-' 기준으로 뒷글자, 무조건 NUMBER라고 가정했을 때 숫자로 변환 후 정렬다만 ORDER BY 문에서 이런 식으로 컬럼을 자르면서 정렬 했을 때 COST가 많이 들 수 있으니 실행계획을 확인하면서 참고바랍니다.

장독깨기 · Answer

지금이라도 컬럼을 나눌 수 있으면 나누는게 제일 좋구요,정규식으로 정렬 할 수도 있습니다.숫자 부분이 4자리가 넘지 않는다 가정한겁니다.4자리 넘어가는게 있으면, 그니까, 최대가 5자리라 하면,아래에서 4를 5로, 000 을 0000 으로 바꾸면 됩니다.아, 그리고, 컬럼을 나누는 방법은, 기존 컬럼을 없애면 다른 영향이 있을 수 있으니,정렬용 컬럼을 3개 추가하시고, 트리그 잡아서 데이터 3개로 나눠 업데이트 하는 것이 좋겠습니다.with w_data as ( select 'A030-1' as code union all select 'A030-8' as code union all select 'A030-13' as code union all select 'A030-17' as code union all select 'A040-1' as code union all select 'A040-2' as code union all select 'A040-2-6' as code union all select 'A040-2-10' as code union all select 'A040-2-11' as code union all select 'A040-3' as code ) select *, REGEXP_REPLACE(REGEXP_REPLACE(code,'([0-9]&#43;)','000\1'),'(0*)([0-9]{4})', '\2') from w_data order by REGEXP_REPLACE(REGEXP_REPLACE(code,'([0-9]&#43;)','000\1'),'(0*)([0-9]{4})', '\2')

yeori · Answer

데이터 구조가 목적에 맞지 않는 전형적인 사례입니다.필연적으로 쿼리가 복잡해지고 나중에 재수없으면 성능 문제가 발목을 잡으면서 걷잡을 수 없이 다른 문제들을 파생시킵니다.쿼리를 발라서 처리할 수도 있지만, 이거는 다리 부러진데다 빨간약 바르는 꼴이고...목적에 맞게 데이터 구조를 변경하는게 더 남는게 많습니다.위와같은 디비를 수도 없이 많이 봤는데, 희한하게도 끝까지 디비 구조를 안바꾸려고 저항합니다.이미 코드를 짜놔서, 일정이 빠듯해서, 일을 더 만들고 싶지 않아서 등등...나만 죽을거냐, 다 같이 죽을거냐, 대개는 후자같음...

mysql order by 정렬에서 오름차순 하기 힘든 부분이 있어 질문드립니다! 고수님들 도와주세요 ㅠ

6개의 답변

Q&A 목록