如何用SQL为分组结果生成tot_cust标识列（同id_首行1其余0）

完全可以通过CASE WHEN结合窗口函数实现这个需求。核心思路是先通过窗口函数给每个id_分组内的行标记序号，再用CASE WHEN判断序号是否为1来赋值tot_cust。

实现代码 #

基于你原有的分组查询，修改后的SQL如下（以CTE写法为例，可读性更强）：

WITH grouped_data AS (
    SELECT id_, product, sum(price) AS total  
    FROM example     
    GROUP BY id_, product  
    ORDER BY id_, product
)
SELECT 
    id_,
    product,
    total,
    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY id_ ORDER BY product) = 1 THEN 1
        ELSE 0
    END AS tot_cust
FROM grouped_data
ORDER BY id_, product;

如果你的SQL环境不支持CTE，也可以用子查询嵌套：

SELECT 
    id_,
    product,
    total,
    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY id_ ORDER BY product) = 1 THEN 1
        ELSE 0
    END AS tot_cust
FROM (
    SELECT id_, product, sum(price) AS total  
    FROM example     
    GROUP BY id_, product  
    ORDER BY id_, product
) AS sub_query
ORDER BY id_, product;

逻辑说明 #

1. ROW_NUMBER() OVER (PARTITION BY id_ ORDER BY product)：按id_分组，每组内按product排序（和原查询的排序规则一致，确保首行是你预期的第一行），给每行分配一个唯一序号。
2. CASE WHEN判断：当序号为1时，tot_cust赋值为1，其余行赋值为0，完美匹配你要求的“同一id_对应的首行值为1，其余行值为0”规则。

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