Łańcuch zamówienia SQL jako liczba


137

Mam numery zapisane VARCHARw bazie danych MySQL. Nie mogę ich wykonać z INTpowodu innych zależnych okoliczności.

Podczas sortowania traktuje je jako znak, a nie liczbę.

W bazie danych mam

1 2 3 4 5 6 7 8 9 10...

Na mojej stronie pokazuje uporządkowaną listę w następujący sposób:

1 10 2 3 4 5 6 7 8 9

Jak mogę sprawić, by pojawiał się w kolejności rosnącej?


5
Z pewnością powinieneś był zapisać dane jako liczbę zamiast ciągu.
Oded

@Oded Możesz mieć dowolne dane (np. Ustawienia), które chcesz uporządkować według wartości liczbowych tylko w bardzo szczególnych przypadkach.
Glutexo

bardzo ciekawy artykuł - dotyczy MSSQL, ale powinien być stosunkowo podobny do MySQL: essentialsql.com/use-sql-server-to-sort-alphanumeric-values
AquaAlex

Odpowiedzi:


256

Jeśli to możliwe, należy zmienić typ danych kolumny na liczbę, jeśli i tak przechowujesz tylko liczby.

Jeśli nie można tego zrobić, a następnie oddać wartość kolumny do integer wyraźnie z

select col from yourtable
order by cast(col as unsigned)

lub pośrednio, na przykład za pomocą operacji matematycznej, która wymusza konwersję na liczbę

select col from yourtable
order by col + 0

BTW MySQL konwertuje łańcuchy od lewej do prawej. Przykłady:

string value  |  integer value after conversion
--------------+--------------------------------
'1'           |  1
'ABC'         |  0   /* the string does not contain a number, so the result is 0 */
'123miles'    |  123 
'$123'        |  0   /* the left side of the string does not start with a number */

3
kolejność według col * 1 działa idealnie. Jaka magia się za tym kryje? Przepraszam, nie jestem profesjonalistą, więc to może być głupie pytanie, ale jak * 1 zmienia to na numer?
Jamol

12
MySQL automatycznie konwertuje ciąg na liczbę, aby pomnożyć przez1
juergen d

1
Pierwsza opcja jest najbardziej odpowiednia, druga ma dodatkowy krok, ale działa mimo wszystko.
Manatax

5
a jeśli masz mieszany ciąg i numer i chcesz, aby oba zostały posortowane, użyj „order by col * 1, col”
Hayden Thring,

1
@superphonic: Wynik będzie decimal.
juergen d

70

Inny sposób, bez użycia jednego odlewu.

(Dla osób, które używają JPA 2.0, gdzie przesyłanie nie jest dozwolone)

select col from yourtable
order by length(col),col

EDYCJA: działa tylko dla dodatnich liczb całkowitych


9
to jest dobre, działało z kolumną zawierającą zarówno int, jak i string
Sruit A.Suk,

1
To nie zadziała we wszystkich przypadkach. Poza tym przypadki, które nie będą działać, to mieszanki liczb całkowitych z; liczby ujemne, liczby z zerami na początku, liczby z ułamkami oraz połączone słowa i liczby.
WonderWorker

3
świetne rozwiązanie bez rzucania i żonglerki, +1
Maksim Luzik

na pierwszy rzut oka nie wydawało się działać, ale zadziałało! Dziękuję Ci!
try2fly.b4ucry


17

Kolumna, z którą sortuję, ma dowolną kombinację alfanumeryczną i liczbową, więc użyłem sugestii w tym poście jako punktu wyjścia i wymyśliłem to.

DECLARE @tmp TABLE (ID VARCHAR(50));
INSERT INTO @tmp VALUES ('XYZ300');
INSERT INTO @tmp VALUES ('XYZ1002');
INSERT INTO @tmp VALUES ('106');
INSERT INTO @tmp VALUES ('206');
INSERT INTO @tmp VALUES ('1002');
INSERT INTO @tmp VALUES ('J206');
INSERT INTO @tmp VALUES ('J1002');

SELECT ID, (CASE WHEN ISNUMERIC(ID) = 1 THEN 0 ELSE 1 END) IsNum
FROM @tmp
ORDER BY IsNum, LEN(ID), ID;

Wyniki

ID
------------------------
106
206
1002
J206
J1002
XYZ300
XYZ1002

Mam nadzieję że to pomoże


Możesz po prostu użyć 1 - ISNUMERIC(ID)zamiast, (CASE WHEN ISNUMERIC(ID) = 1 THEN 0 ELSE 1 END)aby zmienić 0 na 1 i odwrotnie.
S.Serpooshan

7

To działa dla mnie.

select * from tablename
order by cast(columnname as int) asc

