如何用pandas+sqlite3通过循环实现迭代SQL JOIN？

现有两张SQLite表结构及数据如下：

TableA #

TableB #

需要生成目标表TableC：

当前尝试循环创建中间表再JOIN的方法，不仅生成大量中间表、扩展性差，还出现duplicate column level 0错误，需要更简洁高效的实现方案。

方案1：纯SQL条件聚合（推荐，无中间表） #

SQLite无原生PIVOT函数，但可通过条件聚合实现行转列，一次性查询出结果，无需创建任何中间表：

SELECT 
    b.join_id,
    b."other value" AS other_value,
    MAX(CASE WHEN a.distance = 50 THEN a.value END) AS dist_50,
    MAX(CASE WHEN a.distance = 100 THEN a.value END) AS dist_100,
    MAX(CASE WHEN a.distance = 150 THEN a.value END) AS dist_150
FROM TableB b
LEFT JOIN TableA a ON b.join_id = a.join_id
GROUP BY b.join_id, b."other value";

如果distance值是动态的，可通过Python自动生成SQL语句，适配任意数量的distance值：

distances = [50, 100, 150]
# 生成动态的CASE子句
case_clauses = ",\n    ".join([f"MAX(CASE WHEN a.distance = {d} THEN a.value END) AS dist_{d}" for d in distances])

# 拼接完整SQL
sql_query = f"""
SELECT 
    b.join_id,
    b."other value" AS other_value,
    {case_clauses}
FROM TableB b
LEFT JOIN TableA a ON b.join_id = a.join_id
GROUP BY b.join_id, b."other value";
"""

# 执行查询得到目标表
tableC_df = pd.read_sql(sql_query, mydb)

方案2：Pandas直接行转列（无需SQL操作） #

既然已将数据导入Pandas DataFrame，可直接用pivot功能完成转换，再与TableB合并：

# 读取两张表数据
tableA_df = pd.read_sql("SELECT * FROM TableA", mydb)
tableB_df = pd.read_sql("SELECT * FROM TableB", mydb)

# 对TableA行转列
pivot_a = tableA_df.pivot(index='join_id', columns='distance', values='value')
# 重命名列名
pivot_a.columns = [f"dist_{col}" for col in pivot_a.columns]
# 重置索引以便合并
pivot_a = pivot_a.reset_index()

# 与TableB合并得到目标表
tableC_df = pd.merge(tableB_df, pivot_a, on='join_id', how='left')
# 重命名列（匹配目标表格式）
tableC_df = tableC_df.rename(columns={"other value": "other_value"})

这种方法全程在Pandas中处理，无需操作SQLite中间表，代码简洁且扩展性强。

关于之前的错误原因 #

你循环JOIN时出现duplicate column level 0错误，主要有两个原因：

1. 循环中反复将tableB_df写入SQLite再读取，导致DataFrame列名重复（如join_id多次出现）；
2. JOIN条件中误用了result_id，但你的表实际关联字段是join_id。

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