PostgreSQL单查询获取不受WHERE子句影响的列全局最大最小值

实现方案 #

你可以通过交叉连接一个仅返回全表统计值的单行子查询实现需求，不需要额外JSON解析，也不需要重复执行过滤逻辑，全局统计值会自动附加到每一行的查询结果中：

select 
    p.id as p_id,
    p.name as p_name,
    p.tags,
    p.creator,
    p.value,
    p.creation_date,
    cp.id as c_part_id,
    fr.distance,
    count(*) OVER() AS filtered_total, -- 保留原有逻辑：符合WHERE条件的总条数
    global_stats.global_min_value,
    global_stats.global_max_value
from t_p p 
    left join t_c_part cp on p.id = cp.p_id 
    left join t_fl fr on p.id = fr.p_id
    -- 全表统计子查询，仅执行一次
    cross join (
        select min(value) as global_min_value, max(value) as global_max_value from t_p
    ) as global_stats
where p.name = 'test'
ORDER BY p.id ASC, p.name ASC 
OFFSET 0 FETCH NEXT 25 ROWS only;

逻辑说明 #

• 子查询global_stats只会对全表t_p执行一次聚合计算，返回单行的全局最大、最小值，不受主查询WHERE条件影响
• 交叉连接不会改变原有查询的行数，所有返回的分页行都会附带相同的global_min_value和global_max_value，后端直接取任意一行的这两个字段即可拿到全局统计值
• 注意：你原来的查询存在语法错误，p.value和fr.distance字段后缺少逗号，已在上述示例中修正

可维护性优化版本（CTE写法） #

如果你希望把过滤逻辑抽离出来，避免后续修改时多次调整条件，可以用CTE改写，执行效率和上述写法基本一致：

with filtered_data as (
    select 
        p.id as p_id,
        p.name as p_name,
        p.tags,
        p.creator,
        p.value,
        p.creation_date,
        cp.id as c_part_id,
        fr.distance
    from t_p p 
        left join t_c_part cp on p.id = cp.p_id 
        left join t_fl fr on p.id = fr.p_id
    where p.name = 'test'
),
global_stats as (
    select min(value) as global_min_value, max(value) as global_max_value from t_p
)
select 
    fd.*,
    count(*) OVER() AS filtered_total,
    gs.global_min_value,
    gs.global_max_value
from filtered_data fd
cross join global_stats gs
ORDER BY fd.p_id ASC, fd.p_name ASC 
OFFSET 0 FETCH NEXT 25 ROWS only;

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