SQL技术问询：全连接两表后统计符合日期条件的关联行数

原表结构与数据 #

CREATE TABLE my_table1 (
    name VARCHAR(50),
    var1 DATE,
    var2 INT
);

INSERT INTO my_table1 (name, var1, var2) VALUES
('john', '2010-01-01', 94),
('john', '2010-01-04', 106),
('john', '2015-01-01', 99),
('alex', '2010-01-01', 96),
('alex', '2018-01-01', 96),
('sara', '2005-01-01', 94),
('sara', '2006-01-01', 90),
('tim',  '1999-01-01', 101);

CREATE TABLE my_table2 (
    name VARCHAR(50),
    var3 DATE,
    var4 CHAR(1)
);

INSERT INTO my_table2 (name, var3, var4) VALUES
('john', '2001-01-01', 'a'),
('john', '2002-01-01', 'b'),
('alex', '2021-01-01', 'c'),
('alex', '2022-01-01', 'd'),
('sara', '1999-01-01', 'e'),
('sara', '2023-01-01', 'f');

需求说明 #

1. 基于name字段关联my_table1和my_table2，保留my_table1的所有行，仅匹配my_table2中满足var3 > var1的行
2. 统计my_table1每一行对应的、符合条件的my_table2行数

你的SQL存在的问题 #

你的SQL无法实现需求，核心问题如下：

• 连接类型错误：使用INNER JOIN会直接丢弃my_table1中没有匹配my_table2的行（比如john、tim的所有行），但需求要求保留这些行并标记count为0
• 多余的年份匹配条件：EXTRACT(YEAR FROM t1.var1) = EXTRACT(YEAR FROM t2.var3)会过滤掉跨年份但满足var3 > var1的行，比如alex的2010-01-01本应匹配my_table2中2021、2022的两行，这个条件会把它们错误排除
• 窗口函数无法处理无匹配行：由于INNER JOIN已丢失无匹配的行，窗口函数根本无法统计这些行的count为0

正确的SQL实现 #

以下两种方法都能得到你期望的结果：

方法1：子查询直接统计 #

SELECT
    t1.name,
    t1.var1 AS date,
    t1.var2,
    (SELECT COUNT(*) 
     FROM my_table2 t2 
     WHERE t2.name = t1.name AND t2.var3 > t1.var1) AS count
FROM my_table1 t1;

方法2：LEFT JOIN + 窗口函数 #

SELECT
    DISTINCT
    t1.name,
    t1.var1 AS date,
    t1.var2,
    COUNT(t2.name) OVER (PARTITION BY t1.name, t1.var1) AS count
FROM my_table1 t1
LEFT JOIN my_table2 t2 
    ON t1.name = t2.name AND t2.var3 > t1.var1;

执行后会得到你期望的结果集：

name       date var2 count
 alex 2010-01-01   96     2
 alex 2018-01-01   96     2
 sara 2005-01-01   94     1
 sara 2006-01-01   90     1
 john 2010-01-01   94     0
 john 2010-01-04  106     0
 john 2015-01-01   99     0
  tim 1999-01-01  101     0

总结 #

原SQL因连接类型错误和多余过滤条件无法满足需求，改用LEFT JOIN（或子查询）并去掉不必要的年份匹配条件，才能正确统计每个my_table1行对应的符合条件的my_table2行数。

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