Left Join返回行数多于左表问题求助（附可复现示例）

核心问题原因 #

• 代码中的DISTCINT是拼写错误，正确的SQL去重关键字是DISTINCT。拼写错误导致数据库无法识别去重逻辑，LEFT JOIN后生成的多行冗余记录没有被合并，最终结果行数超过左表fans的行数。
• 以你的测试数据为例：fan_id=3在merch_with_current_season表中有2条匹配记录，LEFT JOIN后会生成2行；因为DISTCINT无效，这2行会被完整保留，最终结果行数变成4，而左表只有3行。

修正方案 #

方案1：修正拼写错误 #

把DISTCINT改为DISTINCT，让数据库执行去重逻辑，确保每个fan_id只保留一行：

WITH merch_seasons AS
(
    SELECT DISTINCT fan_id,
        (SELECT transaction_season
         FROM merch_with_current_season
         WHERE merchandise_fan_id_purchaser = fan_id
           AND transaction_season IS NOT NULL
         ORDER BY transaction_season
         LIMIT 1) AS merch_first_season,
        (SELECT transaction_season
         FROM merch_with_current_season
         WHERE merchandise_fan_id_purchaser = fan_id
           AND transaction_season IS NOT NULL
         ORDER BY transaction_season DESC
         LIMIT 1) AS merch_last_season
    FROM
        fans 
    LEFT JOIN
        merch_with_current_season ON fan_id = merchandise_fan_id_purchaser
)

方案2：优化查询逻辑（更高效） #

因为你已经通过相关子查询获取每个fan_id的首/末赛季，完全不需要LEFT JOIN——直接从fans表查询即可，避免JOIN产生的冗余行，效率更高且行数绝对和左表一致：

WITH merch_seasons AS
(
    SELECT fan_id,
        (SELECT transaction_season
         FROM merch_with_current_season
         WHERE merchandise_fan_id_purchaser = fan_id
           AND transaction_season IS NOT NULL
         ORDER BY transaction_season
         LIMIT 1) AS merch_first_season,
        (SELECT transaction_season
         FROM merch_with_current_season
         WHERE merchandise_fan_id_purchaser = fan_id
           AND transaction_season IS NOT NULL
         ORDER BY transaction_season DESC
         LIMIT 1) AS merch_last_season
    FROM fans
)

验证测试 #

用你提供的测试数据运行优化后的查询，会得到3行结果，和fans表行数完全一致：

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