PySpark单语句实现多表关联：匹配失败后关联第三表并合并同名字段

可以通过链式左关联结合coalesce函数实现需求，既完成"先关联df2，未匹配行再关联df3"的逻辑，又能将同名字段合并到同一列：

from pyspark.sql.functions import coalesce

result_df = df1.join(df2, df1.locationCode == df2.airportCode, "left") \
               .join(df3, (df1.locationCode == df3.cityCode) & (df2.airportCode.isNull()), "left") \
               .select(
                   df1["*"],
                   coalesce(df2.cityName, df3.cityName).alias("cityName"),
                   # 其他同名字段按此格式合并
                   # coalesce(df2.other_field, df3.other_field).alias("other_field")
               )

逻辑说明 #

• 第一次左关联df2：保留df1所有行，匹配成功的行携带df2的字段，未匹配行的df2字段（如airportCode、cityName）会为null
• 第二次左关联df3：仅针对df2未匹配的行（通过df2.airportCode.isNull()筛选），用df1.locationCode匹配df3.cityCode
• 合并同名字段：coalesce函数会优先取df2的非空值，若df2字段为空（即未匹配到df2）则取df3的对应值，实现字段合并

优化方案（字段多的场景） #

如果df2和df3有大量同名字段，提前给字段加别名能避免冲突，更易维护：

from pyspark.sql.functions import coalesce

# 给df2、df3的字段加表别名前缀
df2_renamed = df2.selectExpr("airportCode", "cityName as cityName_df2")
df3_renamed = df3.selectExpr("cityCode", "cityName as cityName_df3")

result_df = df1.join(df2_renamed, df1.locationCode == df2_renamed.airportCode, "left") \
               .join(df3_renamed, (df1.locationCode == df3_renamed.cityCode) & (df2_renamed.airportCode.isNull()), "left") \
               .select(
                   df1["*"],
                   coalesce(df2_renamed.cityName_df2, df3_renamed.cityName_df3).alias("cityName")
               )

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