多表INNER JOIN关联后记录重复问题排查求助

算力阿峰

2026-7-9

问题描述

需要通过2个INNER JOIN连接Match_Next、Match_Results和MyTest三张表，目标是获取away_club对应的score_home列表，但添加第二个JOIN后score_home出现大量重复，使用DISTINCT会丢失有效重复数据（比如原本的(0,0,1,2)变成(0,1,2)）。

最初仅关联Match_Next与Match_Results时结果正常，SQL语句：

x = cur.execute('''SELECT Match_Next.home_club||" VS "||Match_Next.away_club,
                          Match_Next.turn, Match_Results.score_home 
                   FROM Match_Next
                   INNER JOIN Match_Results ON Match_Next.home_club = Match_Results.home_club''')

添加第二个JOIN关联MyTest后出现重复，SQL语句：

x = cur.execute('''SELECT Match_Next.home_club||" VS "||Match_Next.away_club,
                          Match_Next.turn, Match_Results.score_home,
                          MyTest.A, MyTest.B
                   FROM Match_Next
                   INNER JOIN Match_Results ON Match_Next.home_club = Match_Results.home_club
                   INNER JOIN MyTest ON Match_Next.home_club = MyTest.home_club''')

数据库表结构及示例数据

Match_Next表

CREATE TABLE "Match_Next" (
"Id" INTEGER,
"turn" INTEGER,
"home_club" INTEGER,
"away_club" INTEGER,
"score_home" NUMERIC,
"score_away" NUMERIC,
PRIMARY KEY("Id" AUTOINCREMENT)
);

INSERT INTO Match_Next
  (`turn`, `home_club`, `away_club`, `score_home`, `score_away`)
VALUES
  ('8', 'Chicago', 'Lakers', '?', '?'),
  ('8', 'Minnesota', 'Dallas', '?', '?');

Match_Results表

CREATE TABLE "Match_Results" (
    "Id" INTEGER,
    "turn" INTEGER,
    "home_club" INTEGER,
    "away_club" INTEGER,
    "score_home" NUMERIC,
    "score_away" NUMERIC,
    PRIMARY KEY("Id" AUTOINCREMENT)
    );

INSERT INTO Match_Results
  (`turn`, `home_club`, `away_club`, `score_home`, `score_away`)
VALUES
  ('7', 'Chicago', 'New York', '2', '1'),
  ('7', 'Dallas', 'Indiana', '1', '0'),
  ('6', 'Milwaukee', 'Dallas', '0', '2'),
  ('6', 'Detroit', 'Chicago', '3', '2'),
  ('5', 'Chicago', 'Brooklyn', '1', '4'),
  ('5', 'Dallas', 'Memphis Gr', '2','3'),
  ('4', 'Chicago', 'Oklahoma', '0', '2'),
  ('4', 'Golden State', 'Dallas', '3', '1'),
  ('3', 'Dallas', 'Phoenix', '3', '0'),
  ('3', 'San Antonio', 'Chicago', '4', '1'),
  ('2', 'Chicago', 'Detroit', '0', '2'),
  ('2', 'Toronto', 'Dallas', '1', '3'),
  ('1', 'Dallas', 'Boston', '2', '1'),
  ('1', 'Indiana', 'Chicago', '2', '0')

MyTest表

CREATE TABLE "MyTest" (
"Id" INTEGER,
"turn" INTEGER,
"home_club" INTEGER,
"away_club" INTEGER,
"score_home" NUMERIC,
"score_away" NUMERIC,
"A" NUMERIC,
"B" NUMERIC,
PRIMARY KEY("Id" AUTOINCREMENT)
);

INSERT INTO MyTest
  (`turn`, `home_club`, `away_club`, `score_home`, `score_away`, `A`, `B`)
