SQLite中类似Inner Join的多列匹配查询实现问题

步骤1：编写精准关联的SQL语句 #

针对你的需求，我们通过UNION合并球队作为主队、客队的得分记录，再与NEXT表关联，确保匹配不同列并提取对应得分，适配SQLite的语句如下：

基础版（提取单条得分记录） #

SELECT 
    n.HomeTeam AS Team,
    CASE
        WHEN r.HomeTeam = n.HomeTeam THEN r.ScoreHome
        WHEN r.AwayTeam = n.HomeTeam THEN r.ScoreAway
    END AS TeamScore
FROM NEXT n
INNER JOIN RESULTS r ON n.HomeTeam = r.HomeTeam OR n.HomeTeam = r.AwayTeam
UNION ALL
SELECT 
    n.AwayTeam AS Team,
    CASE
        WHEN r.HomeTeam = n.AwayTeam THEN r.ScoreHome
        WHEN r.AwayTeam = n.AwayTeam THEN r.ScoreAway
    END AS TeamScore
FROM NEXT n
INNER JOIN RESULTS r ON n.AwayTeam = r.HomeTeam OR n.AwayTeam = r.AwayTeam
ORDER BY Team;

聚合版（直接生成球队得分元组格式） #

如果需要直接得到类似(1,4,1,1)的聚合结果，可使用SQLite的GROUP_CONCAT函数：

SELECT 
    Team,
    GROUP_CONCAT(TeamScore, ',') AS ScoreList
FROM (
    SELECT 
        n.HomeTeam AS Team,
        CASE
            WHEN r.HomeTeam = n.HomeTeam THEN r.ScoreHome
            WHEN r.AwayTeam = n.HomeTeam THEN r.ScoreAway
        END AS TeamScore
    FROM NEXT n
    INNER JOIN RESULTS r ON n.HomeTeam = r.HomeTeam OR n.HomeTeam = r.AwayTeam
    UNION ALL
    SELECT 
        n.AwayTeam AS Team,
        CASE
            WHEN r.HomeTeam = n.AwayTeam THEN r.ScoreHome
            WHEN r.AwayTeam = n.AwayTeam THEN r.ScoreAway
        END AS TeamScore
    FROM NEXT n
    INNER JOIN RESULTS r ON n.AwayTeam = r.HomeTeam OR n.AwayTeam = r.AwayTeam
) AS CombinedScores
GROUP BY Team;

SQL逻辑说明

• 两个SELECT块分别处理NEXT表中的主队、客队，通过CASE语句判断球队在RESULTS中的主客身份，提取对应得分
• UNION ALL保留所有得分记录，避免自动去重丢失数据
• 聚合版通过子查询合并数据后，用GROUP_CONCAT将得分拼接为字符串，方便后续转换为元组

步骤2：结合fetchall()循环的Python实现 #

以下是完整的Python代码，严格保留你要求的循环逻辑：

import sqlite3

# 连接SQLite数据库
conn = sqlite3.connect('your_db_name.db')
my = conn.cursor()

# 执行聚合版SQL语句
my.execute("""
SELECT 
    Team,
    GROUP_CONCAT(TeamScore, ',') AS ScoreList
FROM (
    SELECT 
        n.HomeTeam AS Team,
        CASE
            WHEN r.HomeTeam = n.HomeTeam THEN r.ScoreHome
            WHEN r.AwayTeam = n.HomeTeam THEN r.ScoreAway
        END AS TeamScore
    FROM NEXT n
    INNER JOIN RESULTS r ON n.HomeTeam = r.HomeTeam OR n.HomeTeam = r.AwayTeam
    UNION ALL
    SELECT 
        n.AwayTeam AS Team,
        CASE
            WHEN r.HomeTeam = n.AwayTeam THEN r.ScoreHome
            WHEN r.AwayTeam = n.AwayTeam THEN r.ScoreAway
        END AS TeamScore
    FROM NEXT n
    INNER JOIN RESULTS r ON n.AwayTeam = r.HomeTeam OR n.AwayTeam = r.AwayTeam
) AS CombinedScores
GROUP BY Team;
""")

# 遍历结果并格式化输出
for row in my.fetchall():
    team = row[0]
    # 将拼接的字符串转为整数元组
    score_tuple = tuple(map(int, row[1].split(','))) if row[1] else ()
    print(f"{team} 过往得分记录：{score_tuple}")

# 关闭数据库连接
conn.close()

示例输出 #

芝加哥 过往得分记录：(1, 4, 1, 1)
纽约 过往得分记录：(3, 2, 5)
达拉斯 过往得分记录：(0, 4, 2)

关键注意事项 #

• 确保NEXT和RESULTS表中的球队名称完全一致（如“芝加哥”不能写成“芝加哥公牛”），否则关联会失效
• 若需保留NEXT中无过往得分的球队，将INNER JOIN替换为LEFT JOIN，Python中需处理空值场景
• 如需针对单场赛事分别提取主客队得分，可在SQL中加入NEXT表的赛事标识（如MatchDate）进行分组

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