MySQL：将指定列Unpivot为两列的实现方案

原始数据表 #

目标结果 #

需要将start_1与end_1、start_2与end_2成对转置，得到如下结构：

问题分析 #

你之前尝试的SQL通过两次交叉连接生成了所有start和end的组合，导致每个id产生4行多余数据，核心原因是没有保证start_n和end_n的一一对应关系。

正确方案 #

方案1：UNION ALL 手动成对提取（通用所有SQL数据库） #

直接将每组对应列作为单独行，再合并结果，这是最通用的写法：

SELECT id, start_1 AS start, end_1 AS end
FROM your_table
UNION ALL
SELECT id, start_2 AS start, end_2 AS end
FROM your_table
ORDER BY id;

这个语句会先提取每一行的start_1和end_1作为一行，再提取start_2和end_2作为另一行，合并后正好符合目标行数和对应关系。

方案2：UNPIVOT 配合过滤（适用于SQL Server等支持UNPIVOT的数据库） #

如果要使用UNPIVOT语法，需要在转置后过滤出start_n和end_n对应的行：

SELECT id, start, end
FROM your_table
UNPIVOT (
    start FOR start_col IN (start_1, start_2)
) AS unpivot_start
UNPIVOT (
    end FOR end_col IN (end_1, end_2)
) AS unpivot_end
WHERE RIGHT(start_col, 1) = RIGHT(end_col, 1); -- 匹配列名末尾的数字，保证成对

通过RIGHT函数提取列名最后一位，确保start_1只和end_1配对，start_2只和end_2配对，过滤掉无效的交叉组合。

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