Fonctions ⑤ — CASE pour un branchement multi-conditions

Données utilisées dans cet article — la table staff

Le IIF couvert dans l'article précédent n'avait qu'une condition, donc il ne pouvait faire qu'un branchement à deux issues vrai/faux. L'expression CASE de cet article évalue trois conditions ou plus dans l'ordre et peut renvoyer une valeur différente pour chacune. C'est la syntaxe de base pour écrire un « if-elseif-else » dans SQL, et c'est la construction de branchement la plus courante dans le travail réel — catégorisation de rapports, regroupement par région, classement par grade, etc.

Le sujet est la même table staff (10 lignes) que précédemment. À travers des exercices qui construisent une classification par région sur la colonne city, une classification par tranche de paie sur salary, et une classification générationnelle sur birthday, tu essaieras les deux formes de CASE et leur application aux conditions composées dans l'ordre.

Avant les exercices, confirmons les définitions de colonnes et un échantillon de données de la table staff.

① Utilise PRAGMA table_info(staff); pour vérifier les noms de colonnes, les types et la clé primaire.

② Utilise SELECT * FROM staff LIMIT 5; pour prévisualiser les 5 premières lignes.

CASE a deux manières de s'écrire

L'expression CASE a deux formes — la forme simple et la forme avec recherche — et les deux peuvent produire le même résultat. Deux règles sont communes aux deux.

• Le `END` final est obligatoire — l'oublier est une erreur de syntaxe.
• `ELSE` est facultatif — s'il est omis, les lignes ne correspondant à aucun WHEN deviennent NULL. Pour éviter NULL, indique un défaut explicitement avec ELSE.

Forme ① Forme simple — Comparaison d'égalité sur une colonne

La forme simple s'écrit CASE colonne WHEN valeur1 THEN résultat1 WHEN valeur2 THEN résultat2 ... ELSE défaut END. Juste après CASE tu places une colonne à comparer, et à droite de WHEN tu écris une valeur à tester en égalité contre la colonne. Le test est l'égalité (`=`) uniquement, et tu peux écrire une seule valeur à droite de WHEN — plusieurs valeurs comme WHEN ('Tokyo', 'Osaka') ne sont pas autorisées. Elle convient à une simple substitution colonne = valeur et est plus courte à écrire.

-- Forme simple — remplacer city par un nom de région
SELECT name, city,
  CASE city
    WHEN 'Tokyo' THEN 'Kanto'
    WHEN 'Yokohama' THEN 'Kanto'
    WHEN 'Osaka' THEN 'Kansai'
    WHEN 'Kyoto' THEN 'Kansai'
    ELSE 'Other'
  END AS region
FROM staff;

Imagine l'exigence « afficher la colonne city sous forme de code court ». (L'explication apparaît une fois que tu l'exécutes correctement.)

① Depuis la table staff, prends name et city.

② Avec la forme simple de CASE, ajoute une 3e colonne avec l'alias city_code qui vaut 'TOK' si city est Tokyo, 'OSA' si Osaka, 'KYO' si Kyoto, 'YOK' si Yokohama, 'SAP' si Sapporo, et la valeur city d'origine si aucun ne correspond.

Forme ② Forme avec recherche — Écrire n'importe quelle condition librement

La forme avec recherche s'écrit CASE WHEN expr1 THEN résultat1 WHEN expr2 THEN résultat2 ... ELSE défaut END. Juste après CASE tu n'écris rien, et à droite de chaque WHEN tu mets la condition elle-même. Des opérateurs de comparaison comme salary >= 6000000, des LIKE tels que name LIKE '%Tanaka', IN, et des conditions composées AND / OR — tout ce que tu peux écrire dans WHERE fonctionne ici. WHEN est évalué de haut en bas, et la valeur est décidée par la première branche qui devient TRUE. Parce qu'elle est si expressive, c'est la forme principalement utilisée en pratique.

-- Forme avec recherche — répartir salary en 2 tranches
SELECT name, salary,
  CASE
    WHEN salary >= 5000000 THEN 'High'
    ELSE 'Standard'
  END AS pay_band
FROM staff;

Imagine l'exigence « classer le personnel en 3 paliers (Junior / Mid / Senior) par salaire ».

① Depuis la table staff, prends name et salary.

② Avec la forme avec recherche de CASE, ajoute une 3e colonne avec l'alias tier qui vaut 'Senior' si salary est 6 000 000 ou plus, 'Mid' si 4 500 000 ou plus mais moins de 6 000 000, et 'Junior' en dessous.

Combiner avec des fonctions et des conditions composées pour un branchement plus complexe

Une clause CASE WHEN peut contenir des expressions combinant opérateurs de comparaison, LIKE, IN, AND / OR, et appels de fonctions. Tu peux extraire l'année de naissance avec strftime('%Y', birthday) vu dans Fonctions ① fonctions de date pour une classification générationnelle ; puisque name est dans l'ordre « prénom nom », name LIKE '%Tanaka' (correspondance suffixe) classe les personnes dont le nom de famille est Tanaka ; et tu peux écrire des conditions combinant plusieurs colonnes avec AND.

L'exemple ci-dessous extrait l'année de naissance par tranches de 10 ans et libelle les personnes nées dans les années 1990 en 'Gen Z' et celles des années 1980 en 'Gen X-Y'. Comme strftime renvoie une chaîne, les comparaisons se font contre des littéraux de chaîne ('1990' / '1999').

-- 1) Libeller l'année de naissance par tranches de 5 ans
SELECT name, birthday,
  CASE
    WHEN strftime('%Y', birthday) BETWEEN '1985' AND '1989' THEN 'Late 1980s'
    WHEN strftime('%Y', birthday) BETWEEN '1990' AND '1994' THEN 'Early 1990s'
    ELSE 'Other'
  END AS cohort
FROM staff;

-- 2) Condition composée : salaire élevé AND basé à Tokyo
SELECT name, city, salary,
  CASE
    WHEN salary >= 6000000 AND city = 'Tokyo' THEN 'Tokyo senior'
    WHEN salary >= 6000000 THEN 'Senior'
    ELSE 'Other'
  END AS tag
FROM staff;

Imagine l'exigence « répartir tout le personnel en générations par année de naissance par tranches de 10 ans ».

① Depuis la table staff, prends name et birthday.

② Avec la forme avec recherche de CASE, ajoute une 3e colonne avec l'alias generation qui vaut 'Gen Z' si l'année de naissance extraite de birthday est dans les années 1990, 'Gen X-Y' si dans les années 1980, et 'Other' sinon.

Imagine l'exigence « afficher le TOP 5 par salaire sur un écran de classement, chacun annoté d'un libellé de tranche de paie ».

① Depuis la table staff, prends name et salary.

② Avec la forme avec recherche de CASE, ajoute une 3e colonne avec l'alias tier avec les mêmes libellés de tranche de paie que la Pratique 2 ('Senior' si salary est 6 000 000 ou plus, 'Mid' si 4 500 000 ou plus, 'Junior' sinon).

③ Trie par `salary` décroissant et limite aux 5 premières lignes.