如何用窗口函数/透视法聚合同列值到不同列？表层级状态统计咨询

这题我太熟悉了！要把同一列的不同状态值拆分成单独列做聚合统计，用条件聚合（也就是透视的经典实现方式）或者窗口函数配合分组都能完美解决，针对你的表层级结构，我给你两种实用方案：

这种方式是SQL里实现列转行统计的标准玩法，用CASE WHEN配合聚合函数，把每个状态映射成单独的统计列，逻辑清晰性能也不错。

SELECT
    gp.GrandParentFooId,
    COUNT(p.ParentFooId) AS TotalParentFoo,
    -- 统计Pending状态的Parent数量（Status=1）
    SUM(CASE WHEN p.Status = 1 THEN 1 ELSE 0 END) AS PendingParentCount,
    -- 统计Active状态的Parent数量（Status=2）
    SUM(CASE WHEN p.Status = 2 THEN 1 ELSE 0 END) AS ActiveParentCount,
    -- 统计Paused状态的Parent数量（Status=3）
    SUM(CASE WHEN p.Status = 3 THEN 1 ELSE 0 END) AS PausedParentCount,
    -- 统计Complete状态的Parent数量（Status=4）
    SUM(CASE WHEN p.Status = 4 THEN 1 ELSE 0 END) AS CompleteParentCount
FROM GrandParentFoo gp
-- 用LEFT JOIN保证即使GrandParent没有关联的Parent也能返回统计值（全0）
LEFT JOIN ParentFoo p ON gp.GrandParentFooId = p.GrandParentFooId
-- 替换成你需要指定的GrandParentFooId
WHERE gp.GrandParentFooId = @TargetGrandParentId
GROUP BY gp.GrandParentFooId;

小说明： #

• 用LEFT JOIN而不是INNER JOIN，是为了处理目标GrandParent没有任何关联Parent的情况，这时候所有统计列都会返回0
• 如果你的Status字段是字符串类型（比如直接存'Pending'），把CASE WHEN里的数字换成对应字符串就行，比如CASE WHEN p.Status = 'Pending' THEN 1 ELSE 0 END

如果你需要同时获取Parent的明细数据和汇总统计，窗口函数会更灵活。先通过窗口函数计算每个GrandParent分组的统计值，再去重得到汇总行：

WITH ParentWithSummary AS (
    SELECT
        p.ParentFooId,
        p.GrandParentFooId,
        p.Status,
        -- 窗口函数统计当前GrandParent下的Parent总数
        COUNT(p.ParentFooId) OVER (PARTITION BY p.GrandParentFooId) AS TotalParentFoo,
        -- 窗口函数统计各状态的Parent数量
        SUM(CASE WHEN p.Status = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY p.GrandParentFooId) AS PendingParentCount,
        SUM(CASE WHEN p.Status = 2 THEN 1 ELSE 0 END) OVER (PARTITION BY p.GrandParentFooId) AS ActiveParentCount,
        SUM(CASE WHEN p.Status = 3 THEN 1 ELSE 0 END) OVER (PARTITION BY p.GrandParentFooId) AS PausedParentCount,
        SUM(CASE WHEN p.Status = 4 THEN 1 ELSE 0 END) OVER (PARTITION BY p.GrandParentFooId) AS CompleteParentCount
    FROM ParentFoo p
    WHERE p.GrandParentFooId = @TargetGrandParentId
)
-- 去重得到唯一的汇总行
SELECT DISTINCT
    GrandParentFooId,
    TotalParentFoo,
    PendingParentCount,
    ActiveParentCount,
    PausedParentCount,
    CompleteParentCount
FROM ParentWithSummary
-- 补全没有任何Parent的GrandParent的统计（返回全0）
UNION ALL
SELECT
    @TargetGrandParentId,
    0,
    0,
    0,
    0,
    0
WHERE NOT EXISTS (SELECT 1 FROM ParentFoo WHERE GrandParentFooId = @TargetGrandParentId);

小说明： #

• 窗口函数的PARTITION BY p.GrandParentFooId表示按GrandParent分组计算统计值，每个Parent行都会带上所属GrandParent的汇总数据
• 最后用UNION ALL补全无Parent的情况，确保无论有没有关联数据都能返回结果

额外扩展： #

如果之后需要同时统计ChildFoo的状态，只需要在查询里关联ChildFoo表，然后用同样的SUM(CASE...)逻辑新增统计列就行，比如：

SUM(CASE WHEN c.Status = 1 THEN 1 ELSE 0 END) AS PendingChildCount

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