基于Classification_ID及唯一ID生成条件统计列的SQL需求

我有如下结构的数据表：

我原本用下面的SQL语句，基于Classification_ID创建Cat_1_Count和Cat_2_Count列：

SELECT ID, Location 
CASE WHEN Classification_ID IS NOT NULL THEN 1 ELSE 0 END AS Cat_1_Count 
CASE WHEN Classification_ID IS NULL THEN 1 ELSE 0 END AS Cat_2_Count 
FROM Table1

但现在需要满足额外要求：针对每个唯一的ID，无论Location取值如何，Cat_1_Count或Cat_2_Count仅在该ID对应的某一行设为1，其余行的对应列设为0，期望得到如下结果：

可以用窗口函数ROW_NUMBER()为每个ID分组后的行排序，只在每组的第一行根据Classification_ID的值设置对应计数列为1，其余行设为0。适配的SQL语句如下：

SELECT 
    ID,
    Classification_ID,
    Location,
    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Location) = 1 
             AND Classification_ID IS NOT NULL THEN 1 
        ELSE 0 
    END AS Cat_1_count,
    CASE 
        WHEN ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Location) = 1 
             AND Classification_ID IS NULL THEN 1 
        ELSE 0 
    END AS Cat_2_count
FROM Table1;

逻辑说明 #

• PARTITION BY ID：按ID分组，确保每个ID的行单独处理
• ROW_NUMBER() OVER (...) = 1：标记每个ID分组后的第一行（这里按Location排序，你也可以根据实际需求调整排序字段，比如主键或其他字段来确定哪一行显示1）
• 结合Classification_ID的非空判断，仅在每组第一行设置对应计数列为1，其余行保持0

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