SQL Server：实现每5条记录分组生成top与bottom类型结果的查询需求

原始数据表 #

no  name
---------
41  name1
41  name2
41  name3
41  name4
41  name5
41  name6
41  name7
41  name8
41  name9
41  name10

需求 #

需要编写SQL语句，将数据按每5条分为一组，每组分别生成top和bottom两种REC_TYPE的记录，最终输出结果如下：

no  name    REC_TYPE
--------------------
41  name1   top
41  name2   top
41  name3   top
41  name4   top
41  name5   top
41  name1   bottom
41  name2   bottom
41  name3   bottom
41  name4   bottom
41  name5   bottom
41  name6   top
41  name7   top
41  name8   top
41  name9   top
41  name10  top
41  name6   bottom
41  name7   bottom
41  name8   bottom
41  name9   bottom
41  name10  bottom

已尝试的无效语句 #

之前的语句仅能重复所有记录，无法实现按每5条分组的需求：

SELECT 'top' AS REC_TYPE, * 
FROM table

UNION

SELECT 'bottom', * 
FROM table 

正确解决方案 #

适用于支持窗口函数的数据库（MySQL 8+、PostgreSQL、SQL Server等） #

WITH grouped_data AS (
    SELECT 
        no,
        name,
        -- 按name排序后，每5条划分为一个分组
        FLOOR((ROW_NUMBER() OVER (ORDER BY name) - 1) / 5) AS group_id
    FROM your_table_name
),
rec_types AS (
    SELECT 'top' AS REC_TYPE UNION ALL SELECT 'bottom' AS REC_TYPE
)
SELECT 
    gd.no,
    gd.name,
    rt.REC_TYPE
FROM grouped_data gd
CROSS JOIN rec_types rt
ORDER BY gd.group_id, rt.REC_TYPE, gd.name;

适用于MySQL 5.x（不支持CTE的版本） #

SELECT 
    gd.no,
    gd.name,
    rt.REC_TYPE
FROM (
    SELECT 
        no,
        name,
        FLOOR((@row_num := @row_num + 1) - 1) / 5 AS group_id
    FROM your_table_name, (SELECT @row_num := 0) rn
    ORDER BY name
) gd
CROSS JOIN (
    SELECT 'top' AS REC_TYPE UNION ALL SELECT 'bottom' AS REC_TYPE
) rt
ORDER BY gd.group_id, rt.REC_TYPE, gd.name;

逻辑说明 #

1. 先给每条记录分配行号，通过行号计算分组ID，实现每5条一组的划分；
2. 创建包含top和bottom的类型列表，通过交叉连接让每组内的每条记录都对应两种类型；
3. 最后按分组ID、类型、记录名称排序，得到符合要求的输出。

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