[sql] MySQL의 자연 정렬
MySQL 데이터베이스에서 성능적이고 자연스러운 정렬을 수행하는 우아한 방법이 있습니까?
예를 들어이 데이터 세트가있는 경우 :
- 파이널 판타지
- 파이널 판타지 4
- 파이널 판타지 10
- 파이널 판타지 12
- 파이널 판타지 12 : 프로 마시 아의 사슬
- 파이널 판타지 어드벤처
- 파이널 판타지 오리진
- 파이널 판타지 전술
게임 이름을 구성 요소로 분할하는 것보다 다른 모든 우아한 솔루션
- 제목 : “파이널 판타지”
- 번호 : “12”
- 부제 : “Promathia의 사슬”
올바른 순서로 나오는지 확인하려면? (2 이전이 아니라 4 이후 10).
그렇게하는 것은 a **에게 고통입니다. 왜냐하면 때때로 게임 제목을 파싱하는 메커니즘을 깨는 또 다른 게임이 있기 때문입니다 (예 : “Warhammer 40,000”, “James Bond 007”).
답변
이것이 출시일별로 많은 것이 분류되는 이유라고 생각합니다.
해결책은 “SortKey”에 대한 테이블에 다른 열을 만드는 것입니다. 이것은 쉬운 정렬이나 카운터를 위해 만든 패턴을 따르는 제목의 정리 된 버전 일 수 있습니다.
답변
다음은 빠른 해결책입니다.
SELECT alphanumeric,
integer
FROM sorting_test
ORDER BY LENGTH(alphanumeric), alphanumeric
답변
방금 발견 :
SELECT names FROM your_table ORDER BY games + 0 ASC
숫자가 앞에있을 때 자연스러운 정렬을 수행하고 중간에서도 작동 할 수 있습니다.
답변
@plalx가 게시 한 것과 동일한 기능이지만 MySQL로 다시 작성되었습니다.
DROP FUNCTION IF EXISTS `udf_FirstNumberPos`;
DELIMITER ;;
CREATE FUNCTION `udf_FirstNumberPos` (`instring` varchar(4000))
RETURNS int
LANGUAGE SQL
DETERMINISTIC
NO SQL
SQL SECURITY INVOKER
BEGIN
DECLARE position int;
DECLARE tmp_position int;
SET position = 5000;
SET tmp_position = LOCATE('0', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
SET tmp_position = LOCATE('1', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
SET tmp_position = LOCATE('2', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
SET tmp_position = LOCATE('3', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
SET tmp_position = LOCATE('4', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
SET tmp_position = LOCATE('5', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
SET tmp_position = LOCATE('6', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
SET tmp_position = LOCATE('7', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
SET tmp_position = LOCATE('8', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
SET tmp_position = LOCATE('9', instring); IF (tmp_position > 0 AND tmp_position < position) THEN SET position = tmp_position; END IF;
IF (position = 5000) THEN RETURN 0; END IF;
RETURN position;
END
;;
DROP FUNCTION IF EXISTS `udf_NaturalSortFormat`;
DELIMITER ;;
CREATE FUNCTION `udf_NaturalSortFormat` (`instring` varchar(4000), `numberLength` int, `sameOrderChars` char(50))
RETURNS varchar(4000)
LANGUAGE SQL
DETERMINISTIC
NO SQL
SQL SECURITY INVOKER
BEGIN
DECLARE sortString varchar(4000);
DECLARE numStartIndex int;
DECLARE numEndIndex int;
DECLARE padLength int;
DECLARE totalPadLength int;
DECLARE i int;
DECLARE sameOrderCharsLen int;
SET totalPadLength = 0;
SET instring = TRIM(instring);
SET sortString = instring;
SET numStartIndex = udf_FirstNumberPos(instring);
SET numEndIndex = 0;
SET i = 1;
SET sameOrderCharsLen = CHAR_LENGTH(sameOrderChars);
WHILE (i <= sameOrderCharsLen) DO
SET sortString = REPLACE(sortString, SUBSTRING(sameOrderChars, i, 1), ' ');
SET i = i + 1;
END WHILE;
WHILE (numStartIndex <> 0) DO
SET numStartIndex = numStartIndex + numEndIndex;
SET numEndIndex = numStartIndex;
WHILE (udf_FirstNumberPos(SUBSTRING(instring, numEndIndex, 1)) = 1) DO
SET numEndIndex = numEndIndex + 1;
END WHILE;
SET numEndIndex = numEndIndex - 1;
SET padLength = numberLength - (numEndIndex + 1 - numStartIndex);
IF padLength < 0 THEN
SET padLength = 0;
END IF;
SET sortString = INSERT(sortString, numStartIndex + totalPadLength, 0, REPEAT('0', padLength));
SET totalPadLength = totalPadLength + padLength;
SET numStartIndex = udf_FirstNumberPos(RIGHT(instring, CHAR_LENGTH(instring) - numEndIndex));
END WHILE;
RETURN sortString;
END
;;
용법:
SELECT name FROM products ORDER BY udf_NaturalSortFormat(name, 10, ".")
답변
얼마 전에 MSSQL 2000 용으로이 함수를 작성했습니다 .
/**
* Returns a string formatted for natural sorting. This function is very useful when having to sort alpha-numeric strings.
*
* @author Alexandre Potvin Latreille (plalx)
* @param {nvarchar(4000)} string The formatted string.
* @param {int} numberLength The length each number should have (including padding). This should be the length of the longest number. Defaults to 10.
* @param {char(50)} sameOrderChars A list of characters that should have the same order. Ex: '.-/'. Defaults to empty string.
*
* @return {nvarchar(4000)} A string for natural sorting.
* Example of use:
*
* SELECT Name FROM TableA ORDER BY Name
* TableA (unordered) TableA (ordered)
* ------------ ------------
* ID Name ID Name
* 1. A1. 1. A1-1.
* 2. A1-1. 2. A1.
* 3. R1 --> 3. R1
* 4. R11 4. R11
* 5. R2 5. R2
*
*
* As we can see, humans would expect A1., A1-1., R1, R2, R11 but that's not how SQL is sorting it.
* We can use this function to fix this.
*
* SELECT Name FROM TableA ORDER BY dbo.udf_NaturalSortFormat(Name, default, '.-')
* TableA (unordered) TableA (ordered)
* ------------ ------------
* ID Name ID Name
* 1. A1. 1. A1.
* 2. A1-1. 2. A1-1.
* 3. R1 --> 3. R1
* 4. R11 4. R2
* 5. R2 5. R11
*/
CREATE FUNCTION dbo.udf_NaturalSortFormat(
@string nvarchar(4000),
@numberLength int = 10,
@sameOrderChars char(50) = ''
)
RETURNS varchar(4000)
AS
BEGIN
DECLARE @sortString varchar(4000),
@numStartIndex int,
@numEndIndex int,
@padLength int,
@totalPadLength int,
@i int,
@sameOrderCharsLen int;
SELECT
@totalPadLength = 0,
@string = RTRIM(LTRIM(@string)),
@sortString = @string,
@numStartIndex = PATINDEX('%[0-9]%', @string),
@numEndIndex = 0,
@i = 1,
@sameOrderCharsLen = LEN(@sameOrderChars);
-- Replace all char that has to have the same order by a space.
WHILE (@i <= @sameOrderCharsLen)
BEGIN
SET @sortString = REPLACE(@sortString, SUBSTRING(@sameOrderChars, @i, 1), ' ');
SET @i = @i + 1;
END
-- Pad numbers with zeros.
WHILE (@numStartIndex <> 0)
BEGIN
SET @numStartIndex = @numStartIndex + @numEndIndex;
SET @numEndIndex = @numStartIndex;
WHILE(PATINDEX('[0-9]', SUBSTRING(@string, @numEndIndex, 1)) = 1)
BEGIN
SET @numEndIndex = @numEndIndex + 1;
END
SET @numEndIndex = @numEndIndex - 1;
SET @padLength = @numberLength - (@numEndIndex + 1 - @numStartIndex);
IF @padLength < 0
BEGIN
SET @padLength = 0;
END
SET @sortString = STUFF(
@sortString,
@numStartIndex + @totalPadLength,
0,
REPLICATE('0', @padLength)
);
SET @totalPadLength = @totalPadLength + @padLength;
SET @numStartIndex = PATINDEX('%[0-9]%', RIGHT(@string, LEN(@string) - @numEndIndex));
END
RETURN @sortString;
END
GO
답변
MySQL은 이러한 종류의 “자연 정렬”을 허용하지 않으므로 위에서 설명한대로 (별도의 ID 필드 등) 데이터 설정을 분할하거나 실패하는 것이 가장 좋은 방법 인 것 같습니다. 즉, 제목이 아닌 요소, db의 색인화 된 요소 (날짜, db에 삽입 된 ID 등)를 기반으로 정렬을 수행합니다.
db가 정렬을 수행하도록하는 것은 대용량 데이터 세트를 선택한 프로그래밍 언어로 읽어서 정렬하는 것보다 거의 항상 빠릅니다. 따라서 여기에서 db 스키마를 제어 할 수있는 경우 추가를 살펴보십시오. 위에서 설명한대로 필드를 쉽게 분류하면 장기적으로 많은 번거 로움과 유지 관리를 줄일 수 있습니다.
“자연스러운 정렬”을 추가하라는 요청은 때때로 MySQL 버그 및 토론 포럼 에서 발생하며 많은 솔루션이 데이터의 특정 부분을 제거 ORDER BY하고 쿼리 의 일부로 캐스팅하는 것입니다.
SELECT * FROM table ORDER BY CAST(mid(name, 6, LENGTH(c) -5) AS unsigned)
이러한 종류의 솔루션은 위의 Final Fantasy 예제에서 작동하도록 만들 수 있지만 특별히 유연하지 않으며 “Warhammer 40,000″및 “James Bond 007″과 같은 데이터 세트로 깔끔하게 확장 할 가능성이 낮습니다. .
답변
따라서 만족스러운 답을 찾았다는 것을 알고 있지만 잠시 동안이 문제로 어려움을 겪고 있었으며 이전에는 SQL에서 합리적으로 잘 수행 될 수 없다고 판단했고 JSON에서 자바 스크립트를 사용해야했습니다. 정렬.
다음은 SQL을 사용하여 해결 한 방법입니다. 다른 사람들에게 도움이되기를 바랍니다.
다음과 같은 데이터가 있습니다.
장면 1 장면 1A 장면 1B 장면 2A 장면 3 ... 장면 101 장면 XXA1 장면 XXA2
나는 그것이 또한 효과가있을 것이라고 생각하지만 실제로 물건을 “캐스트”하지 않았다.
먼저 데이터에서 변경되지 않은 부분 (이 경우 “Scene”)을 교체 한 다음 LPAD를 사용하여 정렬했습니다. 이것은 알파 문자열과 번호가 매겨진 문자열을 적절하게 정렬 할 수 있도록하는 것 같습니다.
내 ORDER BY조항은 다음과 같습니다.
ORDER BY LPAD(REPLACE(`table`.`column`,'Scene ',''),10,'0')
분명히 이것은 그렇게 획일적이지 않은 원래의 문제에는 도움이되지 않습니다. 그러나 나는 이것이 아마도 다른 많은 관련 문제에 대해 효과가있을 것이라고 생각합니다.
