SQL Server 2016如何用COUNT(DISTINCT) OVER(PARTITION BY)统计学生及科目数量

解决方案 #

你可以通过窗口函数快速实现需求，兼容大部分主流数据库（MySQL 8.0+、SQL Server 2012+、PostgreSQL等），修改后的语句如下：

SELECT DISTINCT
    class_no,
    subject_name,
    COUNT(*) OVER (PARTITION BY class_no, subject_name) AS total_subjects,
    COUNT(DISTINCT student_id) OVER (PARTITION BY class_no) AS total_students
FROM #T_Test
WHERE class_no = 1

如果你的数据库版本不支持窗口函数的DISTINCT计算，也可以用标量子查询的方式实现，兼容性更好：

SELECT  
    t.class_no,
    t.subject_name,
    COUNT(*) AS total_subjects,
    (SELECT COUNT(DISTINCT student_id) FROM #T_Test WHERE class_no = t.class_no) AS total_students
FROM  #T_Test t
WHERE t.class_no = 1
GROUP BY t.class_no, t.subject_name

说明 #

1. COUNT(*) OVER (PARTITION BY class_no, subject_name) 按班级+科目分组，统计每个科目的出现次数
2. COUNT(DISTINCT student_id) OVER (PARTITION BY class_no) 按班级分组，统计当前班级去重后的学生总数
3. 你给出的期望输出最后一行Physics记录属于笔误，正确统计结果共4行，其中Physics的total_subjects为2，其余3个科目均为1，所有行的total_students均为4。

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