无区域表时如何将州按普查局标准区域分组统计员工ID数

直接在SQL中通过CASE语句将各州映射为标准普查局区域，再按区域分组统计，无需导出到Excel处理：

SELECT 
    COUNT(DISTINCT Empl_ID) AS Employee_Count,
    CASE STATE
        -- 东北部区域
        WHEN 'ME' THEN 'Northeast'
        WHEN 'NH' THEN 'Northeast'
        WHEN 'VT' THEN 'Northeast'
        WHEN 'MA' THEN 'Northeast'
        WHEN 'RI' THEN 'Northeast'
        WHEN 'CT' THEN 'Northeast'
        WHEN 'NY' THEN 'Northeast'
        WHEN 'NJ' THEN 'Northeast'
        WHEN 'PA' THEN 'Northeast'
        -- 南部区域
        WHEN 'DE' THEN 'South'
        WHEN 'MD' THEN 'South'
        WHEN 'DC' THEN 'South'
        WHEN 'VA' THEN 'South'
        WHEN 'WV' THEN 'South'
        WHEN 'NC' THEN 'South'
        WHEN 'SC' THEN 'South'
        WHEN 'GA' THEN 'South'
        WHEN 'FL' THEN 'South'
        WHEN 'KY' THEN 'South'
        WHEN 'TN' THEN 'South'
        WHEN 'AL' THEN 'South'
        WHEN 'MS' THEN 'South'
        WHEN 'AR' THEN 'South'
        WHEN 'LA' THEN 'South'
        WHEN 'OK' THEN 'South'
        WHEN 'TX' THEN 'South'
        -- 中西部区域
        WHEN 'OH' THEN 'Midwest'
        WHEN 'IN' THEN 'Midwest'
        WHEN 'IL' THEN 'Midwest'
        WHEN 'MI' THEN 'Midwest'
        WHEN 'WI' THEN 'Midwest'
        WHEN 'MN' THEN 'Midwest'
        WHEN 'IA' THEN 'Midwest'
        WHEN 'MO' THEN 'Midwest'
        WHEN 'ND' THEN 'Midwest'
        WHEN 'SD' THEN 'Midwest'
        WHEN 'NE' THEN 'Midwest'
        WHEN 'KS' THEN 'Midwest'
        -- 西部区域
        WHEN 'MT' THEN 'West'
        WHEN 'ID' THEN 'West'
        WHEN 'WY' THEN 'West'
        WHEN 'CO' THEN 'West'
        WHEN 'NM' THEN 'West'
        WHEN 'AZ' THEN 'West'
        WHEN 'UT' THEN 'West'
        WHEN 'NV' THEN 'West'
        WHEN 'WA' THEN 'West'
        WHEN 'OR' THEN 'West'
        WHEN 'CA' THEN 'West'
        WHEN 'AK' THEN 'West'
        WHEN 'HI' THEN 'West'
        -- 处理未匹配的州/地区
        ELSE 'Other'
    END AS Census_Region
FROM Employee_Table
GROUP BY Census_Region
ORDER BY Employee_Count DESC;

说明： #

• 上述CASE语句完全按照美国人口普查局的四大区域划分标准编写，覆盖了所有美国州、华盛顿特区，未匹配的地区会归到Other
• 直接在数据库层面完成分组统计，彻底规避大数据集导出到Excel的性能瓶颈
• 最终通过ORDER BY按员工数量降序排列，便于快速查看区域数据差异

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