Oracle中如何在数据组间动态插入空行？每5行后添加空行

没问题！要实现每5行后插入空行的需求，咱们可以借助窗口函数给每行分配行号，再通过UNION ALL把空行插入到指定位置，最后调整排序逻辑来达到效果。

首先给原查询加上行号标记，再生成对应位置的空行，最后合并排序。这里用CTE（公共表表达式）拆分逻辑，可读性更强：

WITH ranked_data AS (
    SELECT 
        php.ref_dcp_key, 
        SUM(php.group_booking) AS sum_group_booking,
        COUNT(php.group_booking) AS count_group_booking,
        0 AS placeholder,
        ROW_NUMBER() OVER (ORDER BY php.ref_dcp_key) AS row_num
    FROM gx_pnr_history ph
    JOIN gx_pnr_his_prof php ON ph.gmpnr_loc_key = php.gmpnr_loc_key
    GROUP BY php.ref_dcp_key
),
empty_rows AS (
    SELECT 
        NULL AS ref_dcp_key,
        NULL AS sum_group_booking,
        NULL AS count_group_booking,
        0 AS placeholder,
        row_num AS row_num
    FROM ranked_data
    WHERE row_num % 5 = 0
)
SELECT ref_dcp_key, sum_group_booking, count_group_booking, placeholder
FROM (
    SELECT ref_dcp_key, sum_group_booking, count_group_booking, placeholder, row_num, 1 AS sort_order
    FROM ranked_data
    UNION ALL
    SELECT ref_dcp_key, sum_group_booking, count_group_booking, placeholder, row_num + 0.5 AS row_num, 2 AS sort_order
    FROM empty_rows
) combined
ORDER BY row_num, sort_order;

逻辑拆解 #

• ranked_data：给原查询结果每行分配连续的row_num，同时给聚合字段加了清晰别名（原查询的聚合字段无别名，建议加上方便识别）。另外把原查询的逗号连接改成JOIN，这是更规范的SQL写法。
• empty_rows：筛选出行号为5的倍数的行，生成对应空行（所有字段设为NULL）。
• 合并排序：把原数据和空行合并，给原数据标记sort_order=1、空行标记sort_order=2，并将空行的行号设为原行号+0.5，确保空行刚好出现在第5、10、15...行的后面。

兼容老版本数据库（无CTE支持） #

如果你的数据库不支持CTE（比如老版本MySQL），可以用子查询改写，核心逻辑完全一致：

SELECT ref_dcp_key, sum_group_booking, count_group_booking, placeholder
FROM (
    SELECT 
        php.ref_dcp_key, 
        SUM(php.group_booking) AS sum_group_booking,
        COUNT(php.group_booking) AS count_group_booking,
        0 AS placeholder,
        ROW_NUMBER() OVER (ORDER BY php.ref_dcp_key) AS row_num,
        1 AS sort_order
    FROM gx_pnr_history ph
    JOIN gx_pnr_his_prof php ON ph.gmpnr_loc_key = php.gmpnr_loc_key
    GROUP BY php.ref_dcp_key
    UNION ALL
    SELECT 
        NULL, NULL, NULL, 0,
        row_num + 0.5, 2
    FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY php.ref_dcp_key) AS row_num
        FROM gx_pnr_history ph
        JOIN gx_pnr_his_prof php ON ph.gmpnr_loc_key = php.gmpnr_loc_key
        GROUP BY php.ref_dcp_key
    ) temp
    WHERE row_num % 5 = 0
) combined
ORDER BY row_num, sort_order;

可选调整 #

如果不想在最后一行（刚好是5的倍数时）添加空行，可以给empty_rows的WHERE条件加个限制：

WHERE row_num % 5 = 0 AND row_num < (SELECT COUNT(*) FROM ranked_data)

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