GROUP BY查询无法按日期筛选计数的技术求助

需求 #

针对Table1中的每一行，统计Table2中Name相同且Date早于该行Date的记录数量，期望输出如下。

Table1 #

Table2 #

期望输出 #

原SQL问题 #

当前编写的SQL仅能按Name分组统计Table2中对应Name的总记录数，无法针对Table1每行的Date筛选出更早的记录：

SELECT t1.uri,
       t1.date,
       t1.name,
       t2.cou
FROM       table1 t1
INNER JOIN (select name, 
                   count(name) AS cou
            FROM table2 t2
            GROUP BY name) t2 
        ON t1.name = t2.name
ORDER BY t1.uri

修改方案 #

方案一：关联子查询（直观简洁） #

SELECT 
    t1.uri,
    t1.name,
    (SELECT COUNT(*) 
     FROM table2 t2 
     WHERE t2.name = t1.name 
       AND t2.date < t1.date) AS Count
FROM table1 t1
ORDER BY t1.uri;

对Table1的每一行，通过子查询直接统计Table2中满足Name匹配且Date更早的记录数，逻辑清晰，适合小数据量场景。

方案二：LEFT JOIN + GROUP BY（性能更优） #

SELECT 
    t1.uri,
    t1.name,
    COUNT(t2.uri) AS Count
FROM table1 t1
LEFT JOIN table2 t2 
    ON t1.name = t2.name 
    AND t2.date < t1.date
GROUP BY t1.uri, t1.name
ORDER BY t1.uri;

通过LEFT JOIN同时匹配Name和Date条件，确保无匹配时Count显示0；再按Table1的URI和Name分组统计匹配记录数，适合大数据量场景，性能更稳定。

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