如何在Oracle的SELECT语句中添加唯一人工主键列？

你可以通过窗口函数在分组后的结果集上生成独立的唯一数值列，完全不需要依赖原表ID，也不会改变原有分组统计的逻辑。以下是主流数据库的具体实现方案：

1. SQL Server / Azure SQL #

使用ROW_NUMBER()窗口函数生成连续唯一序号：

SELECT 
    ROW_NUMBER() OVER (ORDER BY x) AS id, -- 人工唯一主键
    x, 
    AVG(y) AS z
FROM 
    table
UNPIVOT
    (y FOR x
       IN ("COLUMN1" AS 'A',
           "COLUMN1" AS 'B',
           "COLUMN2" AS 'C',
           "COLUMN3" AS 'D',
           "COLUMN4" AS 'E',
           "COLUMN5" AS 'F',
           "COLUMN6" AS 'G'))
GROUP BY 
    x;

建议在ORDER BY中指定固定列（比如x），保证每次查询生成的序号顺序一致。

2. MySQL 8.0+ / MariaDB 10.2+ #

语法与SQL Server一致，注意table是关键字，需要用反引号包裹：

SELECT 
    ROW_NUMBER() OVER (ORDER BY x) AS id,
    x, 
    AVG(y) AS z
FROM 
    `table`
UNPIVOT
    (y FOR x
       IN ("COLUMN1" AS 'A',
           "COLUMN1" AS 'B',
           "COLUMN2" AS 'C',
           "COLUMN3" AS 'D',
           "COLUMN4" AS 'E',
           "COLUMN5" AS 'F',
           "COLUMN6" AS 'G'))
GROUP BY 
    x;

若你的MySQL版本不支持UNPIVOT，可改用UNION ALL实现列转行，主键生成逻辑不受影响。

3. PostgreSQL #

PostgreSQL无原生UNPIVOT，用UNION ALL实现列转行后，同样通过ROW_NUMBER()生成主键：

SELECT 
    ROW_NUMBER() OVER (ORDER BY x) AS id,
    x, 
    AVG(y) AS z
FROM (
    SELECT 'A' AS x, "COLUMN1" AS y FROM "table"
    UNION ALL SELECT 'B' AS x, "COLUMN1" AS y FROM "table"
    UNION ALL SELECT 'C' AS x, "COLUMN2" AS y FROM "table"
    UNION ALL SELECT 'D' AS x, "COLUMN3" AS y FROM "table"
    UNION ALL SELECT 'E' AS x, "COLUMN4" AS y FROM "table"
    UNION ALL SELECT 'F' AS x, "COLUMN5" AS y FROM "table"
    UNION ALL SELECT 'G' AS x, "COLUMN6" AS y FROM "table"
) AS unpivoted_data
GROUP BY 
    x;

核心说明 #

• ROW_NUMBER()仅针对最终分组后的结果集生成唯一序号，完全独立于原表数据，不会干扰AVG(y)的计算结果。
• 如果需要全局唯一的主键（而非仅当前结果集内唯一），可结合数据库的序列对象（比如PostgreSQL的nextval('自定义序列名')），但需提前创建序列；临时结果集场景下，ROW_NUMBER()足够满足需求。

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