如何按指定列分页SQL结果并实现电影流派分组查询

现有movies表结构如下：

ID | Name | Genre
1  | A | fantasy
2  | A | medieval
3  | B | sci-fi
4  | C | comedy
5  | C | sci-fi
6  | C | romance
7  | D | horror

原分页查询语句为：

SELECT * FROM movies ORDER BY id OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY;

需求调整为：跳过第一个电影（A），获取接下来2个电影（B、C）的所有流派行（共4行数据），而非仅2行记录。

附加需求：

• 无法拆分表（若允许修改结构会将电影与流派分表，通过分页电影表关联获取流派）
• 希望SQL分组后生成嵌套结构（如{"name": "A", "genres":["fantasy","medieval"]}），无需使用SUM/MIN/MAX等聚合函数
• 当前使用MSSQL，方案最好兼容其他关系型数据库

解决方案1：获取指定电影的所有流派行 #

通用兼容写法（支持MSSQL、PostgreSQL、MySQL 8.0+、Oracle 12c+） #

先通过子查询分页获取目标电影名称，再关联原表提取对应所有流派行：

SELECT m.*
FROM movies m
INNER JOIN (
    -- 分页筛选唯一电影：跳过1个，取2个
    SELECT DISTINCT Name
    FROM movies
    ORDER BY ID
    OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY
) t ON m.Name = t.Name
ORDER BY m.ID;

注：若使用MySQL 5.x版本，可将子查询中的OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY替换为LIMIT 1,2。

MSSQL专属优化写法 #

利用窗口函数DENSE_RANK()给每个电影分配排名，直接筛选目标分组：

SELECT ID, Name, Genre
FROM (
    SELECT 
        *,
        -- 按电影最小ID排序，给每个电影分配唯一排名（同电影排名一致）
        DENSE_RANK() OVER(ORDER BY MIN(ID) OVER(PARTITION BY Name)) AS movie_rank
    FROM movies
) t
WHERE movie_rank BETWEEN 2 AND 3 -- 跳过第1个电影，取第2、3个
ORDER BY ID;

解决方案2：生成嵌套JSON结构 #

MSSQL实现（SQL Server 2016+） #

使用FOR JSON PATH直接生成符合要求的嵌套结构，无需传统聚合函数：

SELECT 
    DISTINCT Name AS [name],
    -- 子查询生成流派数组，AS [*]去掉键名直接保留值
    (SELECT Genre AS [*] FROM movies m2 WHERE m2.Name = m1.Name FOR JSON PATH) AS [genres]
FROM (
    SELECT DISTINCT Name
    FROM movies
    ORDER BY ID
    OFFSET 1 ROWS FETCH NEXT 2 ROWS ONLY
) m1
FOR JSON PATH;

执行后输出：

[{"name":"B","genres":["sci-fi"]},{"name":"C","genres":["comedy","sci-fi","romance"]}]

多数据库兼容写法（以PostgreSQL为例） #

使用原生JSON函数实现嵌套结构，避免SUM/MIN/MAX类聚合：

SELECT 
    json_build_object(
        'name', Name,
        'genres', json_agg(Genre)
    )
FROM movies
WHERE Name IN (
    SELECT DISTINCT Name
    FROM movies
    ORDER BY ID
    OFFSET 1 LIMIT 2
)
GROUP BY Name
ORDER BY MIN(ID);

执行后输出：

{"name": "B", "genres": ["sci-fi"]}
{"name": "C", "genres": ["comedy", "sci-fi", "romance"]}

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