如何在SQL中统计IMDB电影数据库的Top 10独立电影类型？

要解决这个问题，核心是把genres字段中用|分隔的多值拆分成独立行，再对单个类型分组统计。不同SQL数据库的实现方法略有不同，以下是几种常用方案：

MySQL 实现方案 #

使用递归CTE（公共表表达式）逐步拆分每个电影的类型字段：

WITH RECURSIVE genre_split AS (
    SELECT
        movie_title,
        SUBSTRING_INDEX(genres, '|', 1) AS genre,
        SUBSTRING(genres, LENGTH(SUBSTRING_INDEX(genres, '|', 1)) + 2) AS remaining_genres
    FROM imdb_movies_column_drop
    WHERE genres IS NOT NULL AND genres != ''
    UNION ALL
    SELECT
        movie_title,
        SUBSTRING_INDEX(remaining_genres, '|', 1) AS genre,
        SUBSTRING(remaining_genres, LENGTH(SUBSTRING_INDEX(remaining_genres, '|', 1)) + 2) AS remaining_genres
    FROM genre_split
    WHERE remaining_genres IS NOT NULL AND remaining_genres != ''
)
SELECT
    genre,
    COUNT(DISTINCT movie_title) AS Number_of_Movie
FROM genre_split
GROUP BY genre
ORDER BY Number_of_Movie DESC
LIMIT 10;

• 递归CTE会循环拆分genres字段，直到剩余的类型字符串为空
• COUNT(DISTINCT movie_title)确保同一部电影不会在多个类型统计中重复计数

PostgreSQL 实现方案 #

利用内置的string_to_array和unnest函数快速拆分：

SELECT
    unnest(string_to_array(genres, '|')) AS genre,
    COUNT(DISTINCT movie_title) AS Number_of_Movie
FROM imdb_movies_column_drop
WHERE genres IS NOT NULL AND genres != ''
GROUP BY genre
ORDER BY Number_of_Movie DESC
LIMIT 10;

• string_to_array将分隔的类型字符串转为数组，unnest把数组展开为独立行

SQL Server 实现方案 #

使用STRING_SPLIT函数（适用于2016及以上版本）：

SELECT
    value AS genre,
    COUNT(DISTINCT movie_title) AS Number_of_Movie
FROM imdb_movies_column_drop
CROSS APPLY STRING_SPLIT(genres, '|')
WHERE genres IS NOT NULL AND genres != ''
GROUP BY value
ORDER BY Number_of_Movie DESC
LIMIT 10;

• CROSS APPLY会为每一行电影生成拆分后的类型行，再进行分组统计

原语句问题说明 #

你之前的SQL语句是按完整的组合类型字符串分组，所以得到的是Action|Adventure这类组合的电影数量，而非单个类型的独立统计结果。上述方案通过拆分字段，实现了对单个类型的计数。

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