如何用单个存储过程实现美国家庭数据自定义组聚合？

最简查询方案（无需嵌套视图） #

直接用CTE先统计每个家庭的成员数，再按需求分组统计频次，逻辑清晰且易调试：

WITH HouseholdSizes AS (
    SELECT 
        HOUSEHOLD_ID,
        COUNT(MEMBER_NAME) AS MemberCount
    FROM [dbo].[US_HOUSEHOLDS_TABLE]
    GROUP BY HOUSEHOLD_ID
)
SELECT
    CASE 
        WHEN MemberCount >= 6 THEN '6人及以上'
        ELSE CAST(MemberCount AS VARCHAR(10)) + '人'
    END AS FamilySize,
    COUNT(HOUSEHOLD_ID) AS Frequency
FROM HouseholdSizes
GROUP BY 
    CASE 
        WHEN MemberCount >= 6 THEN '6人及以上'
        ELSE CAST(MemberCount AS VARCHAR(10)) + '人'
    END
ORDER BY 
    CASE 
        WHEN MemberCount >= 6 THEN 6
        ELSE MemberCount
    END;

封装为存储过程 #

如果需要复用逻辑，直接把上述代码封装成存储过程即可：

CREATE PROCEDURE dbo.GetHouseholdSizeStatistics
AS
BEGIN
    SET NOCOUNT ON;

    WITH HouseholdSizes AS (
        SELECT 
            HOUSEHOLD_ID,
            COUNT(MEMBER_NAME) AS MemberCount
        FROM [dbo].[US_HOUSEHOLDS_TABLE]
        GROUP BY HOUSEHOLD_ID
    )
    SELECT
        CASE 
            WHEN MemberCount >= 6 THEN '6人及以上'
            ELSE CAST(MemberCount AS VARCHAR(10)) + '人'
        END AS FamilySize,
        COUNT(HOUSEHOLD_ID) AS Frequency
    FROM HouseholdSizes
    GROUP BY 
        CASE 
            WHEN MemberCount >= 6 THEN '6人及以上'
            ELSE CAST(MemberCount AS VARCHAR(10)) + '人'
        END
    ORDER BY 
        CASE 
            WHEN MemberCount >= 6 THEN 6
            ELSE MemberCount
        END;
END;

逻辑说明 #

1. 先通过CTEHouseholdSizes按家庭ID分组，统计每个家庭的成员总数
2. 外层用CASE语句把6人及以上的家庭统一归为一组，其他按实际人数显示
3. 最后按分组后的家庭规模统计频次，并按人数从小到大排序（6人及以上排在最后）

之前子查询出错大概率是分组逻辑不一致，比如分组字段和排序字段的CASE逻辑不匹配，这个方案把统计和分组拆分，更容易排查问题。

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