如何实现SQL中基于动态列的重复行ROW_NUMBER编号？

这个问题我之前也碰到过——静态SQL里没办法直接把动态查询到的列名列表放到PARTITION BY子句里，必须通过动态SQL先构建完整的查询语句，再执行它。下面分常见数据库给你具体的实现方案：

方案核心思路 #

1. 从information_schema.columns中获取目标表TEST的列名，拼接成逗号分隔的字符串；
2. 将这个字符串嵌入到包含ROW_NUMBER()函数的查询语句中；
3. 执行动态生成的SQL语句。

MySQL 实现 #

-- 1. 获取并拼接带反引号的列名（避免列名含特殊字符/关键字）
SELECT GROUP_CONCAT(CONCAT('`', COLUMN_NAME, '`') SEPARATOR ', ') INTO @partition_cols
FROM information_schema.columns 
WHERE table_name = 'TEST' 
  AND table_schema = DATABASE(); -- 限定当前数据库，避免同名表干扰

-- 2. 构建动态SQL语句
SET @sql = CONCAT(
    'SELECT *, ROW_NUMBER() OVER(PARTITION BY ', @partition_cols, ' ORDER BY (SELECT 0)) AS rn FROM TEST'
);

-- 3. 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

SQL Server 实现（2017+版本） #

DECLARE @partition_cols NVARCHAR(MAX);
DECLARE @sql NVARCHAR(MAX);

-- 1. 获取并拼接带方括号的列名
SELECT @partition_cols = STRING_AGG(QUOTENAME(COLUMN_NAME), ', ')
FROM information_schema.columns 
WHERE table_name = 'TEST' 
  AND table_schema = SCHEMA_NAME();

-- 2. 构建动态SQL语句
SET @sql = N'SELECT *, ROW_NUMBER() OVER(PARTITION BY ' + @partition_cols + ' ORDER BY (SELECT 0)) AS rn FROM TEST';

-- 3. 执行动态SQL
EXEC sp_executesql @sql;

PostgreSQL 实现 #

DO $$
DECLARE
    partition_cols TEXT;
    sql TEXT;
BEGIN
    -- 1. 获取并拼接带双引号的列名
    SELECT string_agg(quote_ident(COLUMN_NAME), ', ') INTO partition_cols
    FROM information_schema.columns 
    WHERE table_name = 'TEST' 
      AND table_schema = current_schema();

    -- 2. 构建动态SQL语句
    sql := 'SELECT *, ROW_NUMBER() OVER(PARTITION BY ' || partition_cols || ' ORDER BY (SELECT 0)) AS rn FROM TEST';

    -- 3. 执行动态SQL
    EXECUTE sql;
END $$;

关键注意事项 #

• 一定要加上table_schema（或对应数据库的限定条件），避免不同 schema 下的同名表干扰；
• 给列名加上引号/反引号/方括号，防止列名包含空格、关键字等特殊字符导致语法错误；
• 如果你的需求不是按所有列分区，而是按部分列，只需要调整information_schema.columns查询的过滤条件即可（比如加AND COLUMN_NAME IN ('col1', 'col2')）。

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