Większość raportów wymaga pewnej logiki: oznaczaj zamówienia jako duże lub małe, zliczaj zamówienia opłacone i oczekujące obok siebie lub najpierw pokazuj pozycje pilne. SQL CASE WHEN obsługuje to wszystko bezpośrednio w zapytaniu i działa we wszystkich głównych bazach danych.

Uruchomiliśmy każde zapytanie w SQLite 3.50 i pokazane wyniki pochodzą z tego uruchomienia. CASE jest częścią standardowego SQL, więc w tych przykładach zachowuje się tak samo w PostgreSQL, MySQL, SQL Server i Oracle.

Co to jest PRZYPADEK KIEDY?

CASE jest wyrażeniem, a nie oddzielnym poleceniem. Ocenia warunki w kolejności i zwraca wartość, więc może przejść wszędzie tam, gdzie dozwolona jest wartość: w SELECT, WHERE, GROUP BY, ORDER BY lub wewnątrz funkcji. Zawsze kończy się na END.

CASE
  WHEN condition1 THEN result1
  WHEN condition2 THEN result2
  ELSE fallback
END

Przykładowa tabela

CREATE TABLE orders (id INT, customer TEXT, total INT, status TEXT);

INSERT INTO orders VALUES
 (1,'Asha',1200,'paid'), (2,'Ben',300,'pending'),
 (3,'Asha',800,'paid'), (4,'Chitra',2500,'shipped'),
 (5,'Dev',150,'cancelled'), (6,'Ben',NULL,'pending');

Zauważ, że rząd 6 nie ma sumy. Ten rząd ma znaczenie później.

Przeszukany przypadek

Jest to bardziej elastyczna forma, ponieważ każdy WHEN ma swój własny pełny warunek:

SELECT id, total,
       CASE
         WHEN total >= 1000 THEN 'large'
         WHEN total >= 500 THEN 'medium'
         ELSE 'small'
       END AS size
FROM orders;

-- 1 | 1200 | large
-- 2 |  300 | small
-- 3 |  800 | medium
-- 4 | 2500 | large
-- 5 |  150 | small
-- 6 | NULL | small

Spójrz na ostatni rząd. Zamówienie 6 nie ma sumy, ale jest oznaczone jako „małe”. Obydwa porównania z wartością NULL nie były prawdziwe, więc padło na ELSE. Jak to naprawić, pokazujemy w sekcji NULL. Zawsze nadawaj wynikowi nazwę, używając AS, w przeciwnym razie możesz otrzymać niezręczną nazwę kolumny.

Prosty PRZYPADEK

Kiedy porównujesz jedną kolumnę ze stałymi wartościami, krótsza forma jest lepiej czytana:

SELECT id,
       CASE status
         WHEN 'paid' THEN 'Complete'
         WHEN 'shipped' THEN 'Complete'
         WHEN 'pending' THEN 'Waiting'
       END AS label
FROM orders;

-- 1 | Complete
-- 2 | Waiting
-- 3 | Complete
-- 4 | Complete
-- 5 | NULL
-- 6 | Waiting

Porządek ma znaczenie

CASE zatrzymuje się na pierwszym prawdziwym warunku i jest to najczęstszy błąd logiczny. Zamień dwa warunki z naszego wcześniejszego zapytania:

SELECT id, total,
       CASE
         WHEN total >= 500 THEN 'medium'
         WHEN total >= 1000 THEN 'large'
         ELSE 'small'
       END AS size
FROM orders WHERE id IN (1, 4);

-- 1 | 1200 | medium
-- 4 | 2500 | medium

Teraz już nic nie może być „duże”. Suma 2500 to także co najmniej 500, więc pierwsza gałąź wygrywa, a druga nigdy nie zostaje osiągnięta. Nie pojawia się żaden błąd, tylko błędne etykiety. W przypadku zakresów najpierw umieść najbardziej restrykcyjny warunek lub zapisz całe zakresy, np. total >= 500 AND total < 1000.

Co się stanie, jeśli nie będzie WIĘCEJ?

W naszym prostym przykładzie CASE powyżej zamówienie 5 zostało anulowane i żaden oddział się tym nie zajął, więc wynik był NULL. Czasami właśnie tego chcesz, jak pokazano w następnej sekcji. Jednak przy tworzeniu etykiet zwykle lepsze jest jasne ELSE 'other', ponieważ uwidacznia nieoczekiwane wartości, zamiast pozostawiać puste miejsca w raporcie.

Raporty w stylu przestawnym z SUM

Jest to jeden z najbardziej przydatnych wzorców w codziennym języku SQL. Zawijanie CASE w agregację zamienia wiersze w kolumny, z jednym wierszem na klienta:

SELECT customer,
       SUM(CASE WHEN status = 'paid' THEN total ELSE 0 END) AS paid,
       SUM(CASE WHEN status = 'pending' THEN 1 ELSE 0 END) AS pending_orders
FROM orders
GROUP BY customer
ORDER BY customer;

-- Asha   | 2000 | 0
-- Ben    |    0 | 2
-- Chitra |    0 | 0
-- Dev    |    0 | 0

Jedno zapytanie daje teraz zarówno wypłacone przychody, jak i liczbę oczekujących zamówień, bez potrzeby stosowania oddzielnych zapytań lub łączeń. Wzorzec działa w prawie każdej bazie danych, w przeciwieństwie do dedykowanej składni PIVOT, którą oferują tylko niektóre. Jeśli GROUP BY jest dla Ciebie nowy, przeczytaj SQL GROUP BY i HAVING.

Liczy się warunkowo

COUNT pomija wartości NULL, więc CASE bez ELSE zlicza tylko pasujące wiersze:

