合并日期ID表：按ID统计Table1日期早于Table2对应最早日期的数量

这个需求的核心是要确保Table1里的每个日期只被统计到最早的、比它晚的Table2日期中，不能重复计算。下面给你两种可行的SQL方案，适用于大多数支持标准SQL的数据库（比如PostgreSQL、BigQuery、SQL Server 2012+、MySQL 8.0+等）：

方法一：先映射每个Table1日期到对应最早的Table2日期 #

这种方法逻辑直观，先给每个Table1的日期找到同一ID下最早的、比它大的Table2日期，再统计每个Table2日期对应的数量：

WITH mapped_dates AS (
    SELECT
        t1.ID,
        t1.Date1,
        -- 找到同一ID下，比当前Date1大的最小Date2
        MIN(t2.Date2) AS matched_date2
    FROM Table1 t1
    LEFT JOIN Table2 t2 
        ON t1.ID = t2.ID 
        AND t2.Date2 > t1.Date1
    GROUP BY t1.ID, t1.Date1
)
-- 统计每个Table2日期对应的Table1日期数量
SELECT
    t2.Date2,
    t2.ID,
    COALESCE(COUNT(md.Date1), 0) AS Count
FROM Table2 t2
LEFT JOIN mapped_dates md
    ON t2.ID = md.ID 
    AND t2.Date2 = md.matched_date2
GROUP BY t2.ID, t2.Date2
ORDER BY t2.ID, t2.Date2;

逻辑解释： #

1. 公共表表达式mapped_dates：把Table1的每条记录和同一ID下第一个比它晚的Table2日期绑定。如果Table1的日期比该ID下所有Table2日期都晚，matched_date2会是NULL，这类记录不会被计入任何Table2的统计。
2. 主查询：将Table2与mapped_dates关联，统计每个Table2日期对应的绑定记录数，用COALESCE把NULL转为0，避免出现空值统计结果。

方法二：用窗口函数划分日期区间（更高效） #

这种方法通过窗口函数给Table2的日期排序并划分区间，直接统计每个区间内的Table1日期数量，适合数据量较大的场景：

WITH ordered_table2 AS (
    SELECT
        ID,
        Date2,
        -- 获取同一ID下，当前Date2的上一个更早的Date2
        LAG(Date2) OVER (PARTITION BY ID ORDER BY Date2) AS prev_date2
    FROM Table2
)
SELECT
    ot2.Date2,
    ot2.ID,
    COUNT(t1.Date1) AS Count
FROM ordered_table2 ot2
LEFT JOIN Table1 t1
    ON t1.ID = ot2.ID
    -- 统计Table1日期在「上一个Table2日期之后，当前Table2日期之前/当天」的记录
    AND t1.Date1 > COALESCE(ot2.prev_date2, '1900-01-01')
    AND t1.Date1 <= ot2.Date2
GROUP BY ot2.ID, ot2.Date2
ORDER BY ot2.ID, ot2.Date2;

逻辑解释： #

1. 公共表表达式ordered_table2：给每个ID的Table2日期按时间排序，并用LAG函数获取当前日期的前一个日期（最早的日期的prev_date2为NULL）。
2. 主查询：关联Table1，统计落在(prev_date2, Date2]区间内的Table1日期数量。对于最早的Table2日期，我们用COALESCE把prev_date2替换为一个极早的日期，确保所有比它早的Table1日期都被统计进来。

示例输出（基于你提供的测试数据） #

两种方法都会得到以下结果：

结果说明： #

• ID=1的2016-04-10统计了Table1中2016-02-12（这是最早比它晚的Table2日期）；
• ID=1的2018-01-10统计了Table1中2017-01-10；
• ID=2的2017-01-01比Table1的2017-12-12早，所以没有符合条件的记录，Count为0。

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