如何使用SQL拆分每行数据中存储的多个genre值

以下是不同主流数据库的可行实现，默认你的genre字段为固定分隔符（如逗号、竖线）拼接的字符串，可根据实际分隔符替换代码中的对应符号：

• MySQL 8.0及以上版本 #

  可以用递归CTE实现拆分，示例表为movies，包含字段movie_id、title、genres：

  WITH RECURSIVE split_genres AS (
      SELECT 
          movie_id,
          title,
          SUBSTRING_INDEX(genres, ',', 1) AS genre,
          SUBSTRING(genres, LENGTH(SUBSTRING_INDEX(genres, ',', 1)) + 2) AS remaining_genres
      FROM movies
      WHERE genres IS NOT NULL AND genres != ''
      UNION ALL
      SELECT 
          movie_id,
          title,
          SUBSTRING_INDEX(remaining_genres, ',', 1),
          SUBSTRING(remaining_genres, LENGTH(SUBSTRING_INDEX(remaining_genres, ',', 1)) + 2)
      FROM split_genres
      WHERE remaining_genres != ''
  )
  SELECT movie_id, title, genre FROM split_genres;
  

  如果是MySQL 8.0.19及更高版本，还可以用JSON_TABLE简化写法：

  SELECT m.movie_id, m.title, j.genre
  FROM movies m
  JOIN JSON_TABLE(
      CONCAT('["', REPLACE(m.genres, ',', '","'), '"]'),
      '$[*]' COLUMNS (genre VARCHAR(255) PATH '$')
  ) j
  WHERE m.genres IS NOT NULL;
  

• PostgreSQL 版本 #

  直接使用内置的unnest+string_to_array函数即可，写法最简洁：

  SELECT 
      movie_id,
      title,
      unnest(string_to_array(genres, ',')) AS genre
  FROM movies
  WHERE genres IS NOT NULL AND genres != '';
  

• SQL Server 2016及以上版本 #

  使用内置STRING_SPLIT函数配合交叉应用实现：

  SELECT 
      m.movie_id,
      m.title,
      s.value AS genre
  FROM movies m
  CROSS APPLY STRING_SPLIT(m.genres, ',') s
  WHERE m.genres IS NOT NULL;
  

• 低版本数据库通用兼容方案 #

  如果你的数据库版本较旧，没有上述内置函数，可以预先创建一张存储连续整数的辅助表numbers（数值范围覆盖单条记录最多的genre数量即可），通过关联实现拆分：

  SELECT 
      m.movie_id,
      m.title,
      SUBSTRING_INDEX(SUBSTRING_INDEX(m.genres, ',', n.num), ',', -1) AS genre
  FROM movies m
  INNER JOIN numbers n 
  ON n.num <= LENGTH(m.genres) - LENGTH(REPLACE(m.genres, ',', '')) + 1
  WHERE m.genres IS NOT NULL;
  

注意：如果你的genre字段使用的是逗号之外的其他分隔符（如竖线、空格等），把上述代码中的逗号替换为实际分隔符即可。

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