如何通过CTE将同表不同type的name字段拆分为独立列展示？

要实现把type=1、2、3对应的name分别作为独立列展示，核心思路是给每个类型的条目分配行号，再按行号关联合并列，用CTE结合窗口函数就能解决你遇到的问题，以下是具体实现：

问题分析 #

• 子查询返回多行错误：直接用子查询作为列时，子查询返回多个值，数据库无法确定取哪一个，必须给每个类型的条目加唯一标识（行号）来匹配。
• CASE语句未达预期：单纯用CASE只能在每行显示对应类型的name，其他列留空，无法将同一类型的多个name按行排列。
• UNION不适用：UNION是合并行数据，而你需要的是拆分列，因此不适合。

具体SQL实现（标准SQL） #

WITH numbered_products AS (
    SELECT 
        name,
        type,
        -- 按type分组，给每个分组内的name分配行号（可根据需求修改ORDER BY字段）
        ROW_NUMBER() OVER (PARTITION BY type ORDER BY name) AS row_num
    FROM table1
)
SELECT 
    t1.name AS "Product with type 1",
    t2.name AS "Product with type 2",
    t3.name AS "Product with type 3"
FROM numbered_products t1
-- 用FULL JOIN确保不同类型条目数不同时不丢失数据
FULL JOIN numbered_products t2 
    ON t1.row_num = t2.row_num AND t2.type = 2
FULL JOIN numbered_products t3 
    ON COALESCE(t1.row_num, t2.row_num) = t3.row_num AND t3.type = 3
-- 过滤掉无有效数据的行
WHERE t1.type = 1 OR t2.type = 2 OR t3.type = 3
-- 按行号排序，保证同一序号的条目在一行
ORDER BY COALESCE(t1.row_num, t2.row_num, t3.row_num);

针对你的原表数据的运行结果 #

关键细节说明 #

1. ROW_NUMBER()窗口函数：PARTITION BY type将数据按类型分组，ORDER BY name决定每个分组内条目的排序顺序（可替换为ID等其他字段），给每个分组内的条目分配唯一行号。
2. FULL JOIN关联：如果不同类型的条目数量不一致，用FULL JOIN可以保留所有条目，缺失的列显示NULL；若使用LEFT JOIN可能会丢失条目数较多的类型的后续行。
3. COALESCE函数：处理行号为空的情况，确保排序时能正确按行号顺序排列。

兼容不同数据库的调整 #

• 若使用MySQL（不支持FULL JOIN），可以改用LEFT JOIN结合子查询获取最大行号，再生成序列关联：

WITH numbered_products AS (
    SELECT 
        name,
        type,
        ROW_NUMBER() OVER (PARTITION BY type ORDER BY name) AS row_num
    FROM table1
),
max_rows AS (
    SELECT MAX(row_num) AS max_row FROM numbered_products
),
row_sequence AS (
    SELECT 1 AS row_num UNION ALL SELECT row_num + 1 FROM row_sequence WHERE row_num < (SELECT max_row FROM max_rows)
)
SELECT 
    (SELECT name FROM numbered_products WHERE type=1 AND row_num=rs.row_num) AS "Product with type 1",
    (SELECT name FROM numbered_products WHERE type=2 AND row_num=rs.row_num) AS "Product with type 2",
    (SELECT name FROM numbered_products WHERE type=3 AND row_num=rs.row_num) AS "Product with type 3"
FROM row_sequence rs;

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