使用information_schema实现全列动态分区统计重复行的SQL问题

你当前写法的核心错误是：PARTITION BY 后跟的是一个返回拼接字符串的标量子查询，本质是给所有行分配了同一个常量分区键，数据库会把所有行归到同一个分区内计算行号，最终得到全局递增的序号，达不到按所有列分区计数的效果。

SQL 不支持在静态查询的语法位置直接嵌入子查询返回的字符串作为列名，你需要用动态 SQL 拼接完整的查询语句，把获取到的所有列名直接拼到 PARTITION BY 的参数位置，以下是常见数据库的实现：

SQL Server 版本 #

DECLARE @cols NVARCHAR(MAX), @sql NVARCHAR(MAX)
-- 获取Example表的所有列名，拼接为逗号分隔的格式
SELECT @cols = STRING_AGG(QUOTENAME(column_name), ',')
FROM [information_schema].[columns] 
WHERE table_name = 'Example'
-- 拼接最终执行的查询语句
SET @sql = N'
SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY ' + @cols + ' ORDER BY id) AS row_count
FROM [Example]
'
-- 执行动态SQL
EXEC sp_executesql @sql

MySQL 版本 #

-- 获取当前库Example表的所有列名，拼接为逗号分隔的格式
SET @cols = (
    SELECT GROUP_CONCAT(CONCAT('`', column_name, '`'))
    FROM information_schema.columns 
    WHERE table_name = 'Example' AND table_schema = DATABASE()
);
-- 拼接最终查询语句
SET @sql = CONCAT('
SELECT 
    *,
    ROW_NUMBER() OVER (PARTITION BY ', @cols, ' ORDER BY id) AS row_count
FROM `Example`
');
-- 执行动态SQL
PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

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