SQL за подреждане по номер - 1,2,3,4 и т.н. вместо 1,10,11,12

Един от начините за подреждане по положителни цели числа, когато те се съхраняват като varchar, е първо да се подреди по дължината и след това по стойността:

order by len(registration_no), registration_no

Това е особено полезно, когато колоната може да съдържа нечислови стойности.

Забележка: в някои бази данни функцията за получаване на дължината на низ може да се нарича length() вместо len().

ORDER_BY cast(registration_no as unsigned) ASC

изрично преобразува стойността в число. Друга възможност за постигане на същото би била

ORDER_BY registration_no + 0 ASC

което ще наложи имплицитен разговор.

Всъщност трябва да проверите дефиницията на таблицата и да я промените. Можете да промените типа данни на int по този начин

ALTER TABLE your_table MODIFY COLUMN registration_no int;

Ако използвате SQL Server:

ORDER_BY cast(registration_no as int) ASC

ORDER_BY cast(registration_no as unsigned) ASC

дава желания резултат с предупреждения.

Следователно, по-добре да отидете

ORDER_BY registration_no + 0 ASC

за чист резултат без никакви SQL предупреждения.

Предполагам, че вашият тип колона е STRING (CHAR, VARCHAR и т.н.) и процедурата за сортиране я сортира като низ. Това, което трябва да направите, е да конвертирате стойността в числова стойност. Как да го направите ще зависи от SQL системата, която използвате.

Понякога просто нямате избор да съхранявате числа, смесени с текст. В едно от нашите приложения хостът на уеб сайта, който използваме за нашия сайт за електронна търговия, създава динамични филтри от списъци. Няма опция за сортиране по което и да е поле освен по показания текст. Когато искахме филтри, изградени от списък, който казва неща като 2" до 8" 9" до 12" 13" до 15" и т.н., ни трябваше да сортираме 2-9-13, а не 13-2-9, както ще стане, когато четене на числовите стойности. Така че използвах функцията за репликация на SQL Server заедно с дължината на най-дългото число, за да допълня всички по-къси числа с водещ интервал. Сега 20 се сортира след 3 и т.н.

Работех с изглед, който ми даде минималните и максималните дължини, ширини и т.н. за типа и класа на елемента и ето пример за това как направих текста. (LBnLow и LBnHigh са ниският и високият край на 5-те скоби за дължина.)

REPLICATE(' ', LEN(LB5Low) - LEN(LB1High)) + CONVERT(NVARCHAR(4), LB1High) + '" and Under' AS L1Text,
REPLICATE(' ', LEN(LB5Low) - LEN(LB2Low)) + CONVERT(NVARCHAR(4), LB2Low) + '" to ' + CONVERT(NVARCHAR(4), LB2High) + '"' AS L2Text,
REPLICATE(' ', LEN(LB5Low) - LEN(LB3Low)) + CONVERT(NVARCHAR(4), LB3Low) + '" to ' + CONVERT(NVARCHAR(4), LB3High) + '"' AS L3Text,
REPLICATE(' ', LEN(LB5Low) - LEN(LB4Low)) + CONVERT(NVARCHAR(4), LB4Low) + '" to ' + CONVERT(NVARCHAR(4), LB4High) + '"' AS L4Text,
CONVERT(NVARCHAR(4), LB5Low) + '" and Over' AS L5Text

Предпочитам да правя "PAD" пред данните. MySql го нарича LPAD, но можете да работите по пътя си, за да направите същото нещо в SQL Server.

ORDER BY  REPLACE(STR(ColName, 3), SPACE(1), '0') 

Тази формула ще предостави водещи нули въз основа на дължината на колоната от 3. Тази функционалност е много полезна в други ситуации извън ORDER BY, затова исках да предоставя тази опция.

Резултати: 1 става 001, а 10 става 010, докато 100 остава същото.

Този проблем е само защото сте декларирали колоната в тип данни CHAR, VARCHAR или TEXT. Просто променете типа данни на INT, BIGINT и т.н. Това ще реши проблема с вашето персонализирано подреждане.