SQLite中LEFT JOIN结果行数超源表的原因排查问询

问题核心 #

你需要基于repository_exporter和t两表关联创建analysis表，要求最终记录数与t表一致，但执行左连接后记录数远超t表——核心原因是repository_exporter表中存在重复的projectUniqueName值，导致t表单条记录匹配多条repo表记录，从而生成重复数据。

从数据量对比看：t表有1373748条，analysis表却有1796887条，说明部分Project在repository_exporter中对应多条记录，左连接时会将这些匹配结果全部保留，导致总条数膨胀。

解决方案 #

方案1：先清理repository_exporter的重复数据 #

如果业务逻辑允许projectUniqueName唯一，先对repo表去重：

-- 创建临时表存储去重后的repo数据（保留每个projectUniqueName的一条记录）
CREATE TABLE repo_unique AS
SELECT *
FROM repository_exporter
GROUP BY projectUniqueName;

-- 替换原表（操作前建议备份数据）
DROP TABLE repository_exporter;
ALTER TABLE repo_unique RENAME TO repository_exporter;

之后再执行你原来的创建表语句，就能保证记录数与t表一致。

方案2：关联时直接控制只取单条匹配记录 #

如果不能修改原表，可通过以下方式确保每个t.Project只匹配一条repo记录：

方法A：用DISTINCT去重（适用于重复记录完全一致的场景）

CREATE TABLE 'analysis' AS 
SELECT DISTINCT
  r.fullPath, 
  r.httpUrlToRepo, 
  r.ownershipLevel2, 
  r.projectUniqueName, 
  t.Project, t.Language, t.File, t.nByte, t.nBlank, 
  t.nComment, t.nCode, t.nComplexity 
FROM t 
LEFT JOIN repository_exporter r
  ON t.Project = r.projectUniqueName;

方法B：用窗口函数指定取某一条记录（适用于同一Project对应不同repo记录的场景）

比如按id排序取每组第一条：

CREATE TABLE 'analysis' AS 
SELECT
  fullPath, 
  httpUrlToRepo, 
  ownershipLevel2, 
  projectUniqueName, 
  Project, Language, File, nByte, nBlank, 
  nComment, nCode, nComplexity 
FROM (
  SELECT
    r.fullPath, 
    r.httpUrlToRepo, 
    r.ownershipLevel2, 
    r.projectUniqueName, 
    t.Project, t.Language, t.File, t.nByte, t.nBlank, 
    t.nComment, t.nCode, t.nComplexity,
    -- 按Project分组，给每个匹配的repo记录编号
    ROW_NUMBER() OVER (PARTITION BY t.Project ORDER BY r.id) AS rn
  FROM t 
  LEFT JOIN repository_exporter r
    ON t.Project = r.projectUniqueName
) AS sub_query
-- 只保留每组的第一条记录
WHERE rn = 1;

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