求助：如何用Pivot/Case语句实现公民测试结果的合并统计

Got it, let's break down how to build this query to get the exact stats you need—combining NULL and "Has not Taken" into one category, plus counting each group and their percentages. Both CASE statements and PIVOT work here; I'll walk you through both approaches.

方法1：使用CASE语句（直观易读） #

This is the most straightforward approach, especially if you want full control over each calculation. We'll use CASE to group results, then count each category and compute percentages relative to the total number of students per school.

SELECT
    schools,
    -- 统计"未参加"（NULL + Has not Taken）的人数
    COUNT(CASE WHEN test_result IS NULL OR test_result = 'Has not Taken' THEN 1 END) AS not_taken_count,
    -- 统计"通过"人数
    COUNT(CASE WHEN test_result = 'Passed' THEN 1 END) AS passed_count,
    -- 统计"未通过"人数
    COUNT(CASE WHEN test_result = 'Failed' THEN 1 END) AS failed_count,
    -- 统计"特殊教育豁免"人数
    COUNT(CASE WHEN test_result = 'Sped-Exempted' THEN 1 END) AS sped_exempted_count,
    -- 计算该校总人数
    COUNT(*) AS total_students,
    -- 计算各类占比（保留2位小数）
    ROUND(COUNT(CASE WHEN test_result IS NULL OR test_result = 'Has not Taken' THEN 1 END) * 100.0 / NULLIF(COUNT(*), 0), 2) AS not_taken_percent,
    ROUND(COUNT(CASE WHEN test_result = 'Passed' THEN 1 END) * 100.0 / NULLIF(COUNT(*), 0), 2) AS passed_percent,
    ROUND(COUNT(CASE WHEN test_result = 'Failed' THEN 1 END) * 100.0 / NULLIF(COUNT(*), 0), 2) AS failed_percent,
    ROUND(COUNT(CASE WHEN test_result = 'Sped-Exempted' THEN 1 END) * 100.0 / NULLIF(COUNT(*), 0), 2) AS sped_exempted_percent
FROM
    your_student_test_table -- 替换成你的实际表名
GROUP BY
    schools
ORDER BY
    schools;

代码说明： #

• CASE 语句会判断每条记录的test_result属于哪一类，COUNT只统计符合条件的行（不符合的会被忽略，返回0）。
• NULLIF(COUNT(*), 0) 用来避免除数为0的错误（如果某个学校没有学生，占比会显示NULL而不是报错）。
• ROUND(..., 2) 把百分比保留两位小数，让结果更整洁。

方法2：使用PIVOT（行转列更简洁） #

If you prefer a more compact structure for turning result categories into columns, PIVOT is a great option. First, we'll standardize the results with a CTE, then pivot to count each group.

-- 第一步：标准化测试结果，把NULL和"Has not Taken"合并为"Not Taken"
WITH standardized_test_results AS (
    SELECT
        schools,
        CASE
            WHEN test_result IS NULL OR test_result = 'Has not Taken' THEN 'Not Taken'
            ELSE test_result
        END AS result_category
    FROM
        your_student_test_table -- 替换成你的实际表名
)
-- 第二步：用PIVOT转列并计算统计值
SELECT
    schools,
    [Not Taken] AS not_taken_count,
    [Passed] AS passed_count,
    [Failed] AS failed_count,
    [Sped-Exempted] AS sped_exempted_count,
    -- 计算总人数
    [Not Taken] + [Passed] + [Failed] + [Sped-Exempted] AS total_students,
    -- 计算占比
    ROUND([Not Taken] * 100.0 / NULLIF([Not Taken] + [Passed] + [Failed] + [Sped-Exempted], 0), 2) AS not_taken_percent,
    ROUND([Passed] * 100.0 / NULLIF([Not Taken] + [Passed] + [Failed] + [Sped-Exempted], 0), 2) AS passed_percent,
    ROUND([Failed] * 100.0 / NULLIF([Not Taken] + [Passed] + [Failed] + [Sped-Exempted], 0), 2) AS failed_percent,
    ROUND([Sped-Exempted] * 100.0 / NULLIF([Not Taken] + [Passed] + [Failed] + [Sped-Exempted], 0), 2) AS sped_exempted_percent
FROM
    standardized_test_results
PIVOT (
    COUNT(result_category)
    FOR result_category IN ([Not Taken], [Passed], [Failed], [Sped-Exempted])
) AS test_result_pivot
ORDER BY
    schools;

代码说明： #

• The CTE (standardized_test_results) cleans up the test results first, ensuring NULLs and "Has not Taken" are grouped under a single label.
• PIVOT takes the distinct result_category values and turns them into columns, counting how many times each appears per school.
• Just like the CASE method, we use NULLIF to avoid division by zero errors.

额外提示 #

• 记得把your_student_test_table替换成你的实际表名。
• 如果只需要统计高年级学生，可在GROUP BY前（CASE方法）或CTE内部（PIVOT方法）添加WHERE筛选条件，比如WHERE grade_level >= 10（根据你的年级定义调整）。

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