PostgreSQL中指定列数据重复时对应行值置空的SQL实现方案

要实现你需要的效果——仅保留每组cms_code的第一行值，后续重复行的该列置为NULL——用PostgreSQL的窗口函数就能轻松搞定，核心是给每个分组内的行标记序号，再通过条件判断控制显示内容。

核心SQL语句 #

假设你的表名为your_table，可以用以下查询：

SELECT
    CASE
        WHEN row_number() OVER (PARTITION BY cms_code ORDER BY ctid) = 1
        THEN cms_code
        ELSE NULL
    END AS cms_code,
    product
FROM your_table
ORDER BY cms_code IS NULL, cms_code, ctid;

语句解释 #

1. 窗口函数分组编号：PARTITION BY cms_code将数据按cms_code分组，row_number()给每个分组内的行从1开始依次编号。这里用ctid作为排序依据是因为它是PostgreSQL内置的行物理位置标识符，能保持数据原有的存储顺序；如果你的表有自己的业务排序字段（比如主键、创建时间），替换成对应的字段会更可靠。
2. 条件判断置空：通过CASE语句，仅保留每组中序号为1的行的cms_code值，其余行该列显示NULL。
3. 结果排序：ORDER BY cms_code IS NULL, cms_code, ctid确保结果中，每个分组的首行（非NULL的cms_code）排在最前面，同组的其他行紧随其后，和你要求的输出顺序一致。

用示例数据测试 #

如果要直接验证效果，可以用WITH子句构建你的示例数据进行测试：

WITH sample_data AS (
    SELECT '55381XA384' AS cms_code, 'MS' AS product UNION ALL
    SELECT '55381XA384' AS cms_code, 'MS' AS product UNION ALL
    SELECT '55381XA384' AS cms_code, 'HSD' AS product UNION ALL
    SELECT '55381XA384' AS cms_code, 'HSD' AS product UNION ALL
    SELECT '55381XA382' AS cms_code, 'HSD' AS product UNION ALL
    SELECT '55381XA382' AS cms_code, 'XP' AS product UNION ALL
    SELECT '55381XA382' AS cms_code, 'MS' AS product UNION ALL
    SELECT '55381XA382' AS cms_code, 'HSD' AS product UNION ALL
    SELECT '55381XA382' AS cms_code, 'HSD' AS product
)
SELECT
    CASE
        WHEN row_number() OVER (PARTITION BY cms_code ORDER BY ctid) = 1
        THEN cms_code
        ELSE NULL
    END AS cms_code,
    product
FROM sample_data
ORDER BY cms_code IS NULL, cms_code, ctid;

执行这段代码就能得到你期望的输出结果。

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