MySQL单SQL实现学生借书统计及近7天每日借书量查询问题

需求1：查询所有学生的借书总量（未借书学生返回0） #

你之前使用LEFT JOIN仍过滤了无借书记录的学生，大概率是将BookAssignment表的筛选条件写在了WHERE子句中，或是统计计数时用了错误的统计字段。正确写法如下：

SELECT 
    s.StudentID,
    s.StudentName,
    COUNT(ba.AssignID) AS BookCount
FROM Student s
LEFT JOIN BookAssignment ba 
    ON s.StudentID = ba.StudentID
-- 如果有对借书记录的筛选条件，请写在ON子句中，不要写在WHERE里
GROUP BY s.StudentID, s.StudentName;

逻辑说明：LEFT JOIN会完整保留Student表的所有记录，无借书记录的学生对应的BookAssignment表字段全部为NULL，COUNT(ba.AssignID)会自动忽略NULL值，最终无借书记录的学生统计结果自然为0，不会漏统计。 #

需求2：查询过去连续7天每天的借书总量（当日无借书返回0） #

该需求的核心是先生成过去7天的连续日期序列，再左关联借书记录表统计，避免遗漏无借书记录的日期。

MySQL 8.0+ 版本（支持递归CTE）写法： #

WITH RECURSIVE date_series AS (
    -- 生成从今天往前7天的连续日期
    SELECT CURDATE() AS record_date
    UNION ALL
    SELECT DATE_SUB(record_date, INTERVAL 1 DAY)
    FROM date_series
    WHERE record_date > DATE_SUB(CURDATE(), INTERVAL 6 DAY)
)
SELECT 
    ds.record_date AS `统计日期`,
    COUNT(ba.AssignID) AS `当日借书总量`
FROM date_series ds
LEFT JOIN BookAssignment ba 
    ON DATE(ba.DateTime) = ds.record_date
GROUP BY ds.record_date
ORDER BY ds.record_date;

MySQL 5.x 兼容版本写法： #

SELECT 
    ds.record_date AS `统计日期`,
    COUNT(ba.AssignID) AS `当日借书总量`
FROM (
    -- 手动构造过去7天的日期序列
    SELECT DATE_SUB(CURDATE(), INTERVAL 0 DAY) AS record_date UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 1 DAY) AS record_date UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 2 DAY) AS record_date UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 3 DAY) AS record_date UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 4 DAY) AS record_date UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 5 DAY) AS record_date UNION ALL
    SELECT DATE_SUB(CURDATE(), INTERVAL 6 DAY) AS record_date
) ds
LEFT JOIN BookAssignment ba 
    ON DATE(ba.DateTime) = ds.record_date
GROUP BY ds.record_date
ORDER BY ds.record_date;

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