SELECT COUNT(*) AS all_orders,
       COUNT(CASE WHEN status = 'paid' THEN 1 END) AS paid_orders,
       COUNT(CASE WHEN total >= 1000 THEN 1 END) AS large_orders
FROM orders;

-- 6 | 2 | 2

Bądź ostrożny: jeśli dodasz ELSE 0 wewnątrz COUNT, każdy wiersz będzie liczony, ponieważ 0 nie jest równe NULL. Użyj ELSE 0 z SUM i pomiń to z COUNT. PostgreSQL i SQLite obsługują również COUNT(*) FILTER (WHERE ...), co ładnie się czyta, ale nie jest dostępne wszędzie.

Grupowanie według CASE

Możesz tworzyć własne segmenty, a następnie podsumowywać je:

SELECT CASE WHEN total >= 1000 THEN 'large' ELSE 'other' END AS bucket,
       COUNT(*)
FROM orders
GROUP BY bucket
ORDER BY bucket;

-- large | 2
-- other | 4

Grupowanie według aliasów działa w SQLite, PostgreSQL i MySQL. SQL Server na to nie pozwala, więc musiałbyś powtórzyć całe wyrażenie lub najpierw umieścić zapytanie w CTE lub podzapytaniu.

Sortowanie niestandardowe za pomocą ORDER BY

Kolejność alfabetyczna rzadko jest tym, czego oczekuje firma. CASE umożliwia zdefiniowanie własnego zamówienia:

SELECT id, status FROM orders
ORDER BY CASE status
           WHEN 'pending' THEN 1
           WHEN 'paid' THEN 2
           ELSE 3
         END, id;

-- 2 | pending
-- 6 | pending
-- 1 | paid
-- 3 | paid
-- 4 | shipped
-- 5 | cancelled

Zamówienia oczekujące są na pierwszym miejscu, co jest przydatne w przypadku kolejki roboczej. Druga kolumna sortowania id utrzymuje wiersze w przewidywalnej kolejności w każdej grupie.

CASE w AKTUALIZACJI

CASE pozwala jednej instrukcji zastosować różne zmiany w różnych wierszach. W tym miejscu małe oczekujące zamówienia są oznaczane do sprawdzenia:

UPDATE orders
SET status = CASE
               WHEN status = 'pending' AND total < 500 THEN 'review'
               ELSE status
             END;

SELECT id, status FROM orders WHERE customer = 'Ben';

-- 2 | review
-- 6 | pending

ELSE status powoduje, że inne wiersze pozostają niezmienione. Pominięcie tego jest niebezpieczne, ponieważ niedopasowane wiersze zostaną ustawione na NULL. Bez klauzuli WHERE ta instrukcja dotyczy także każdego wiersza, dlatego w przypadku dużych tabel dodaj filtr i rozważ zawinięcie zmiany w transakcji, aby móc sprawdzić wynik przed zatwierdzeniem. Zauważ też, że rząd 6 nie uległ zmianie, ponieważ jego suma NULL nie spełniła total < 500.

Pułapki NULL

NULL powoduje dwa oddzielne problemy z CASE. Po pierwsze, prosta forma porównuje przy użyciu równości i nic nie jest równe NULL, nawet samo NULL:

SELECT CASE NULL WHEN NULL THEN 'matched' ELSE 'not matched' END;

-- not matched

Zatem CASE col WHEN NULL nigdy nie pasuje. Zamiast tego użyj szukanego formularza z IS NULL. Po drugie, jak widzieliśmy w przypadku rzędu 6, wartość NULL wymyka się z porównań i ląduje w ELSE. Rozwiązanie polega na tym, aby najpierw to sprawdzić:

SELECT id,
       CASE
         WHEN total IS NULL THEN 'unknown'
         WHEN total >= 1000 THEN 'large'
         ELSE 'other'
       END AS size
FROM orders WHERE id IN (1, 2, 6);

-- 1 | large
-- 2 | other
-- 6 | unknown

Zamówienie 6 otrzymuje teraz uczciwą etykietę. Nasz przewodnik po wartościach SQL NULL opisuje w pełni to zachowanie.

CASE vs inne opcje

Ta ostatnia opcja zasługuje na przemyślenie. Jeśli CASE rozrośnie się do trzydziestu gałęzi i pojawi się w pięciu zapytaniach, przenieś mapowanie do tabeli i dołącz do niej. Wtedy zmiana jest aktualizacją w jednym wierszu, a nie zmianą kodu w pięciu miejscach.

Typowe błędy

• Zła kolejność warunków. Szeroki warunek najpierw ukrywa węższe.
• Zapominanie END. To najczęstszy błąd składniowy.
• Używając WHEN NULL. To nigdy nie pasuje, więc użyj WHEN col IS NULL.
• Dodanie ELSE 0 do COUNT. Zlicza wszystkie wiersze.
• Pomijanie ELSE w AKTUALIZACJI. Może wyczyścić wartości z wartością NULL.
• Filtrowanie według CASE w WHERE. Może uniemożliwić użycie indeksu. Zobacz nasz przewodnik po optymalizacji zapytań SQL.

Podsumowanie

CASE WHEN to niewielka funkcja, która wykonuje ogromną pracę. Zapamiętaj trzy zasady: wygrywa pierwsza prawdziwa gałąź, niedopasowane wiersze stają się NULL bez ELSE, a NULL wymaga własnego sprawdzenia. Aby poćwiczyć, weź raport, który aktualnie tworzysz z kilkoma zapytaniami i spróbuj utworzyć go jako jedno zapytanie, używając SUM(CASE ...).

Najczęstsze pytania

Ucz się dalej