MySQL 'Order By' - sortowanie alfanumeryczne poprawnie
Chcę posortować następujące pozycje danych w kolejności przedstawionej poniżej (numery 1-12):
1 2 3 4 5 6 7 8 9 10 11 12
Jednak moje zapytanie-używając order by xxxxx asc
sortuje po pierwszej cyfrze przede wszystkim:
1 10 11 12 2 3 4 5 6 7 8 9Jakieś sztuczki, żeby to uporządkować?
Ponadto, w interesie pełnego ujawnienia, może to być mieszanka liter i cyfr( choć w tej chwili tak nie jest), np.:
A1 534G G46A 100B 100A 100JE
Itd....
Dzięki!Aktualizacja: osoby pytające o zapytanie
select * from table order by name asc
15 answers
Ludzie używają do tego różnych sztuczek. I Googled i dowiedzieć się kilka wyników każdy śledzić różne sztuczki. Spójrz na nie:
- sortowanie alfanumeryczne w MySQL
- sortowanie naturalne w MySQL
- sortowanie wartości liczbowych zmieszanych z wartościami alfanumerycznymi
- mySQL natural sort
- sortowanie naturalne w MySQL
Edit:
Właśnie dodałem kod każdego linku dla przyszli goście.
Sortowanie alfanumeryczne w MySQL
Given input
1A 1a 10A 9B 21C 1C 1D
Oczekiwana wydajność
1A 1C 1D 1a 9B 10A 21C
Zapytanie
Bin Way
===================================
SELECT
tbl_column,
BIN(tbl_column) AS binray_not_needed_column
FROM db_table
ORDER BY binray_not_needed_column ASC , tbl_column ASC
-----------------------
Cast Way
===================================
SELECT
tbl_column,
CAST(tbl_column as SIGNED) AS casted_column
FROM db_table
ORDER BY casted_column ASC , tbl_column ASC
Given input
Table: sorting_test -------------------------- ------------- | alphanumeric VARCHAR(75) | integer INT | -------------------------- ------------- | test1 | 1 | | test12 | 2 | | test13 | 3 | | test2 | 4 | | test3 | 5 | -------------------------- -------------
Oczekiwany Wynik
-------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
-------------------------- -------------
| test1 | 1 |
| test2 | 4 |
| test3 | 5 |
| test12 | 2 |
| test13 | 3 |
-------------------------- -------------
Zapytanie
SELECT alphanumeric, integer
FROM sorting_test
ORDER BY LENGTH(alphanumeric), alphanumeric
Sortowanie wartości liczbowych zmieszanych z wartościami alfanumerycznymi
Podano input
2a, 12, 5b, 5a, 10, 11, 1, 4b
Oczekiwany Wynik
1, 2a, 4b, 5a, 5b, 10, 11, 12
Zapytanie
SELECT version
FROM version_sorting
ORDER BY CAST(version AS UNSIGNED), version;
Hope this helps
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 10:31:26
Wiem, że ten post jest zamknięty, ale myślę, że mój sposób może pomóc niektórym ludziom. No i jest:
Mój zbiór danych jest bardzo podobny, ale jest nieco bardziej złożony. Ma numery, dane alfanumeryczne:
1
2
Chair
3
0
4
5
-
Table
10
13
19
Windows
99
102
Dog
Chciałbym mieć najpierw symbol" -", potem cyfry, potem tekst.
Więc idę tak:
SELECT name, (name = '-') boolDash, (name = '0') boolZero, (name+0 > 0) boolNum
FROM table
ORDER BY boolDash DESC, boolZero DESC, boolNum DESC, (name+0), name
Wynik powinien być czymś:
-
0
1
2
3
4
5
10
13
99
102
Chair
Dog
Table
Windows
Cały pomysł polega na prostym sprawdzaniu w SELECT i sortowaniu z wynikiem.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2013-10-16 20:47:05
Po prostu zrób to:
SELECT * FROM table ORDER BY column `name`+0 ASC
Dodanie + 0 oznacza, że:
0, 10, 11, 2, 3, 4
Staje się:
0, 2, 3, 4, 10, 11
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-03-03 18:58:35
Nienawidzę tego, ale to zadziała
order by lpad(name, 10, 0) <-- assuming maximum string length is 10
<-- you can adjust to a bigger length if you want to
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-12-19 05:00:13
Miałem dobre wyniki z
SELECT alphanumeric, integer FROM sorting_test ORDER BY CAST(alphanumeric AS UNSIGNED), alphanumeric ASC
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-08-11 06:17:52
To działa dla typu danych: Data1, Data2, Data3 ......, Data21 Oznacza ciąg "Data" jest wspólny we wszystkich wierszach.
Dla porządku według ASC będzie on sortowany idealnie, dla porządku według DESC nie nadaje się.
SELECT * FROM table_name ORDER BY LENGTH(column_name), column_name ASC;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-06-25 09:15:57
Tego typu pytanie zostało zadane wcześniej.
Rodzaj sortowania, o którym mówisz, nazywa się "sortowanie Naturalne". Dane, na których chcesz sortować, są alfanumeryczne. Lepiej byłoby utworzyć nową kolumnę do sortowania.
Aby uzyskać dalszą pomoc sprawdź natural-sort-in-mysql
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 12:02:38
Jeśli chcesz posortować kolumnę alfanumeryczną, która nie ma żadnego standardowego formatu
SELECT * FROM table ORDER BY (name = '0') DESC, (name+0 > 0) DESC, name+0 ASC, name ASC
Można dostosować To rozwiązanie do obsługi znaków niealfanumerycznych w razie potrzeby za pomocą dodatkowej logiki.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-02-18 15:40:57
To powinno sortować alfanumeryczne pole jak:
1 / tylko liczba, order by 1,2,3,4,5,6,7,8,9,10,11
itd...
2 / następnie pole z tekstem w stylu: 1foo, 2bar, aaa11aa, aaa22aa, b5452
itd...
SELECT MyField
FROM MyTable
order by
IF( MyField REGEXP '^-?[0-9]+$' = 0,
9999999999 ,
CAST(MyField AS DECIMAL)
), MyField
Zapytanie sprawdza, czy dane są liczbą, jeśli nie umieścić go na 99999999999, następnie zamów najpierw na tej kolumnie, następnie zamów na danych z tekstem
Powodzenia!Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-07-05 02:09:46
Zamiast próbować napisać jakąś funkcję i spowolnić zapytanie SELECT
, pomyślałem o innym sposobie zrobienia tego...
Utwórz Dodatkowe pole w bazie danych, które przechowuje wynik z następującej klasy, a po wstawieniu nowego wiersza Uruchom wartość pola, która zostanie naturalnie posortowana przez tę klasę i zapisz jej wynik w dodatkowym polu. Następnie zamiast sortować według oryginalnego pola, posortuj według dodatkowego pole.
String nsFieldVal = new NaturalSortString(getFieldValue(), 4).toString()
The above means:
- Create a NaturalSortString for the String returned from getFieldValue()
- Allow up to 4 bytes to store each character or number (4 bytes = ffff = 65535)
| field(32) | nsfield(161) |
a1 300610001
String sortString = new NaturalSortString(getString(), 4).toString()
import StringUtils;
/**
* Creates a string that allows natural sorting in a SQL database
* eg, 0 1 1a 2 3 3a 10 100 a a1 a1a1 b
*/
public class NaturalSortString {
private String inStr;
private int byteSize;
private StringBuilder out = new StringBuilder();
/**
* A byte stores the hex value (0 to f) of a letter or number.
* Since a letter is two bytes, the minimum byteSize is 2.
*
* 2 bytes = 00 - ff (max number is 255)
* 3 bytes = 000 - fff (max number is 4095)
* 4 bytes = 0000 - ffff (max number is 65535)
*
* For example:
* dog123 = 64,6F,67,7B and thus byteSize >= 2.
* dog280 = 64,6F,67,118 and thus byteSize >= 3.
*
* For example:
* The String, "There are 1000000 spots on a dalmatian" would require a byteSize that can
* store the number '1000000' which in hex is 'f4240' and thus the byteSize must be at least 5
*
* The dbColumn size to store the NaturalSortString is calculated as:
* > originalStringColumnSize x byteSize + 1
* The extra '1' is a marker for String type - Letter, Number, Symbol
* Thus, if the originalStringColumn is varchar(32) and the byteSize is 5:
* > NaturalSortStringColumnSize = 32 x 5 + 1 = varchar(161)
*
* The byteSize must be the same for all NaturalSortStrings created in the same table.
* If you need to change the byteSize (for instance, to accommodate larger numbers), you will
* need to recalculate the NaturalSortString for each existing row using the new byteSize.
*
* @param str String to create a natural sort string from
* @param byteSize Per character storage byte size (minimum 2)
* @throws Exception See the error description thrown
*/
public NaturalSortString(String str, int byteSize) throws Exception {
if (str == null || str.isEmpty()) return;
this.inStr = str;
this.byteSize = Math.max(2, byteSize); // minimum of 2 bytes to hold a character
setStringType();
iterateString();
}
private void setStringType() {
char firstchar = inStr.toLowerCase().subSequence(0, 1).charAt(0);
if (Character.isLetter(firstchar)) // letters third
out.append(3);
else if (Character.isDigit(firstchar)) // numbers second
out.append(2);
else // non-alphanumeric first
out.append(1);
}
private void iterateString() throws Exception {
StringBuilder n = new StringBuilder();
for (char c : inStr.toLowerCase().toCharArray()) { // lowercase for CASE INSENSITIVE sorting
if (Character.isDigit(c)) {
// group numbers
n.append(c);
continue;
}
if (n.length() > 0) {
addInteger(n.toString());
n = new StringBuilder();
}
addCharacter(c);
}
if (n.length() > 0) {
addInteger(n.toString());
}
}
private void addInteger(String s) throws Exception {
int i = Integer.parseInt(s);
if (i >= (Math.pow(16, byteSize)))
throw new Exception("naturalsort_bytesize_exceeded");
out.append(StringUtils.padLeft(Integer.toHexString(i), byteSize));
}
private void addCharacter(char c) {
//TODO: Add rest of accented characters
if (c >= 224 && c <= 229) // set accented a to a
c = 'a';
else if (c >= 232 && c <= 235) // set accented e to e
c = 'e';
else if (c >= 236 && c <= 239) // set accented i to i
c = 'i';
else if (c >= 242 && c <= 246) // set accented o to o
c = 'o';
else if (c >= 249 && c <= 252) // set accented u to u
c = 'u';
else if (c >= 253 && c <= 255) // set accented y to y
c = 'y';
out.append(StringUtils.padLeft(Integer.toHexString(c), byteSize));
}
@Override
public String toString() {
return out.toString();
}
}
Dla kompletności, poniżej znajduje się metoda StringUtils.padLeft
:
public static String padLeft(String s, int n) {
if (n - s.length() == 0) return s;
return String.format("%0" + (n - s.length()) + "d%s", 0, s);
}
Wynik powinien wyjść jak poniżej
-1
-a
0
1
1.0
1.01
1.1.1
1a
1b
9
10
10a
10ab
11
12
12abcd
100
a
a1a1
a1a2
a-1
a-2
áviacion
b
c1
c2
c12
c100
d
d1.1.1
e
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-11-28 18:12:05
SELECT length(actual_project_name),actual_project_name,
SUBSTRING_INDEX(actual_project_name,'-',1) as aaaaaa,
SUBSTRING_INDEX(actual_project_name, '-', -1) as actual_project_number,
concat(SUBSTRING_INDEX(actual_project_name,'-',1),SUBSTRING_INDEX(actual_project_name, '-', -1)) as a
FROM ctts.test22
order by
SUBSTRING_INDEX(actual_project_name,'-',1) asc,cast(SUBSTRING_INDEX(actual_project_name, '-', -1) as unsigned) asc
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-01-07 09:47:01
kolejność MySQL poprzez sortowanie alfanumeryczne w prawidłowej kolejności
Przykład:
SELECT `alphanumericCol` FROM `tableName` ORDER BY
SUBSTR(`alphanumericCol` FROM 1 FOR 1),
LPAD(lower(`alphanumericCol`), 10,0) ASC
Wyjście:
1
2
11
21
100
101
102
104
S-104A
S-105
S-107
S-111
To prosty przykład.
SELECT HEX(some_col) h
FROM some_table
ORDER BY h
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2020-11-03 03:14:29
Wybierz s.id, s.name, długość(s.name) len, ASCII(s.name) ASCCCI FROM table_name s ORDER BY ASCCI, len, NAME ASC;
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-08-07 07:13:24
Try this for ORDER BY DESC
SELECT * FROM testdata ORDER BY LENGHT(name) DESC, name DESC
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2019-07-10 11:33:11