MySQL中COUNT与GROUP_CONCAT结合CASE的统计异常及筛选需求

你写的SQL里，COUNT('writtenDates')和COUNT('practicalDates')的写法是错的——COUNT()传入非空字符串时，会统计分组内所有非NULL的行，根本不是你要的符合条件的有效提交数，所以才会出现两个统计值相同、且等于学生作业总行数的异常情况。

下面是修正后的SQL，直接实现统计+筛选的需求：

SELECT 
    a.userid,
    GROUP_CONCAT(
        CASE WHEN wt.type = 'written' AND a.submissionDate IS NOT NULL AND a.submissionDate != '0000-00-00' 
             THEN a.submissionDate 
        END
    ) AS writtenDates,
    SUM(
        CASE WHEN wt.type = 'written' AND a.submissionDate IS NOT NULL AND a.submissionDate != '0000-00-00' 
             THEN 1 
             ELSE 0 
        END
    ) AS numWritten,
    GROUP_CONCAT(
        CASE WHEN wt.type = 'practical' AND a.submissionDate IS NOT NULL AND a.submissionDate != '0000-00-00' 
             THEN a.submissionDate 
        END
    ) AS practicalDates,
    SUM(
        CASE WHEN wt.type = 'practical' AND a.submissionDate IS NOT NULL AND a.submissionDate != '0000-00-00' 
             THEN 1 
             ELSE 0 
        END
    ) AS numPractical 
FROM assignments a
LEFT JOIN workTypes wt ON a.workID = wt.workID 
GROUP BY a.userid
HAVING numPractical > 0 AND numWritten = 0;

关键修改说明： #

• 用SUM(CASE...)替换COUNT()：满足有效提交条件（类型匹配+日期非空且不是'0000-00-00'）时返回1，否则返回0，求和后就是对应类型的有效提交次数，完全符合你的统计需求。
• 给表加了a（assignments）和wt（workTypes）的别名，让SQL更简洁好读。
• 直接添加HAVING子句，一步筛选出有有效实践作业、无有效书面作业的学生，无需先统计再二次筛选。

测试验证： #

用你提供的测试数据执行这个SQL，不会返回任何结果（因为两个学生都有有效书面作业）。如果新增一个userid=3的学生，提交workID=435且submissionDate='5/5/2022'，则会得到如下结果：

userid | writtenDates | numWritten | practicalDates | numPractical
-------|--------------|------------|----------------|-------------
3      | NULL         | 0          | 5/5/2022       | 1

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