如何按初始状态统计同ModelID的Trained、Enabled状态计数？

我有一张记录流程步骤的表，结构及数据如下：

我希望得到如下查询结果：

我尝试了以下SQL查询语句，但无法得到预期结果：

select sum(case when status = 'Set Local' then 1 else 0 end), sum(case when status = 'Set Remote' then 1 else 0 end), sum(case when status = 'Set Upload' then 1 else 0 end)
from TABLE
inner join (select distinct ModelId, status as ModelStatus from TABLE where (status = 'Trained' or status = 'Enabled')) as Model on Model.ModelId = TABLE.ModelId

请问如何调整才能得到需要的结果？

解决方案思路 #

你的核心需求是按每个ModelID的初始状态（以Set开头的状态），统计该ModelID是否完成了Trained和Enabled步骤。原查询的问题在于没有正确关联每个ModelID的初始状态与后续流程步骤，且聚合逻辑不符合需求。

正确的实现步骤应该是：

1. 先为每个ModelID提取它的初始Set状态；
2. 单独统计每个ModelID是否存在Trained和Enabled状态；
3. 最后按初始状态分组，聚合计算对应数量。

调整后的SQL查询 #

WITH model_initial_status AS (
    -- 提取每个ModelID的初始Set状态（假设每个ModelID仅对应一个Set开头的状态）
    SELECT ModelID, Status AS InitialStatus
    FROM your_table_name
    WHERE Status LIKE 'Set %'
),
model_step_status AS (
    -- 标记每个ModelID是否完成了Trained和Enabled步骤
    SELECT 
        ModelID,
        MAX(CASE WHEN Status = 'Trained' THEN 1 ELSE 0 END) AS HasTrained,
        MAX(CASE WHEN Status = 'Enabled' THEN 1 ELSE 0 END) AS HasEnabled
    FROM your_table_name
    WHERE Status IN ('Trained', 'Enabled')
    GROUP BY ModelID
)
-- 按初始状态分组，统计对应完成数量
SELECT
    mis.InitialStatus AS Status,
    SUM(mss.HasTrained) AS CountTrained,
    SUM(mss.HasEnabled) AS CountEnabled
FROM model_initial_status mis
LEFT JOIN model_step_status mss ON mis.ModelID = mss.ModelID
GROUP BY mis.InitialStatus
ORDER BY mis.InitialStatus;

代码解释 #

• CTE model_initial_status：筛选出所有以Set开头的状态记录，确保每个ModelID只保留它的初始启动状态；
• CTE model_step_status：对每个ModelID，用MAX(CASE...)判断是否存在Trained或Enabled状态（存在则标记为1，否则为0）；
• 主查询：将两个CTE通过ModelID关联，用LEFT JOIN保证即使某个ModelID没有后续步骤（你的测试数据里没有这种情况，但逻辑更严谨）也能被统计，最后按初始状态分组求和得到目标结果。

测试结果 #

用你的测试数据运行该查询，会得到完全符合预期的输出：

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