如何为列转行后的表添加唯一行值列并简化实现，避免ID为0？

现有一张parents表，通过如下SQL查询获取数据：

SELECT 
    parent_id,  
    parent2_id,   
    parent3_id,  
    parent4_id  
FROM 
    parents

需要将查询结果的4列转换为4行的结构，同时添加一列包含每行的唯一值，并且要求结果表的ID列无0值。目前已找到一个实现方案但较为繁琐，希望得到简化的方案。

通用方案（适配多数关系型数据库） #

使用UNION ALL拆分列并过滤0值，再生成唯一ID，语法适配MySQL、SQL Server、Oracle等：

SELECT 
    ROW_NUMBER() OVER(ORDER BY t.parent_type, t.parent_val) AS unique_id,
    t.parent_val AS parent_id
FROM (
    SELECT 'parent1' AS parent_type, parent_id AS parent_val FROM parents WHERE parent_id != 0
    UNION ALL
    SELECT 'parent2' AS parent_type, parent2_id AS parent_val FROM parents WHERE parent2_id != 0
    UNION ALL
    SELECT 'parent3' AS parent_type, parent3_id AS parent_val FROM parents WHERE parent3_id != 0
    UNION ALL
    SELECT 'parent4' AS parent_type, parent4_id AS parent_val FROM parents WHERE parent4_id != 0
) t

• 内层通过UNION ALL将4列拆分为行记录，同时用WHERE过滤掉值为0的条目
• 外层用ROW_NUMBER()生成全局唯一的unique_id，可根据需求调整ORDER BY后的排序逻辑

优化方案（针对特定数据库） #

SQL Server 2008+ #

使用CROSS APPLY结合VALUES行构造器，代码更紧凑：

SELECT 
    ROW_NUMBER() OVER(ORDER BY v.parent_type, v.parent_val) AS unique_id,
    v.parent_val AS parent_id
FROM parents
CROSS APPLY (
    VALUES
        ('parent1', parent_id),
        ('parent2', parent2_id),
        ('parent3', parent3_id),
        ('parent4', parent4_id)
) v(parent_type, parent_val)
WHERE v.parent_val != 0

PostgreSQL #

使用LATERAL JOIN配合VALUES构造行集合，逻辑更清晰：

SELECT 
    ROW_NUMBER() OVER(ORDER BY t.parent_type, t.parent_val) AS unique_id,
    t.parent_val AS parent_id
FROM parents
LATERAL (
    SELECT * FROM (
        VALUES
            ('parent1', parent_id),
            ('parent2', parent2_id),
            ('parent3', parent3_id),
            ('parent4', parent4_id)
    ) AS temp(parent_type, parent_val)
    WHERE temp.parent_val != 0
) t

或者用unnest函数实现：

SELECT 
    ROW_NUMBER() OVER(ORDER BY unnest_types, unnest_vals) AS unique_id,
    unnest_vals AS parent_id
FROM parents,
    unnest(ARRAY['parent1','parent2','parent3','parent4']) AS unnest_types,
    unnest(ARRAY[parent_id, parent2_id, parent3_id, parent4_id]) AS unnest_vals
WHERE unnest_vals != 0

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