SQL需求：按product分组，将非最大row_id的value替换为0

返回原有结果集，但需按product分组，当row_id不是该组的最大值时，将对应的value替换为0。

当前查询结果 #

row_id   product    value  product_id
   101  product1    10      1
   102  product1    10      1
   103  product1    10      1
   104  product2    20      2
   105  product3    20      2
   106  product3    30      3
   107  product3    30      3
   108  product4    40      4

测试表结构与插入语句 #

CREATE TABLE tbl26 (row_id int, product VARCHAR2(30), value int, product_id int);
insert into tbl26 (row_id,product,value, product_id)
SELECT 101,  'product1', 10, 1    FROM DUAL UNION ALL
SELECT 102,  'product1', 10, 1    FROM DUAL UNION ALL
SELECT 103,  'product1', 10, 1   FROM DUAL UNION ALL
SELECT 104,  'product2', 20, 2   FROM DUAL UNION ALL
SELECT 105,  'product3', 20, 2   FROM DUAL UNION ALL
SELECT 106,  'product3', 30, 3   FROM DUAL UNION ALL
SELECT 107,  'product3', 30, 3   FROM DUAL UNION ALL
SELECT 108,  'product4', 40, 4   FROM DUAL;

问题与期望结果 #

尝试使用PARTITION BY未得到预期结果，期望输出如下：

row_id   product    value  product_id
  101   product1    0       1
  102   product1    0       1
  103   product1    10      1
  104   product2    0       2
  105   product3    20      2
  106   product3    0       3
  107   product3    30      3
  108   product4    40      4

解决方案 #

可以通过窗口函数结合条件判断实现需求，SQL语句如下：

SELECT 
    row_id,
    product,
    CASE 
        WHEN row_id = MAX(row_id) OVER (PARTITION BY product) THEN value 
        ELSE 0 
    END AS value,
    product_id
FROM tbl26
ORDER BY row_id;

逻辑说明 #

• MAX(row_id) OVER (PARTITION BY product)：为每一行计算其所属product分组内的最大row_id
• CASE条件判断：如果当前行的row_id等于分组内的最大值，保留原value，否则将value置为0
• 最后按row_id排序，保证结果顺序与原数据集一致

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