Łańcuch zamówienia SQL jako liczba

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?

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 */

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

Inny i prosty sposób

ORDER BY ABS(column_name)

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

To działa dla mnie.

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

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

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

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

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

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

select * from tablename ORDER BY ABS(column_name)

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

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.