MySQL "ordre Par" - tri correct des caractères alphanumériques
je veux trier les éléments de données suivants dans l'ordre où ils sont présentés ci-dessous (numéros 1-12):
1 2 3 4 5 6 7 8 9 10 11 12
cependant, ma requête-en utilisant order by xxxxx asc
trie par le premier chiffre avant tout autre:
1 10 11 12 2 3 4 5 6 7 8 9
y a-t-il des astuces pour le faire Trier plus correctement?
de plus, dans l'intérêt d'une divulgation complète, il pourrait s'agir d'un mélange de lettres et de chiffres( bien que ce ne soit pas le cas actuellement), C'est-à-dire:
A1 534G G46A 100B 100A 100JE
etc....
Merci!
mise à jour: les personnes qui demandent de la requête
select * from table order by name asc
12 réponses
les gens utilisent différents trucs pour faire ça. J'ai Googlé et trouvé des résultats de chaque visite de suivi des trucs différents. Regardez-les:
- Tri alphanumérique dans MySQL
- tri naturel en MySQL
- tri de valeurs numériques mélangées avec des valeurs alphanumériques
- mySQL naturel de tri
- Naturel Tri dans MySQL
Edit:
je viens d'ajouter le code de chaque lien pour les futurs visiteurs.
entrée donnée
1A 1a 10A 9B 21C 1C 1D
résultats escomptés
1A 1C 1D 1a 9B 10A 21C
Requête
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
entrée donnée
Table: sorting_test -------------------------- ------------- | alphanumeric VARCHAR(75) | integer INT | -------------------------- ------------- | test1 | 1 | | test12 | 2 | | test13 | 3 | | test2 | 4 | | test3 | 5 | -------------------------- -------------
Production Prévue
-------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
-------------------------- -------------
| test1 | 1 |
| test2 | 4 |
| test3 | 5 |
| test12 | 2 |
| test13 | 3 |
-------------------------- -------------
Requête
SELECT alphanumeric, integer
FROM sorting_test
ORDER BY LENGTH(alphanumeric), alphanumeric
tri de valeurs numériques mélangées avec valeurs alphanumériques
entrée donnée
2a, 12, 5b, 5a, 10, 11, 1, 4b
Production Prévue
1, 2a, 4b, 5a, 5b, 10, 11, 12
Requête
SELECT version
FROM version_sorting
ORDER BY CAST(version AS UNSIGNED), version;
Espérons que cette aide
je sais que ce poste est fermé, mais je pense que ma façon pourrait aider certaines personnes. Alors voilà:
mon ensemble de données est très similaire mais est un peu plus complexe. Il a des nombres, des données alphanumériques:
1
2
Chair
3
0
4
5
-
Table
10
13
19
Windows
99
102
Dog
je voudrais avoir le symbole' -', d'abord, puis les nombres, puis le texte.
donc je vais comme ceci:
SELECT name, (name = '-') boolDash, (name = '0') boolZero, (name+0 > 0) boolNum
FROM table
ORDER BY boolDash DESC, boolZero DESC, boolNum DESC, (name+0), name
le résultat devrait être quelque chose:
-
0
1
2
3
4
5
10
13
99
102
Chair
Dog
Table
Windows
toute l'idée est de faire un contrôle simple dans le SELECT et le tri avec le résultat.
faites Simplement ceci:
SELECT * FROM table ORDER BY column `name`+0 ASC
ajouter le +0 signifiera que:
0, Dix, Onze, Deux, Trois, 4
devient:
0, Deux, Trois, Quatre, Dix, 11
j'ai eu de bons résultats avec
SELECT alphanumeric, integer FROM sorting_test ORDER BY CAST(alphanumeric AS UNSIGNED), alphanumeric ASC
ce type de question a déjà été posée.
le type de tri dont vous parlez est appelé"tri naturel". Les données sur lesquelles vous voulez faire le tri sont alphanumériques. Il serait préférable de créer une nouvelle colonne pour le tri.
pour obtenir de l'aide, veuillez vérifier naturel-sort-en-mysql
cela devrait trier le champ alphanumérique comme:
1 / Nombre seulement, order by 1,2,3,4,5,6,7,8,9,10,11
, etc...
2 / puis le champ avec le texte comme: 1foo, 2bar, aaa11aa, aaa22aa, b5452
etc...
SELECT MyField
FROM MyTable
order by
IF( MyField REGEXP '^-?[0-9]+$' = 0,
9999999999 ,
CAST(MyField AS DECIMAL)
), MyField
le contrôle de requête si les données sont un nombre, si pas mettre à 9999999999 , puis commander d'abord sur cette colonne, puis commander sur les données avec le texte
bonne chance!
sélectionner S. id., art. nom, longueur(art. nom) len, ASCII(art. nom) ASCCCI De table_name s Ordre de ASCCCI, len, nom ASC;
au lieu d'essayer d'écrire une fonction et de ralentir la requête SELECT
, j'ai pensé à une autre façon de le faire...
Créer un champ supplémentaire dans votre base de données qui contient le résultat de la Classe suivante et lorsque vous insérez une nouvelle ligne, exécutez la valeur du champ qui sera naturellement triés par cette classe et enregistrer le résultat dans le champ supplémentaire. Alors au lieu de trier par votre champ d'origine, trier par le champ supplémentaire.
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();
}
}
pour être complet, vous trouverez ci-dessous la méthode 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);
}
Le résultat devrait sortir comme suit
-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
si vous avez besoin de trier une colonne alphanumérique qui n'a aucun format standard quel qu'il soit
SELECT * FROM table ORDER BY (name = '0') DESC, (name+0 > 0) DESC, name+0 ASC, name ASC
vous pouvez adapter cette formule pour inclure la prise en charge des caractères non alphanumériques si vous le souhaitez en utilisant une logique supplémentaire.
cela fonctionne pour le type de données: Data1, Data2, Data3 ......,Données21. Signifie que la chaîne de caractères" Data " est courante dans toutes les lignes.
pour la commande par ASC il triera parfaitement, pour la commande par DESC ne convient pas.
SELECT * FROM table_name ORDER BY LENGTH(column_name), column_name ASC;
je pense que le type de données de votre colonne est du texte ou quelque chose d'autre qui n'est pas trié correctement. changez-le en int, bigint , float comme un type numérique pour qu'il fonctionne correctement...
SELECT * FROM table ORDER BY column name ASC