SQL, jak sprawić, by wartości zerowe były ostatnie podczas sortowania rosnąco


297

Mam tabelę SQL z polem daty i godziny. Pole, o którym mowa, może mieć wartość NULL. Mam zapytanie i chcę sortować wyniki rosnąco według pola datetime, jednak chcę wierszy, w których pole datetime ma wartość null na końcu listy, a nie na początku.

Czy istnieje prosty sposób na osiągnięcie tego?



Odpowiedzi:


394
select MyDate
from MyTable
order by case when MyDate is null then 1 else 0 end, MyDate

2
Zauważ jednak, że jeśli umieścisz indeks w kolumnie sortowania w celu poprawy wydajności (*), wówczas ta metoda nieco skomplikuje plan zapytań i utraci znaczną część wydajności. * - indeksy pod warunkiem, że dane zostały wcześniej zapisane, dzięki czemu unika się sortowania według wykonania zapytania. Zaleca się, jeśli to możliwe, odfiltrowanie rekordów NULL, aby całkowicie uniknąć tego problemu.
redcalx

2
Podano również miłą
sudhAnsu63

40
order by case when MyDate is null then 1 else 0 endto naprawdę długa droga do powiedzeniaORDER BY MyDate IS NULL
Martin

5
@Martin Uwaga to pytanie nie jest oznaczone mysql. Dostarczyłem uogólnione rozwiązanie - istnieje wiele różnych sposobów robienia tego samego na różnych dbs.
RedFilter

1
@KyleDelaney Ponieważ order by 0jest interpretowany jako indeks kolumny, a indeksy kolumn są oparte na 1. Aby posortować zapytanie według trzeciej kolumny, możesz powiedzieć order by 3(co jest okropnym pomysłem na zapytania produkcyjne), ale bardzo przydatne (jak jest *) podczas eksperymentowania.
RedFilter

159

(„Trochę” późno, ale w ogóle o tym nie wspomniano)

Nie określiłeś swojego DBMS.

W standardowym SQL (i najnowszym DBMS, takim jak Oracle, PostgreSQL, DB2, Firebird, Apache Derby, HSQLDB i H2) możesz określić NULLS LASTlub NULLS FIRST:

Użyj, NULLS LASTaby posortować je do końca:

select *
from some_table
order by some_column DESC NULLS LAST

16
AFAIK NULLS FIRSTi NULLS LASTzostały dodane w SQL: 2003, ale nie ma standardowej implementacji dostępnej w różnych DMBS. W zależności od silnika bazy danych użyj ORDER BY expr some_column DESC NULLS LAST(Oracle), ORDER BY ISNULL(some_column, 1), some_column ASC(MSSQL) lub ORDER BY ISNULL(some_column), some_column ASC(MySQL z inną implementacją ISNULL ()).
SaschaM78

3
@ SaschaM78: domyślne sortowanie wartości NULL jest zależne od DBMS. Niektórzy sortują je na końcu, inni na początku. Niektórzy nie przejmują się zerami ASClub DESCzerami. Tak więc jedynym sposobem, aby to zapewnić, jest użycie, NULL FIRST/LAST jeśli DBMS to obsługuje. Na to dokumentuje , co zamierza. Użycie isnull()lub innych funkcji jest obejściem brakującego wsparcia dla NULLS FIRST/LAST( obsługiwanego przez Oracle, PostgreSQL, DB2, Firebird, Apache Derby, HSQLDB i H2)
a_horse_w_na_nazwa

Masz całkowitą rację, chciałem tylko dodać fakt, że istnieje całkiem sporo DBMS, które nie są zgodne ze standardem (jeszcze) lub mają swoje specjalizacje, takie jak Oracle wymagające exprsłowa kluczowego przy użyciu NULLS FIRST / LAST. I dzięki, że wartości null były wyświetlane jako pierwsze / ostatnie, w zależności od typu bazy danych, nie wiedziałem o tym!
SaschaM78

2
Wygląda to obiecująco, ale niestety próbowałem i NULLS LASTnie działałem w mojej bazie danych MySQL.
AdmiralThrawn

1
@AdmiralAdama: zawsze możesz uaktualnić do Postgres
a_horse_with_no_name


14
order by coalesce(date-time-field,large date in future)

10
Chociaż na ogół będzie to działać, należy zauważyć, że ta odpowiedź ma kilka problemów: duża data w przyszłości może kolidować z rzeczywistymi danymi lub być mniejsza od rzeczywistych danych, co powoduje niedokładne sortowanie. Jest to także rozwiązanie „magicznej liczby”, które nie jest samo-dokumentujące.
RedFilter

Jest to świetna alternatywa dla odpowiedzi @RedFilter, gdy trzeba porównać kolumnę, o której mowa, z inną kolumną z datą. Używam tego do listy stażystów związkowych. Jeśli pracownik ma datę kwalifikowaną (która nie ma wartości null), bąbelki tej daty zapisują się na górze, w przeciwnym razie użyj HireDate. Użycie ORDER BY ISNULL (QualifiedDate, „1-1-2099”), HireDate, LastName itp. Powoduje, że Data Qualified nie jest sprzeczna z datą HiredDate i tworzona jest poprawna lista senirity.
Alan Fisher

14

Możesz użyć wbudowanej funkcji, aby sprawdzić zerową lub zerową wartość, jak poniżej. Testuję to i działa dobrze.

select MyDate from MyTable order by ISNULL(MyDate,1) DESC, MyDate ASC;


