【PostgreSQL 17】6 条件表达式

CASE

SELECT
	first_name, 
	last_name,
	CASE department_id 
		WHEN 90 THEN '管理'
		WHEN 60 THEN '开发'
		ELSE '其他'
	END AS "部门"
FROM employees
;

-- COUNT会忽略NULL
SELECT
	COUNT(CASE department_id WHEN 10 THEN 1 END) AS dept10_count,
	COUNT(CASE department_id WHEN 20 THEN 1 END) AS dept20_count,
	COUNT(CASE department_id WHEN 30 THEN 1 END) AS dept30_count
FROM
	employees
;

SELECT
	first_name, last_name,
	CASE
		WHEN salary < 5000 THEN '低收入'
		WHEN salary BETWEEN 5000 AND 10000 THEN '中等收入'
		ELSE '高收入'
	END AS "收入"
FROM
	employees
;

NULLIF

使用NULLIF避免除0报错。相同返回NULL，不同返回前者。

SELECT
	1 / NULLIF(0, 0)
;

SELECT
	1 / NULLIF(1, 0)
;

COALESCE

返回第一个不为空的值

SELECT
	COALESCE(1, 2, 3) AS "1",
	COALESCE(null, 2, 3) AS "2",
	COALESCE(null, null, 3) AS "3"
;

SELECT
	first_name,
	COALESCE(commission_pct, 0)
FROM
	employees
;

把一些null转化为了0，这样看起来舒服。

参考资料

[1] 不剪发的Tony老师【PostgreSQL开发指南】第17节 条件表达式