PostgreSQL中如何获取符合最大条件的单个JSONB格式记录

DevOps 小满

2026-7-3

解决方案

要实现按id分组，获取每组中period_type最大且对应period最大的记录，并将指定字段转为单个JSONB输出，可以用PostgreSQL的窗口函数结合jsonb_build_object完成，具体步骤如下：

1. 用窗口函数筛选目标记录

使用ROW_NUMBER()窗口函数，对每个id分组内的记录先按period_type降序（取最大的类型），再按period降序（取该类型下最大的周期）排序，排序后排名为1的就是我们需要的目标记录。

2. 生成目标JSONB

对筛选出的目标记录，用jsonb_build_object把period_type、period、commission字段组装成JSONB对象，和id一起输出。

完整SQL代码

WITH ranked_records AS (
    SELECT
        id,
        period_type,
        period,
        commission,
        ROW_NUMBER() OVER (
            PARTITION BY id
            ORDER BY period_type DESC, period DESC
        ) AS rn
    FROM your_table_name -- 替换为你的实际表名
)
SELECT
    id,
    jsonb_build_object(
        'period_type', CASE period_type -- 若需要把整数转成文本展示则保留此部分，不需要可直接写period_type
            WHEN 1 THEN 'Daily'
            WHEN 2 THEN 'Monthly'
            ELSE period_type::text
        END,
        'period', period,
        'commission', commission
    ) AS result_jsonb
FROM ranked_records
WHERE rn = 1;

代码说明

WITH ranked_records：定义临时数据集，给每条记录按分组规则生成排名。
PARTITION BY id：按id分组处理每条记录。
ORDER BY period_type DESC, period DESC：先确保取到每组中period_type最大的记录，再在同类型中取period最大的。
jsonb_build_object：将指定字段的键值对组装成JSONB对象，其中CASE语句用于把实际的整数period_type转换为示例中的文本格式，无需转换可直接传入period_type字段。