基于球员姓名集合查询共同所属联赛的PostgreSQL SQL优化需求

问题背景 #

现有两张数据库表：

• LEAGUE_TEAM：存储联赛ID、名称
• PLAYER_DETAILS：记录联赛ID、球员姓氏（last_name）、名字（first_name），一名球员可参与多个联赛

需求：输入JSON格式的球员姓名集合（每个元素包含last_name和first_name），找出所有球员共同参与的联赛。之前分步查询（查单个球员联赛再比对）的方式太繁琐，需要适配PostgreSQL 16的高效SQL语句。

示例场景 #

• 输入Thiago Silva、Willy Caballero → 返回联赛1、2、3
• 输入Frank Lampard、Willy Caballero → 返回联赛1、2
• 输入Mason Mount、Willy Caballero → 返回联赛4
• 输入Thiago Silva、Frank Lampard、Willy Caballero → 返回联赛1、2

解决方案 #

核心SQL语句 #

利用PostgreSQL的JSONB处理能力，直接解析输入的球员列表，通过分组统计筛选出所有球员共同参与的联赛：

WITH input_players AS (
  SELECT 
    (player->>'last_name')::TEXT AS last_name,
    (player->>'first_name')::TEXT AS first_name
  FROM 
    jsonb_to_recordset('[{"last_name": "Silva", "first_name": "Thiago"}, {"last_name": "Caballero", "first_name": "Willy"}]'::JSONB)
    AS player(last_name TEXT, first_name TEXT)
),
player_leagues AS (
  SELECT 
    pd.league_id,
    COUNT(DISTINCT pd.last_name || '|' || pd.first_name) AS player_count
  FROM 
    PLAYER_DETAILS pd
  JOIN 
    input_players ip ON pd.last_name = ip.last_name AND pd.first_name = ip.first_name
  GROUP BY 
    pd.league_id
),
total_players AS (
  SELECT COUNT(*) AS count FROM input_players
)
SELECT 
  lt.league_id,
  lt.name
FROM 
  player_leagues pl
JOIN 
  total_players tp ON pl.player_count = tp.count
JOIN 
  LEAGUE_TEAM lt ON pl.league_id = lt.league_id
ORDER BY 
  lt.league_id;

逻辑说明 #

1. 解析输入JSON：用jsonb_to_recordset把输入的JSON球员列表转成关系型临时表，方便后续关联查询
2. 统计联赛关联的球员数：关联PLAYER_DETAILS和临时表，按联赛ID分组，统计每个联赛下匹配到的不同球员数量
3. 筛选共同联赛：将每个联赛的球员数和输入的总球员数对比，数量相等则说明所有球员都参与了该联赛
4. 关联联赛表：最后和LEAGUE_TEAM关联，获取联赛名称

Java调用示例 #

在Java 11中可以通过PreparedStatement传入JSON参数，避免SQL注入：

String sql = """
WITH input_players AS (
  SELECT 
    (player->>'last_name')::TEXT AS last_name,
    (player->>'first_name')::TEXT AS first_name
  FROM 
    jsonb_to_recordset(?::JSONB)
    AS player(last_name TEXT, first_name TEXT)
),
player_leagues AS (
  SELECT 
    pd.league_id,
    COUNT(DISTINCT pd.last_name || '|' || pd.first_name) AS player_count
  FROM 
    PLAYER_DETAILS pd
  JOIN 
    input_players ip ON pd.last_name = ip.last_name AND pd.first_name = ip.first_name
  GROUP BY 
    pd.league_id
),
total_players AS (
  SELECT COUNT(*) AS count FROM input_players
)
SELECT 
  lt.league_id,
  lt.name
FROM 
  player_leagues pl
JOIN 
  total_players tp ON pl.player_count = tp.count
JOIN 
  LEAGUE_TEAM lt ON pl.league_id = lt.league_id
ORDER BY 
  lt.league_id;
""";

// 构造输入的JSON字符串（示例）
String playersJson = "[{\"last_name\": \"Silva\", \"first_name\": \"Thiago\"}, {\"last_name\": \"Caballero\", \"first_name\": \"Willy\"}]";

try (PreparedStatement pstmt = conn.prepareStatement(sql)) {
  pstmt.setString(1, playersJson);
  ResultSet rs = pstmt.executeQuery();
  // 遍历结果集处理数据
  while (rs.next()) {
    int leagueId = rs.getInt("league_id");
    String leagueName = rs.getString("name");
    // 业务逻辑处理
  }
} catch (SQLException e) {
  e.printStackTrace();
}

性能优化建议 #

• 在PLAYER_DETAILS表上创建复合索引：CREATE INDEX idx_player_details_name_league ON PLAYER_DETAILS(last_name, first_name, league_id);，能大幅提升JOIN和分组操作的效率
• 如果存在姓名大小写不一致的情况，可改用LOWER(pd.last_name) = LOWER(ip.last_name)进行匹配，或者提前统一存储和输入的大小写格式

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