如何将表A的每一行与表B中对应的两行数据进行关联查询

你可以通过以下两种常用方式实现需求，两种方式均可输出你要的合并后结果：

方式1：双JOIN关联（通用适配所有SQL引擎，无需预处理数据） #

直接将表B拆分为主队、客队两个数据集，分别和表A关联即可，参考SQL如下：

SELECT
  a.date,
  a.league_name,
  a.league_id,
  a.match,
  -- 主队字段（status=1）
  b1.team_name,
  b1.goal_for AS g_for,
  b1.goal_against AS g_against,
  b1.team_id,
  b1.status,
  -- 客队字段（status=2）
  b2.team_name,
  b2.goal_for,
  b2.goal_against,
  b2.team_id,
  b2.status
FROM 表A a
-- 关联主队数据
LEFT JOIN 表B b1 
  ON a.match LIKE CONCAT('%', b1.team_name, '%') AND b1.status = 1
-- 关联客队数据
LEFT JOIN 表B b2 
  ON a.match LIKE CONCAT('%', b2.team_name, '%') AND b2.status = 2

注意：如果你的球队名称存在包含关系（比如同时存在team1和team11），字符串匹配可能出现误差，建议给表A新增home_team、away_team两个单独字段存储主客队名称，后续直接用等值关联，准确性和性能都会大幅提升。

方式2：行转列聚合关联（性能更优，适合有公共关联键的场景） #

如果表B也存储了和表A一致的league_id作为比赛关联维度，可以先把表B的两行数据聚合为一行，再和表A关联，查询效率更高：

WITH b_merged AS (
  SELECT
    league_id,
    -- 聚合主队信息
    MAX(CASE WHEN status = 1 THEN team_name END) AS home_team_name,
    MAX(CASE WHEN status = 1 THEN goal_for END) AS home_g_for,
    MAX(CASE WHEN status = 1 THEN goal_against END) AS home_g_against,
    MAX(CASE WHEN status = 1 THEN team_id END) AS home_team_id,
    MAX(CASE WHEN status = 1 THEN status END) AS home_status,
    -- 聚合客队信息
    MAX(CASE WHEN status = 2 THEN team_name END) AS away_team_name,
    MAX(CASE WHEN status = 2 THEN goal_for END) AS away_goal_for,
    MAX(CASE WHEN status = 2 THEN goal_against END) AS away_goal_against,
    MAX(CASE WHEN status = 2 THEN team_id END) AS away_team_id,
    MAX(CASE WHEN status = 2 THEN status END) AS away_status
  FROM 表B
  GROUP BY league_id
)
SELECT a.*, b.* FROM 表A a
JOIN b_merged b ON a.league_id = b.league_id

提示：你示例中的期望结果存在重复字段名，实际执行时建议给主客队的同名字段增加前缀区分，避免数据库返回字段冲突报错。

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