如何编写SQL正确统计指定时间范围各项目不同类型文档数量

当前SQL存在3类核心问题，导致结果不符合预期：

• 语法错误：documentA_total字段行末尾缺失逗号，且引用的项目表名project与实际表结构projecttable不一致，会直接触发执行报错。
• WHERE条件破坏LEFT JOIN逻辑：时间过滤条件写在WHERE子句中时，LEFT JOIN生成的无关联文档/关联文档不在时间范围的记录（对应字段为NULL）会被条件直接过滤，导致无符合文档的项目（如id=1的项目）完全丢失，无法返回计数为0的结果。
• 多表JOIN产生笛卡尔积：直接同时左联两张文档表，会导致同一项目下的A、B类文档记录两两相乘，即使使用COUNT(DISTINCT)能部分抵消计数错误，也会带来不必要的性能损耗，数据量较大时问题会更明显。

采用先聚合统计再关联项目表的写法，从根源避免上述问题，修正后的SQL如下：

SELECT
    p.id,
    COALESCE(a.documentA_total, 0) AS documentA_total,
    COALESCE(b.documentB_total, 0) AS documentB_total
FROM projecttable p
-- 先统计时间范围内各项目的A类文档数量
LEFT JOIN (
    SELECT
        projectId,
        COUNT(id) AS documentA_total
    FROM documenta
    WHERE createdAt BETWEEN '{{daterange.start}}' AND '{{daterange.end}}'
    GROUP BY projectId
) a ON a.projectId = p.id
-- 再统计时间范围内各项目的B类文档数量
LEFT JOIN (
    SELECT
        projectId,
        COUNT(id) AS documentB_total
    FROM documentb
    WHERE createdAt BETWEEN '{{daterange.start}}' AND '{{daterange.end}}'
    GROUP BY projectId
) b ON b.projectId = p.id
;

传入时间范围2022-01-01到2022-03-31 23:59:59执行上述SQL，返回结果完全符合预期：

• 项目1：A类0、B类0
• 项目2：A类1、B类0
• 项目3：A类2、B类1
• 项目4：A类0、B类1

注：使用BETWEEN做时间范围过滤时，建议结束时间传入范围最后一天的23:59:59，避免漏掉结束日期当天的非零点数据。

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