【PostgreSQL17】6 条件表达式

CASE

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

-- COUNT会忽略NULL
SELECTCOUNT(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
FROMemployees
;

SELECTfirst_name, last_name,CASEWHEN salary < 5000 THEN '低收入'WHEN salary BETWEEN 5000 AND 10000 THEN '中等收入'ELSE '高收入'END AS "收入"
FROMemployees
;

NULLIF

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

SELECT1 / NULLIF(0, 0)
;

SELECT1 / NULLIF(1, 0)
;

COALESCE

返回第一个不为空的值

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

SELECTfirst_name,COALESCE(commission_pct, 0)
FROMemployees
;

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

参考资料

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