czy możesz mi wytłumaczyć dlaczego?
Slavik

najprostsza odpowiedź, która robi dokładnie to, co jest potrzebne, rzuć ciąg na int, a następnie uporządkuj według tego int w porządku rosnącym, bez funky :)
denford mutseriwa

4

Inny sposób na konwersję.

Jeśli masz pole tekstowe, możesz je przekształcić lub jego część numeryczną w następujący sposób: dodaj zera wiodące, aby wszystkie ciągi liczb całkowitych miały równą długość.

ORDER BY CONCAT( REPEAT(  "0", 18 - LENGTH( stringfield ) ) , stringfield ) 

lub uporządkuj według części pola coś w rodzaju „tensymbols13”, „tensymbols1222” itd.

ORDER BY CONCAT( REPEAT(  "0", 18 - LENGTH( LEFT( stringfield , 10 ) ) ) , LEFT( stringfield , 10 ) ) 

2

Szukałem również pól sortowania, które mają prefiks literowy. Oto, co znalazłem rozwiązanie. Może to pomóc temu, kto szuka tego samego rozwiązania.

Wartości pól:

FL01,FL02,FL03,FL04,FL05,...FL100,...FL123456789

select SUBSTRING(field,3,9) as field from table order by SUBSTRING(field,3,10)*1 desc

SUBSTRING(field,3,9) wstawiam 9, ponieważ 9 jest wystarczające dla mnie do przechowywania maksymalnie 9-cyfrowych wartości całkowitych.

Wynik będzie więc 123456789 123456788 123456787 ... 100 99 ... 2 1


2

To poradzi sobie z liczbami ujemnymi, ułamkami, ciągami, wszystkim:

ORDER BY ISNUMERIC(col) DESC, Try_Parse(col AS decimal(10,2)), col;

2

Może pomóc, kto szuka tego samego rozwiązania.

select * from tablename ORDER BY ABS(column_name)

0

Jeśli korzystasz z AdonisJS i masz mieszane identyfikatory, takie jak ABC-202, ABC-201 ..., możesz łączyć nieprzetworzone zapytania z Query Builder i wdrożyć powyższe rozwiązanie ( https://stackoverflow.com/a/25061144/4040835 ) następująco:

const sortField =
  'membership_id'
const sortDirection =
  'asc'
const subquery = UserProfile.query()
  .select(
    'user_profiles.id',
    'user_profiles.user_id',
    'user_profiles.membership_id',
    'user_profiles.first_name',
    'user_profiles.middle_name',
    'user_profiles.last_name',
    'user_profiles.mobile_number',
    'countries.citizenship',
    'states.name as state_of_origin',
    'user_profiles.gender',
    'user_profiles.created_at',
    'user_profiles.updated_at'
  )
  .leftJoin(
    'users',
    'user_profiles.user_id',
    'users.id'
  )
  .leftJoin(
    'countries',
    'user_profiles.nationality',
    'countries.id'
  )
  .leftJoin(
    'states',
    'user_profiles.state_of_origin',
    'states.id'
  )
  .orderByRaw(
    `SUBSTRING(:sortField:,3,15)*1 ${sortDirection}`,
    {
      sortField: sortField,
    }
  )
  .paginate(
    page,
    per_page
  )

UWAGI: W tej linii: SUBSTRING(:sortField:,3,15)*1 ${sortDirection},

  1. „3” oznacza numer indeksu ostatniego nienumerycznego znaku przed cyframi. Jeśli Twój mieszany identyfikator to „ABC-123”, Twój numer indeksu będzie wynosił 4.
  2. „15” jest używane do wyłapania jak największej liczby cyfr po łączniku.
  3. „1” wykonuje operację matematyczną na podłańcuchu, która skutecznie rzutuje podciąg na liczbę.

Ref 1: Możesz przeczytać więcej o wiązaniach parametrów w zapytaniach surowych tutaj: https://knexjs.org/#Raw-Bindings Ref 2: Adonis Raw Queries: https://adonisjs.com/docs/4.1/query-builder# _raw_queries


-3

Zmień swoje pole na INT zamiast VARCHAR.

Nie mogę uczynić ich INT z powodu innych zależnych okoliczności.

Następnie najpierw napraw zależne okoliczności. W przeciwnym razie będziesz pracować nad prawdziwym podstawowym problemem. Korzystanie z MySQL CAST jest opcją, ale maskuje twój zły schemat, który powinien zostać naprawiony.

Korzystając z naszej strony potwierdzasz, że przeczytałeś(-aś) i rozumiesz nasze zasady używania plików cookie i zasady ochrony prywatności.
Licensed under cc by-sa 3.0 with attribution required.