Domanda MySQL 'Ordina per' - ordinamento alfanumerico correttamente


Voglio ordinare i seguenti dati nell'ordine in cui sono presentati di seguito (numeri 1-12):

1
2
3
4
5
6
7
8
9
10
11
12

Tuttavia, la mia query - utilizzando order by xxxxx asc ordina dalla prima cifra sopra ogni altra cosa:

1
10
11
12
2
3
4
5
6
7
8
9

Qualche trucco per farlo ordinare più correttamente?

Inoltre, nell'interesse della completa divulgazione, questo potrebbe essere un mix di lettere e numeri (anche se in questo momento non lo è), I.E .:

A1
534G
G46A
100B
100A
100JE

eccetera....

Grazie!

aggiornamento: le persone chiedono la query

select * from table order by name asc

44
2017-12-19 04:42


origine


risposte:


Le persone usano diversi trucchi per farlo. Ho cercato su Google e ho scoperto che alcuni risultati seguono diversi trucchi. Dai un'occhiata a loro:

Modificare:

Ho appena aggiunto il codice di ogni link per i futuri visitatori.

Ordinamento numerico alfa in MySQL

Dato input

1A 1a 10A 9B 21C 1C 1D

Uscita prevista

1A 1C 1D 1a 9B 10A 21C

domanda

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

Ordinamento naturale in MySQL

Dato input

Tabella: sorting_test
 -------------------------- -------------
| alfanumerico VARCHAR (75) | intero INT |
 -------------------------- -------------
| test1 | 1 |
| test12 | 2 |
| test13 | 3 |
| test2 | 4 |
| test3 | 5 |
 -------------------------- -------------

Uscita prevista

 -------------------------- -------------
| alphanumeric VARCHAR(75) | integer INT |
 -------------------------- -------------
| test1                    | 1           |
| test2                    | 4           |
| test3                    | 5           |
| test12                   | 2           |
| test13                   | 3           |
 -------------------------- -------------

domanda

SELECT alphanumeric, integer
       FROM sorting_test
       ORDER BY LENGTH(alphanumeric), alphanumeric  

Ordinamento di valori numerici miscelati con valori alfanumerici

Dato input

2a, 12, 5b, 5a, 10, 11, 1, 4b

Uscita prevista

1, 2a, 4b, 5a, 5b, 10, 11, 12

domanda

SELECT version
FROM version_sorting
ORDER BY CAST(version AS UNSIGNED), version;

Spero che questo ti aiuti


73
2017-12-19 05:03



So che questo post è chiuso, ma penso che il mio modo possa aiutare alcune persone. Quindi eccolo:

Il mio set di dati è molto simile ma è un po 'più complesso. Ha numeri, dati alfanumerici:

1
2
Chair 
3
0
4
5
-
Table
10
13
19
Windows
99
102
Dog

Vorrei prima avere il simbolo '-', poi i numeri, poi il testo.

Quindi vado così:

SELECT name, (name = '-') boolDash, (name = '0') boolZero, (name+0 > 0) boolNum 
FROM table 
ORDER BY boolDash DESC, boolZero DESC, boolNum DESC, (name+0), name

Il risultato dovrebbe essere qualcosa:

-
0    
1
2
3
4
5
10
13
99
102
Chair
Dog
Table
Windows

L'intera idea sta facendo un semplice controllo nel SELECT e l'ordinamento con il risultato.


12
2017-10-16 20:47



Basta fare questo:

SELECT * FROM table ORDER BY column `name`+0 ASC

L'aggiunta di +0 significa che:

0, 10, 11, 2, 3, 4

diventa:

0, 2, 3, 4, 10, 11


10
2018-03-03 18:58



Odio questo, ma Questo funzionerà

order by lpad(name, 10, 0)  <-- assuming maximum string length is 10
                            <-- you can adjust to a bigger length if you want to

5
2017-12-19 05:00



Ho avuto dei buoni risultati con

SELECT alphanumeric, integer FROM sorting_test ORDER BY CAST(alphanumeric AS UNSIGNED), alphanumeric ASC

4
2017-08-11 06:17



Questo tipo di domanda è stato chiesto in precedenza.

Il tipo di ordinamento di cui parli si chiama "Natural Sorting". I dati su cui si desidera eseguire l'ordinamento sono alfanumerici. Sarebbe meglio creare una nuova colonna per l'ordinamento.

Per ulteriore controllo dell'assistenza naturale-sort-in-mysql


1
2017-12-19 04:53



Questo dovrebbe ordinare il campo alfanumerico come: 1 / solo numero, order by 1,2,3,4,5,6,7,8,9,10,11 eccetera... 2 / Poi campo con testo come: 1foo, 2bar, aaa11aa, aaa22aa, b5452 eccetera...

SELECT  MyField
FROM MyTable
order by 
    IF( MyField REGEXP '^-?[0-9]+$' = 0, 
    9999999999 ,  
    CAST(MyField AS DECIMAL) 
    ), MyField

La query controlla se il dato è un numero, se non lo metti a 9999999999, quindi ordina prima su questa colonna, quindi ordina i dati con il testo

In bocca al lupo!


0
2017-07-04 19:47



SELEZIONARE s.id, s.name, LENGTH (s.name) len, ASCII (s.name) ASCCCI FROM nome_tabella s ORDINE DI ASCCCI, len, NOME ASC;


0
2017-07-16 13:43



Invece di cercare di scrivere qualche funzione e rallentare il SELECTquery, ho pensato ad un altro modo per farlo ...

Crea un campo aggiuntivo nel tuo database che contiene il risultato della seguente classe e quando inserisci una nuova riga, esegui il valore del campo che verrà ordinato in modo naturale attraverso questa classe e salverai il risultato nel campo extra. Quindi, anziché ordinare il campo originale, ordina per il campo extra.

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();
    }
}

Per completezza, di seguito è il StringUtils.padLeft metodo:

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);
}

Il risultato dovrebbe venire come il seguente

-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
2017-11-28 00:25



Se è necessario ordinare una colonna alfanumerica che non ha alcun formato standard di sorta

SELECT * FROM table ORDER BY (name = '0') DESC, (name+0 > 0) DESC, name+0 ASC, name ASC

È possibile adattare questa soluzione per includere il supporto per caratteri non alfanumerici, se desiderato, utilizzando una logica aggiuntiva.


0
2017-12-28 18:54



Funziona per tipo di dati:    data1, Dati2, Dati3 ......, Dati21. Significa "Dati" La stringa è comune in tutte le righe.

Per ORDER BY ASC si ordinerà perfettamente, per ORDINA PER DESC non adatto.

SELECT * FROM table_name ORDER BY LENGTH(column_name), column_name ASC;

0
2018-06-25 09:15