SQL查询实现：统计各项目每个审批日期前的累计测试数量

默认你两张表的表名分别为project_approvals（存项目审批记录）、project_tests（存项目测试记录），如果实际表名不同替换即可。

核心逻辑说明 #

因为两张表的日期字段都是MM/DD/YYYY格式的字符串，所以需要先转换为日期类型才能做大小比较，再关联两张表按项目、审批日期分组统计符合条件的测试记录数即可。

通用实现写法（关联分组方式，兼容性最好） #

以下以MySQL语法为例，不同数据库仅需调整日期转换函数：

SELECT 
    pa.PROJECT,
    pa.APPROVAL_DATE,
    COUNT(pt.TEST_DATE) AS TOTAL_TESTS_BEFORE_APPROVAL_DATE
FROM 
    project_approvals pa
LEFT JOIN 
    project_tests pt 
    ON pa.PROJECT = pt.PROJECT
    AND STR_TO_DATE(pt.TEST_DATE, '%m/%d/%Y') < STR_TO_DATE(pa.APPROVAL_DATE, '%m/%d/%Y')
GROUP BY 
    pa.PROJECT,
    pa.APPROVAL_DATE
ORDER BY 
    pa.PROJECT,
    STR_TO_DATE(pa.APPROVAL_DATE, '%m/%d/%Y');

其他写法（关联子查询，逻辑更直观） #

SELECT 
    pa.PROJECT,
    pa.APPROVAL_DATE,
    (
        SELECT COUNT(*) 
        FROM project_tests pt 
        WHERE pt.PROJECT = pa.PROJECT 
        AND STR_TO_DATE(pt.TEST_DATE, '%m/%d/%Y') < STR_TO_DATE(pa.APPROVAL_DATE, '%m/%d/%Y')
    ) AS TOTAL_TESTS_BEFORE_APPROVAL_DATE
FROM 
    project_approvals pa
ORDER BY 
    pa.PROJECT,
    STR_TO_DATE(pa.APPROVAL_DATE, '%m/%d/%Y');

不同数据库的日期转换适配 #

• Oracle/PostgreSQL：将STR_TO_DATE替换为TO_DATE，格式参数保持'MM/DD/YYYY'即可
• SQL Server：将STR_TO_DATE(字段名, '%m/%d/%Y')替换为CONVERT(DATE, 字段名, 101)

如果你的表中日期字段本身就是日期类型而非字符串，直接去掉转换函数，直接比较pt.TEST_DATE < pa.APPROVAL_DATE即可。

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