PostgreSQL查询：指定字段为空时选取另一字段最大值的实现

嘿，这个需求我清楚啦，我来给你写对应的PostgreSQL查询语句，并且分两种可能的场景解释，确保完全匹配你的需求：

场景1：筛选符合条件的原表记录 #

如果你的需求是从原表中筛选出两类记录：

• 一类是field1不为空，且field2在(4,5,6)范围内的记录；
• 另一类是field1为空，且field2等于所有field1非空记录中field2最大值的记录。

对应的查询语句如下：

WITH max_non_null_field2 AS (
    -- 先计算field1非空时field2的最大值
    SELECT MAX(field2) AS max_val
    FROM target_table
    WHERE field1 IS NOT NULL
)
SELECT field1, field2
FROM target_table, max_non_null_field2
WHERE 
    -- 第一种情况：field1非空且field2在指定范围
    (field1 IS NOT NULL AND field2 IN (4, 5, 6))
    -- 第二种情况：field1为空且field2等于预先计算的最大值
    OR 
    (field1 IS NULL AND field2 = max_non_null_field2.max_val);

语句解释： #

• 用WITH子句（通用表表达式，CTE）创建了一个临时结果集max_non_null_field2，专门算出所有field1非空记录里field2的最大值，避免重复计算；
• 主查询通过WHERE条件的逻辑或（OR），同时筛选出符合两种规则的记录。

场景2：替换空field1对应的field2值 #

如果你的需求是保留所有field1为空的记录，但将它们的field2替换为field1非空时的最大值，同时保留field1非空且field2在(4,5,6)范围内的记录的原有field2值。

对应的查询语句如下：

WITH max_non_null_field2 AS (
    SELECT MAX(field2) AS max_val
    FROM target_table
    WHERE field1 IS NOT NULL
)
SELECT 
    field1,
    -- 用CASE语句动态替换field2的值
    CASE 
        WHEN field1 IS NOT NULL THEN field2
        ELSE max_non_null_field2.max_val
    END AS field2
FROM target_table, max_non_null_field2
WHERE 
    (field1 IS NOT NULL AND field2 IN (4, 5, 6))
    OR 
    field1 IS NULL;

语句解释： #

• 同样先用CTE计算最大值；
• 用CASE条件表达式，对field1为空的记录，把field2替换成预先计算的最大值，其他符合条件的记录保留原field2值；
• WHERE条件确保只保留你需要的两类记录：field1非空且field2在指定范围的，以及所有field1为空的。

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