VALUES
  ('7', 'Chicago', 'New York', '2', '1', 'example_aaa', 'example_bbb'), 
  ('7', 'Dallas', 'Indiana', '1', '0', 'example_aaa', 'example_bbb'), 
  ('6', 'Milwaukee', 'Dallas', '0', '2', 'example_aaa', 'example_bbb'), 
  ('6', 'Detroit', 'Chicago', '3', '2', 'example_aaa', 'example_bbb'), 
  ('5', 'Chicago', 'Brooklyn', '1', '4', 'example_aaa', 'example_bbb'), 
  ('5', 'Dallas', 'Memphis Gr', '2','3', 'example_aaa', 'example_bbb'), 
  ('4', 'Chicago', 'Oklahoma', '0', '2', 'example_aaa', 'example_bbb'), 
  ('4', 'Golden State', 'Dallas', '3', '1', 'example_aaa', 'example_bbb'), 
  ('3', 'Dallas', 'Phoenix', '3', '0', 'example_aaa', 'example_bbb'), 
  ('3', 'San Antonio', 'Chicago', '4', '1', 'example_aaa', 'example_bbb'), 
  ('2', 'Chicago', 'Detroit', '0', '2', 'example_aaa', 'example_bbb'), 
  ('2', 'Toronto', 'Dallas', '1', '3', 'example_aaa', 'example_bbb'), 
  ('1', 'Dallas', 'Boston', '2', '1', 'example_aaa', 'example_bbb'), 
  ('1', 'Indiana', 'Chicago', '2', '0', 'example_aaa', 'example_bbb')

解决方案

问题根源是仅用home_club关联会产生笛卡尔积——Match_Results中每个home_club的记录，都会和MyTest中同home_club的所有记录配对，导致重复。以下是几种解决方法：

方法1：使用更精确的关联条件

如果MyTest和Match_Results的记录是一一对应的（同一场比赛的记录），可以同时用home_club、away_club、turn三个字段关联，确保每条Match_Results记录只匹配到对应的MyTest记录：

x = cur.execute('''SELECT Match_Next.home_club||" VS "||Match_Next.away_club,
                          Match_Next.turn, Match_Results.score_home,
                          MyTest.A, MyTest.B
                   FROM Match_Next
                   INNER JOIN Match_Results ON Match_Next.home_club = Match_Results.home_club
                   INNER JOIN MyTest ON Match_Next.home_club = MyTest.home_club
                   AND Match_Results.turn = MyTest.turn
                   AND Match_Results.away_club = MyTest.away_club''')

方法2：用子查询获取A/B字段（避免笛卡尔积）

如果只需要每个home_club对应的A/B字段（假设每个home_club的A/B值固定），可以把MyTest的查询改成子查询，只取每个home_club的唯一A/B值：

x = cur.execute('''SELECT Match_Next.home_club||" VS "||Match_Next.away_club,
                          Match_Next.turn, Match_Results.score_home,
                          (SELECT A FROM MyTest WHERE home_club = Match_Next.home_club LIMIT 1) AS A,
                          (SELECT B FROM MyTest WHERE home_club = Match_Next.home_club LIMIT 1) AS B
                   FROM Match_Next
                   INNER JOIN Match_Results ON Match_Next.home_club = Match_Results.home_club''')

如果A/B值是按turn区分的，也可以在子查询中加上AND turn = Match_Next.turn来匹配对应轮次的值。

方法3：在代码层去重（保留原始有效重复）

如果SQL层面难以避免重复，可以在Python循环处理结果时，去除因JOIN产生的额外重复，同时保留原始的有效重复项：

result_dict = {}
# 存储每个比赛对应的已处理过的唯一记录键
processed_keys = {}

for row in cur.fetchall():
    match_id = row[0]  # 比如"Chicago VS Lakers"
    turn = row[1]
    score_home = row[2]
    a_val = row[3]
    b_val = row[4]
    
    # 用比赛ID+轮次+比分作为唯一标识，避免重复添加
    unique_key = (match_id, turn, score_home)
    
    if match_id not in result_dict:
        result_dict[match_id] = {
            "score_home_list": [],
            "A": a_val,
            "B": b_val
        }
        processed_keys[match_id] = set()
    
    if unique_key not in processed_keys[match_id]:
        result_dict[match_id]["score_home_list"].append(score_home)
        processed_keys[match_id].add(unique_key)

# 提取最终需要的score_home列表
final_result = {k: v["score_home_list"] for k, v in result_dict.items()}

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

多表INNER JOIN关联后记录重复问题排查求助

数据库表结构及示例数据 #