如何在单条SQL中两次使用GROUP BY？及特定分组统计需求咨询

没问题，咱们可以通过嵌套分组来解决这个需求——先把每个员工在对应课程和完成日期的平均分算出来并筛选合格的，再基于这个结果统计每个课程+日期组合下的员工总数。其实不是不能用两次GROUP BY，而是要把它们放在不同的层级里（比如子查询或CTE），而非在同一条SELECT语句里直接叠加。

解决方案思路 #

1. 内层分组：先按「员工ID+课程ID+完成日期」分组，计算每个员工在对应课程下的测试平均分，筛选出平均分高于50的记录；
2. 外层分组：基于内层的合格员工记录，再按「课程ID+完成日期」分组，统计每个组合下的员工数量。

具体实现代码 #

方案1：使用子查询

SELECT 
    CourseId, 
    DateOfCourseCompletion, 
    COUNT(DISTINCT EmployeeId) AS QualifiedEmployeeCount
FROM (
    -- 内层：筛选出所有课程测试平均分高于50的员工-课程-日期记录
    SELECT 
        ctbe.CourseId, 
        ctbe.DateOfCourseCompletion, 
        ctbe.EmployeeId
    FROM tblTestsTakenByEmployee ttbe 
    INNER JOIN tblCoursesTakenByEmployee ctbe 
        ON ttbe.EmployeeId = ctbe.EmployeeId  -- 改用ON替代USING，避免列名前缀混淆
    WHERE ctbe.HasCompletedCourse = 'Y'
    GROUP BY ctbe.CourseId, ctbe.DateOfCourseCompletion, ctbe.EmployeeId
    HAVING AVG(ttbe.GivenMark) > 50
) AS QualifiedEmployees
GROUP BY CourseId, DateOfCourseCompletion
ORDER BY CourseId, DateOfCourseCompletion;

方案2：使用CTE（可读性更好）

如果你的数据库支持CTE（比如MySQL 8+、PostgreSQL、SQL Server等），可以用这种更清晰的写法：

WITH QualifiedEmployees AS (
    SELECT 
        ctbe.CourseId, 
        ctbe.DateOfCourseCompletion, 
        ctbe.EmployeeId
    FROM tblTestsTakenByEmployee ttbe 
    INNER JOIN tblCoursesTakenByEmployee ctbe 
        ON ttbe.EmployeeId = ctbe.EmployeeId
    WHERE ctbe.HasCompletedCourse = 'Y'
    GROUP BY ctbe.CourseId, ctbe.DateOfCourseCompletion, ctbe.EmployeeId
    HAVING AVG(ttbe.GivenMark) > 50
)
SELECT 
    CourseId, 
    DateOfCourseCompletion, 
    COUNT(DISTINCT EmployeeId) AS QualifiedEmployeeCount
FROM QualifiedEmployees
GROUP BY CourseId, DateOfCourseCompletion
ORDER BY CourseId, DateOfCourseCompletion;

补充说明 #

• 内层子查询/CTE已经确保每个员工在同一个「课程+日期」组合下只有一条记录，所以外层用COUNT(*)也能得到正确结果，但COUNT(DISTINCT EmployeeId)更稳妥，能避免潜在的重复数据问题；
• 你原来的查询中USING (ctbe.EmployeeId)写法有误，USING的括号里只需写列名（不需要表前缀），改成ON的写法更直观，不容易出错。

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