Pytanie sortowanie mysql numerów wersji


Mam wartości takie jak:

1.1.2 
9.1 
2.2
4
1.2.3.4
3.2.14
3.2.1.4.2
.....

Muszę sortować te wartości za pomocą mysql. Typ danych dla tego jest varbinary (300).

Pożądane wyniki będą takie jak:

1.1.2
1.2.3.4
2.2
3.2.1.4.2
3.2.14
4
9.1

Zapytanie to:

select version_number from table order by version_number asc 

nie podaje prawidłowej kolejności sortowania.

Pożądanym wynikiem tego jest:

1.1.2
1.2.3.4
2.2
3.2.1.4.2
3.2.14 
4
9.1

Numery wersji są maksymalnie 20 cyfr (jak 1.2.3.4.5.6.7.8.9.2.34) i więcej. Nie ma określonego maksymalnego rozmiaru, a standardowa wersja jest podobna do wspomnianej powyżej.


10
2017-09-22 00:44


pochodzenie


Jakiego rodzaju przechowujesz te elementy? - Oliver Charlesworth
W porządku rosnącym, którego oczekujesz jako pierwszy 1.2.13 lub 1.2.2? - mwan
Czy możesz opublikować wyniki i wskazać problem (rzeczywisty czy pożądany)? - Michael M.
Przeważnie przechowujesz ten numer wersji w Varchar2, więc nie możesz wykonywać sortowania w ten sposób. - Thinhbk
Czy istnieje maksymalna znana wielkość każdego komponentu? Czy możesz zapisać standardową wersję (np 011.023.005 zamiast 11.23.5) w osobnej kolumnie tylko dla celów sortowania? - mu is too short


Odpowiedzi:


Spróbuj nadużywać INET_ATON funkcja do sortowania:

SELECT version_number FROM table ORDER BY INET_ATON(SUBSTRING_INDEX(CONCAT(version_number,'.0.0.0'),'.',4))

Ta sztuczka została pierwotnie opublikowana na lista dyskusyjna mysql, wielkie dzięki dla oryginalnego plakatu, Michael Stassen!

Oto, co miał do powiedzenia:

Jeśli każda część jest nie większa niż 255, możesz użyć INET_ATON () do zrobienia   czego chcesz (do czwartej części). Sztuką jest robienie każdego z nich   najpierw wyglądaj jak IP, używając CONCAT, aby dodać "0.0.0", aby się upewnić, że każdy   wiersz ma co najmniej 4 części, a następnie SUBSTRING_INDEX, aby wyciągnąć tylko   pierwsze 4 części.

Teraz muszę zaznaczyć, że ponieważ sortujemy funkcję   kolumna, a nie na samej kolumnie, nie możemy użyć indeksu na   kolumna do pomocy przy sortowaniu. Innymi słowy, sortowanie będzie   stosunkowo powolny.

W tym drugim przypadku zaleca rozwiązanie podobne do tego zamieszczonego przez @spanky (osobne kolumny).


29
2017-12-06 23:21





Zapisałbym go w trzech osobnych kolumnach, po jednej na każdą część numeru wersji.

Ustaw każdą kolumnę jako TINYINT, a nawet utwórz indeks w 3 kolumnach. To powinno uprościć sytuację.

Następnie możesz zrobić: select CONCAT(v1,'.',v2,'.',v3) AS version_number FROM table ORDER BY v1 asc, v2 asc, v3 asc


5
2017-09-22 01:38



To będzie bardzo przerażające z 20 komponentami w numerze wersji. - mu is too short
w takim przypadku można replikować swoje wersje jako zagnieżdżony model zestawu, dzięki czemu można go rozwijać we wszystkich kierunkach bez konieczności dodawania kolumn do tabeli. ale wdrożenie tego jest prawdopodobnie dość kłopotliwe. - iHaveacomputer
Możesz rozważyć select concat('1', '.', '2', '.', null); a następnie pomyśl jeszcze raz o 20 kolumnach. - mu is too short
Tak, kiedy to opublikowałem, pytanie nie zawierało faktu, że jest 20 cyfr i wyglądało na 3 lub 4 cyfry. Wygląda na to, że nikt nie opublikował odpowiedzi. Ja na przykład chciałbym wiedzieć, dlaczego ktoś użyłby 20 sztuk numeru wersji. Nie mogę sobie nawet wyobrazić scenariusza. Ale aby pomóc, zalecam opublikowanie standardowej wersji o stałej szerokości, takiej jak @muistooshort. - spanky


Jeśli chcesz wspierać wersje takie jak 1.1-beta lub używając starych wersji MySql bez INTE_ATON, możesz uzyskać ten sam rodzaj, dzieląc wersję i sortując każdą część jako liczbę całkowitą i ciąg znaków:

SELECT
    version,
    REPLACE(SUBSTRING(SUBSTRING_INDEX(version, '.', 1), LENGTH(SUBSTRING_INDEX(version, '.', 1 - 1)) + 1), '.', '') v1,
    REPLACE(SUBSTRING(SUBSTRING_INDEX(version, '.', 2), LENGTH(SUBSTRING_INDEX(version, '.', 2 - 1)) + 1), '.', '') v2,
    REPLACE(SUBSTRING(SUBSTRING_INDEX(version, '.', 3), LENGTH(SUBSTRING_INDEX(version, '.', 3 - 1)) + 1), '.', '') v3,
    REPLACE(SUBSTRING(SUBSTRING_INDEX(version, '.', 4), LENGTH(SUBSTRING_INDEX(version, '.', 4 - 1)) + 1), '.', '') v4
FROM 
    versions_table
ORDER BY
    0+v1, v1 DESC, 0+v2, v2 DESC, 0+v3, v3 DESC, 0+v4, v4 DESC;

0
2018-01-16 22:40