如何用Python递归生成包含UNION和JOIN的SQL语句

需求回顾 #

• 列数由变量v决定：总列数 = v + 1，首列固定
• UNION数量为v-1，即共v个SELECT块用UNION ALL连接
• 第k个SELECT块（从0开始计数）：
  • JOIN数量 = k + 1（等于该块中实际列的数量，首列除外）
  • NULL列数 = v - 1 - k，实际列数 = k + 1

修正后的实现代码 #

def generate_dynamic_sql(v, tables, std_cols):
    total_cols = v + 1
    select_blocks = []
    
    for k in range(v):
        # 生成SELECT子句：固定列 + 实际列 + NULL列
        select_parts = [f"{tables[0][0]}.{std_cols[0]}"]
        for col_idx in range(1, total_cols):
            if col_idx <= k + 1:
                alias = f"a{col_idx-1}"
                select_parts.append(f"{alias}.{std_cols[1]} AS col_{col_idx}")
            else:
                select_parts.append(f"NULL AS col_{col_idx}")
        
        select_clause = "select\n  " + ",\n  ".join(select_parts)
        
        # 生成FROM和JOIN子句
        from_clause = f"from {tables[0]} {tables[0][0]}"
        join_clauses = []
        for join_idx in range(k + 1):
            alias = f"a{join_idx}"
            join_clause = f"left join {tables[1]} {alias}\n  on {tables[0][0]}.{std_cols[0]} = {alias}.{std_cols[0]} and {alias}.level = {join_idx + 1}"
            join_clauses.append(join_clause)
        
        full_from_join = from_clause + "\n  " + "\n  ".join(join_clauses)
        
        # 拼接当前SELECT块
        select_block = f"{select_clause}\n{full_from_join}"
        select_blocks.append(select_block)
    
    # 用UNION ALL连接所有块
    final_sql = "\nunion all\n".join(select_blocks)
    return final_sql

# 测试示例
if __name__ == "__main__":
    # v=2场景
    v = 2
    tables = ['t1', 't2']
    std_cols = ['col_1', 'col_2']
    print("v=2时生成的SQL：")
    print(generate_dynamic_sql(v, tables, std_cols))
    
    print("\n---分割线---\n")
    
    # v=3场景
    v = 3
    print("v=3时生成的SQL：")
    print(generate_dynamic_sql(v, tables, std_cols))

代码说明 #

1. 按循环生成每个SELECT块，对应v个不同的JOIN和列组合
2. SELECT子句：首列固定，前k+1个列关联JOIN表的实际字段，剩余列用NULL填充
3. JOIN子句：每个块生成对应数量的LEFT JOIN，同表使用不同别名，level值随JOIN顺序递增
4. 最终用UNION ALL拼接所有SELECT块，得到符合需求的完整SQL

v=2测试输出 #

select
  a.col_1,
  a0.col_2 AS col_2,
  NULL AS col_3
from t1 a
  left join t2 a0
  on a.col_1 = a0.col_1 and a0.level = 1
union all
select
  a.col_1,
  a0.col_2 AS col_2,
  a1.col_2 AS col_3
from t1 a
  left join t2 a0
  on a.col_1 = a0.col_1 and a0.level = 1
  left join t2 a1
  on a.col_1 = a1.col_1 and a1.level = 2

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