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
46
demandé sur ajreal 2011-12-19 08:42:31

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:

Edit:

je viens d'ajouter le code de chaque lien pour les futurs visiteurs.

Tri alphanumérique dans MySQL

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

tri naturel en MySQL

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

76
répondu Jomoos 2017-05-23 10:31:26

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.

12
répondu antoine 2013-10-16 20:47:05

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

10
répondu Andrew Odendaal 2016-03-03 18:58:35

je déteste cela, mais ce travaillera

order by lpad(name, 10, 0)  <-- assuming maximum string length is 10
                            <-- you can adjust to a bigger length if you want to
5
répondu ajreal 2011-12-19 05:00:13

j'ai eu de bons résultats avec

SELECT alphanumeric, integer FROM sorting_test ORDER BY CAST(alphanumeric AS UNSIGNED), alphanumeric ASC
4
répondu Blouarf 2016-08-11 06:17:52

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

1
répondu nishantagarwal 2017-05-23 12:02:38

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!

0
répondu user8255718 2017-07-05 02:09:46

sélectionner S. id., art. nom, longueur(art. nom) len, ASCII(art. nom) ASCCCI De table_name s Ordre de ASCCCI, len, nom ASC;

0
répondu Hosain Ahmed 2017-08-07 07:13:24

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
0
répondu Christian 2017-11-28 18:12:05

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.

0
répondu Weston Ganger 2017-12-28 18:54:21

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;
0
répondu ShivBuyya 2018-06-25 09:15:57

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
-2
répondu Om Infowave Developers 2015-06-26 06:23:43