CASE
WHEN C1 THEN E1
WHEN C2 THEN E2
...
WHEN CN THEN EN
[ELSE ED]
END
1.3.4.2. mysql
-＞ SELECT c.first_name, c.last_name,
-＞ CASE
-＞ WHEN active = 0 THEN 0
-＞ ELSE
-＞ (SELECT count(*) FROM rental r
-＞ WHERE r.customer_id = c.customer_id)
-＞ END num_rentals
-＞ FROM customer c;
1.3.5. 简单的case表达式
1.3.5.1. simple case expression
1.3.5.2. 类似于搜索型case表达式，但是在灵活性上略逊
1.3.5.3. sql
CASE V0
WHEN V1 THEN E1
WHEN V2 THEN E2
...
WHEN VN THEN EN
[ELSE ED]
END
1.3.5.4. sql
CASE category.name
WHEN 'Children' THEN 'All Ages'
WHEN 'Family' THEN 'All Ages'
WHEN 'Sports' THEN 'All Ages'
WHEN 'Animation' THEN 'All Ages'
WHEN 'Horror' THEN 'Adult'
WHEN 'Music' THEN 'Teens'
WHEN 'Games' THEN 'Teens'
ELSE 'Other'
END
1.4. 结果集变换
1.4.1. mysql
-＞ SELECT monthname(rental_date) rental_month,
-＞ count(*) num_rentals
-＞ FROM rental
-＞ WHERE rental_date BETWEEN '2005-05-01' AND '2005-08-01'
-＞ GROUP BY monthname(rental_date);
1.4.2. mysql
-＞ SELECT
-＞ SUM(CASE WHEN monthname(rental_date) = 'May' THEN 1
-＞ ELSE 0 END) May_rentals,
-＞ SUM(CASE WHEN monthname(rental_date) = 'June' THEN 1
-＞ ELSE 0 END) June_rentals,
-＞ SUM(CASE WHEN monthname(rental_date) = 'July' THEN 1
-＞ ELSE 0 END) July_rentals
-＞ FROM rental
-＞ WHERE rental_date BETWEEN '2005-05-01' AND '2005-08-01';
1.4.3. 如果monthname()函数返回了所需的列值，case表达式返回1；否则，返回0
1.5. 检查存在性
1.5.1. 只希望确定两个实体之间是否存在某种关系，而并不关心数量
1.5.2. mysql
-＞ SELECT a.first_name, a.last_name,
-＞ CASE
-＞ WHEN EXISTS (SELECT 1 FROM film_actor fa
-＞ INNER JOIN film f ON fa.film_id = f.film_id
-＞ WHERE fa.actor_id = a.actor_id
-＞ AND f.rating = 'G') THEN 'Y'
-＞ ELSE 'N'
-＞ END g_actor,
-＞ CASE
-＞ WHEN EXISTS (SELECT 1 FROM film_actor fa
-＞ INNER JOIN film f ON fa.film_id = f.film_id
-＞ WHERE fa.actor_id = a.actor_id
-＞ AND f.rating = 'PG') THEN 'Y'
-＞ ELSE 'N'
-＞ END pg_actor,
-＞ CASE
-＞ WHEN EXISTS (SELECT 1 FROM film_actor fa
-＞ INNER JOIN film f ON fa.film_id = f.film_id
-＞ WHERE fa.actor_id = a.actor_id
-＞ AND f.rating = 'NC-17') THEN 'Y'
-＞ ELSE 'N'
-＞ END nc17_actor
-＞ FROM actor a
-＞ WHERE a.last_name LIKE 'S%' OR a.first_name LIKE 'S%';
1.6. 除零错误
1.6.1. 执行涉及除法的运算时，应该始终注意确保分母不能为0
1.6.2. mysql
-＞ SELECT c.first_name, c.last_name,
-＞ sum(p.amount) tot_payment_amt,
-＞ count(p.amount) num_payments,
-＞ sum(p.amount) /
-＞ CASE WHEN count(p.amount) = 0 THEN 1
-＞ ELSE count(p.amount)
-＞ END avg_payment
-＞ FROM customer c
-＞ LEFT OUTER JOIN payment p
-＞ ON c.customer_id = p.customer_id
-＞ GROUP BY c.first_name, c.last_name;
1.7. 条件更新
1.7.1. 在更新数据表中的行时，有时候需要根据条件逻辑生成列值
1.7.2. sql
UPDATE customer
SET active =
CASE
WHEN 90 ＜= (SELECT datediff(now(), max(rental_date))
FROM rental r
WHERE r.customer_id = customer.customer_id)
THEN 0
ELSE 1
END
WHERE active = 1;
SELECT c.first_name, c.last_name,
CASE
WHEN a.address IS NULL THEN 'Unknown'
ELSE a.address
END address,
CASE
WHEN ct.city IS NULL THEN 'Unknown'
ELSE ct.city
END city,
CASE
WHEN cn.country IS NULL THEN 'Unknown'
ELSE cn.country
END country
FROM customer c
LEFT OUTER JOIN address a
ON c.address_id = a.address_id
LEFT OUTER JOIN city ct
ON a.city_id = ct.city_id
LEFT OUTER JOIN country cn
ON ct.country_id = cn.country_id;