如何简化PostgreSQL查询中重复的CASE WHEN模式？

精简后的代码实现 #

SELECT
    CASE WHEN is_vip THEN NULL ELSE COALESCE(users.user_badge, users.customer_badge) END AS normal_badge,
    CASE WHEN is_vip THEN NULL ELSE users.expiration_date END AS expiration_date,
    CASE WHEN is_vip THEN NULL ELSE COALESCE(users.user_number, users.customer_number) END AS standard_user
FROM
    users
CROSS JOIN LATERAL (
    SELECT LOWER(COALESCE(users.user_number, users.customer_number)) LIKE 'vip%' AS is_vip
) vip_check;

核心精简思路 #

把重复出现的长条件LOWER(COALESCE(users.user_number, users.customer_number)) LIKE 'vip%'通过CROSS JOIN LATERAL打包成一个临时布尔列is_vip，之后所有CASE判断直接引用这个列即可——既减少了重复代码，也让逻辑更清晰，后续要修改判断规则的话只改一处就行。

备选CTE写法 #

如果更习惯用公共表表达式（CTE），也可以这么写：

WITH user_vip_status AS (
    SELECT
        *,
        LOWER(COALESCE(user_number, customer_number)) LIKE 'vip%' AS is_vip
    FROM users
)
SELECT
    CASE WHEN is_vip THEN NULL ELSE COALESCE(user_badge, customer_badge) END AS normal_badge,
    CASE WHEN is_vip THEN NULL ELSE expiration_date END AS expiration_date,
    CASE WHEN is_vip THEN NULL ELSE COALESCE(user_number, customer_number) END AS standard_user
FROM user_vip_status;

这两种写法都完全保留原查询的逻辑，不需要自定义函数，比原代码精简不少。

内容的提问来源于stack exchange，提问作者Tabares