SQL34 每份试卷每月作答数和截止当月的作答总数。

题目主要信息：

• 输出每份试卷每月作答数和截止当月的作答总数。
• 只输出作答数不为 0 的月份

问题拆解：

总体思路

1. 统计共存在多少不同的 exam_id、month，记作表 A
2. 将表 A 按照 exam_id LEFT JOIN 原始表，筛选出 A.month 大于等于原始表 month 的数据
3. 此时统计全量数据，即可得到截至当月的作答总数。统计 A.month = 原始表 month 的数据可以得到当月作答数

实际操作

• 通过 SELECT DISTINCT 语句获取所有不同的 exam_id、month。month 的获取需要使用 DATE_FORMAT 方法，这里只需要获取其年份和月份。知识点：SELECT DISTINCT, DATE_FORMAT
• 通过 GROUP BY 汇总统计每个 exam_id、month 的填答数量，最后通过 ORDER BY 按照 exam_id 和 month 排序输出。知识点：GROUP BY, ORDER BY
• 分别对 “当月数据” 和 “截至当月数据” 做统计。“截至当月” 只需要简单 COUNT(1) 即可。“当月数据” 需要通过 SUM(IF(a.month=b.month, 1, 0)) 来进行筛选统计。知识点：SUM, COUNT, IF

代码：

SELECT
    record_a.exam_id,
    record_a.month,
    SUM(IF(record_a.month=DATE_FORMAT(record_b.start_time,'%Y%m'), 1, 0)),
    COUNT(1)
FROM
(
    SELECT DISTINCT
        exam_id,
        DATE_FORMAT(start_time,'%Y%m') AS month
    FROM
        exam_record
) AS record_a
LEFT JOIN
    exam_record AS record_b
ON
    record_a.exam_id = record_b.exam_id
WHERE
    record_a.month >= DATE_FORMAT(record_b.start_time,'%Y%m')
GROUP BY
    record_a.exam_id,
    record_a.month
ORDER BY
    record_a.exam_id,
    record_a.month