如何保留Course-Participants表全部记录数同时按Course_Id统计各Status总数

你之前的写法使用GROUP BY Course_Id会将同一个课程ID的所有行合并为1条聚合结果，自然无法保留全部原始记录，也不能关联查询Course-Participants字段。要实现保留全部原始行同时附带每个Course_Id的聚合统计结果，使用窗口函数是最简洁的方案，语法兼容MySQL 8.0+、PostgreSQL、SQL Server等所有支持标准SQL窗口函数的数据库。

调整后SQL（窗口函数版） #

SELECT     
  [Course-Participants],
  [Course_Id],
  [Status],
  SUM(CASE WHEN Status = 0 THEN 1 ELSE 0 END) OVER (PARTITION BY Course_Id) AS Sum_Status_A,
  SUM(CASE WHEN Status = 1 THEN 1 ELSE 0 END) OVER (PARTITION BY Course_Id) AS Sum_Status_B,
  SUM(CASE WHEN Status = 2 THEN 1 ELSE 0 END) OVER (PARTITION BY Course_Id) AS Sum_Status_C 
FROM CourseParticipant

• 这里去掉了GROUP BY子句，改用窗口函数语法SUM(...) OVER (PARTITION BY Course_Id)，会将相同Course_Id的行归为同一个统计窗口，统计结果会自动同步到该Course_Id对应的所有原始行上
• 你原有写法里的Course_ID is not null判断可以省略，因为PARTITION BY Course_Id时NULL值会单独归为一个窗口，如果你需要过滤掉Course_Id为NULL的记录，可以额外加WHERE Course_Id IS NOT NULL子句

兼容低版本数据库的SQL写法 #

如果你使用的是不支持窗口函数的低版本数据库（比如MySQL 5.x），可以用子查询关联的方案实现：

SELECT 
  t1.[Course-Participants],
  t1.[Course_Id],
  t1.[Status],
  t2.Sum_Status_A,
  t2.Sum_Status_B,
  t2.Sum_Status_C
FROM CourseParticipant t1
LEFT JOIN (
  SELECT     
    Course_Id,
    SUM(CASE WHEN Status = 0 THEN 1 ELSE 0 END) AS Sum_Status_A,
    SUM(CASE WHEN Status = 1 THEN 1 ELSE 0 END) AS Sum_Status_B,
    SUM(CASE WHEN Status = 2 THEN 1 ELSE 0 END) AS Sum_Status_C 
  FROM CourseParticipant
  GROUP BY Course_Id
) t2 ON t1.Course_Id = t2.Course_Id

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