Dla dat do pracy z mssql znalazłem użyteczne umieszczenie daleko w przyszłości daty w funkcji ISNULL, tj. ISNULL (MyDate, '2100-01-01')
Emi-C

W MySQL mówi, że przekazuję zbyt wiele parametrów. Udało mi się to przeanalizować ISNULL(MyDate) DESC, MyDate ASC, ale nie posortowano tego w prawidłowej kolejności.
AdmiralThrawn

12

Jeśli Twój silnik na to pozwala ORDER BY x IS NULL, xlub ORDER BY x NULLS LASTużyj tego. Ale jeśli to nie pomoże, mogą pomóc:

Jeśli sortujesz według typu numerycznego, możesz to zrobić: (Pożyczanie schematu z innej odpowiedzi ).

SELECT *          
FROM Employees
ORDER BY ISNULL(DepartmentId*0,1), DepartmentId;

wyświetlanie wyników posortowane według DepartmentId z ostatnimi zerami

Każda liczba inna niż null staje się 0, a null staje się 1, co sortuje null na końcu.

Możesz to również zrobić dla ciągów:

SELECT *
FROM Employees
ORDER BY ISNULL(LEFT(LastName,0),'a'), LastName

wyświetlanie wyników posortowane według LastName z zerami na końcu

Ponieważ 'a'> ''.

Działa to nawet z datami poprzez wymuszenie na zerową wartość int i użycie metody dla wartości int powyżej:

SELECT *
FROM Employees
ORDER BY ISNULL(CONVERT(INT, HireDate)*0, 1), HireDate

(Pozwala udawać, że schemat ma HireDate).

Metody te pozwalają uniknąć konieczności wymyślania lub zarządzania wartością „maksymalną” każdego typu lub naprawiania zapytań, jeśli typ danych (i maksimum) ulegną zmianie (oba problemy dotyczą innych rozwiązań ISNULL). Plus są znacznie krótsze niż CASE.


Działa świetnie ! Dziękuję
Vani,

8

Kiedy kolumna zamówienia jest liczbowa (jak ranga), możesz pomnożyć ją przez -1, a następnie uporządkować malejąco. Zachowa kolejność, której oczekujesz, ale na końcu ustawi NULL.

select *
from table
order by -rank desc

Właśnie miałem to skomentować. Nauczyłem się tego od stackoverflow.com/a/8174026/1193304 i jest świetny
Chris


3

Dzięki RedFilter za zapewnienie doskonałego rozwiązania problemu z błędami sortowania zerowalnego pola daty i godziny.

Korzystam z bazy danych SQL Server dla mojego projektu.

Zmiana wartości zerowej datetime na „1” rozwiązuje problem sortowania według kolumny typu danych datetime. Jeśli jednak mamy kolumnę z typem danych innym niż data / godzina, wówczas nie jest w stanie tego obsłużyć.

Aby obsłużyć sortowanie kolumn varchar, próbowałem użyć „ZZZZZZZ”, ponieważ wiedziałem, że kolumna nie ma wartości zaczynających się na „Z”. Działało zgodnie z oczekiwaniami.

W tych samych wierszach użyłem maksymalnych wartości +1 dla int i innych typów danych, aby uzyskać sortowanie zgodnie z oczekiwaniami. To również dało mi wyniki, jakie były wymagane.

Jednak zawsze byłoby idealnie uzyskać coś łatwiejszego w samym silniku bazy danych, który mógłby zrobić coś takiego:

Order by Col1 Asc Nulls Last, Col2 Asc Nulls First 

Jak wspomniano w odpowiedzi udzielonej przez a_horse_w_no_name.



3

Jeśli używasz MariaDB, w dokumentacji NULL Values podają następujące informacje .

Zamawianie

Gdy zamawiasz według pola, które może zawierać wartości NULL, wszystkie wartości NULL są uważane za mające najniższą wartość. Więc zamawianie w kolejności DESC spowoduje, że NULL pojawi się na końcu. Aby zmusić NULL do uznania za najwyższe wartości, można dodać kolejną kolumnę, która ma wyższą wartość, gdy głównym polem jest NULL. Przykład:

SELECT col1 FROM tab ORDER BY ISNULL(col1), col1;

Kolejność malejąca, najpierw NULL:

SELECT col1 FROM tab ORDER BY IF(col1 IS NULL, 0, 1), col1 DESC;

Wszystkie wartości NULL są również uważane za równoważne do celów klauzul DISTINCT i GROUP BY.

Powyżej pokazano dwa sposoby zamawiania według wartości NULL, można je również połączyć ze słowami kluczowymi ASC i DESC. Na przykład innym sposobem uzyskania wartości NULL w pierwszej kolejności byłoby:

SELECT col1 FROM tab ORDER BY ISNULL(col1) DESC, col1;
--                                         ^^^^

2

Rozwiązanie wykorzystujące „przypadek” jest uniwersalne, ale nie używaj indeksów.

order by case when MyDate is null then 1 else 0 end, MyDate

W moim przypadku potrzebowałem wydajności.

 SELECT smoneCol1,someCol2  
 FROM someSch.someTab
 WHERE someCol2 = 2101 and ( someCol1 IS NULL ) 
  UNION   
 SELECT smoneCol1,someCol2
 FROM someSch.someTab
 WHERE someCol2 = 2101 and (  someCol1 IS NOT NULL)  

1
Jeśli jesteś zainteresowany wydajnością, powinieneś używać UNION ALL.
RedFilter


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.