postgresql 中 case 的用法

SQL CASE表达式是一种通用的条件表达式，类似于其它语言中的if/else语句。
1、带 ELSE 的 CASE 表达式
让我们使用 CASE 表达式对员工进行薪水分析，其中薪水将根据以下范围分为低、平均、非常好和无数据：

‘Low’：如果薪水 < 50000
‘Average’ ： 如果工资 > 50000 AND 工资 <= 100000
‘High’： 如果薪水 > 100000
‘No Data’：如果工资为空

SELECT emp_id, first_name, last_name, salary,
CASE WHEN salary <= 50000 THEN ‘Low’
WHEN salary > 50000 AND salary <= 100000 THEN ‘Average’
WHEN salary > 100000 THEN ‘High’
ELSE ‘No Data’
END salary_analysis
FROM Employee;
2、简单的 CASE 表达式
SELECT emp_id, first_name, last_name,
CASE gender
WHEN ‘M’ THEN ‘Male’
WHEN ‘F’ THEN ‘Female’
END gender
FROM Employee;

========================================
CASE WHEN condition THEN result
[WHEN …]
[ELSE result]
END

select sum(city_id),CASE
when city_id < 3 then ‘小于3’
when city_id > 8 then ‘大于8’
else ‘大于3小于8’ END
FROM city
group by
CASE
when city_id < 3 then ‘小于3’
when city_id > 8 then ‘大于8’
else ‘大于3小于8’ END
order by
CASE
when city_id < 3 then ‘小于3’
when city_id > 8 then ‘大于8’
else ‘大于3小于8’ END DESC

select SUM(city_id),case name
when ‘北京’ then ‘古都’
when ‘西安’ THEN ‘古都’
when ‘上海’ THEN ‘魔都’
when ‘NewYork’ then ‘纽约’
else ‘其他城市’ END
from city
GROUP BY
case name
when ‘北京’ then ‘古都’
when ‘西安’ THEN ‘古都’
when ‘上海’ THEN ‘魔都’
when ‘NewYork’ then ‘纽约’
else ‘其他城市’ END