如何将多列整数类型字段合并为单个字段？附SQL查询语句

嘿，你这需求其实就是把横向的多列转换成纵向的单列，也就是SQL里常说的**行转列（Unpivoting）**操作。根据你用的数据库不同，有几种不同的实现方式，我给你列出来最常用的几种：

通用方案：使用UNION ALL（兼容所有数据库） #

这个方法兼容性拉满，不管你用MySQL、PostgreSQL、SQL Server还是Oracle都能用。核心思路就是把每一列单独作为一个查询结果，然后用UNION ALL把所有结果拼起来。

基于你的查询语句，修改后的代码大概是这样：

-- 先把你的原查询作为子查询，获取所有需要合并的列
SELECT merged_value AS organisationunitid
FROM (
    SELECT ou.organisationunitid AS one, 
           ou2.organisationunitid AS two, 
           ou3.organisationunitid AS three, 
           ou4.organisationunitid AS four, 
           ou5.organisationunitid AS five, 
           ou6.organisationunitid AS six, 
           ou7.organisationunitid AS seven, 
           ou8.organisationunitid AS eight, 
           ou9.organisationunitid AS nine, 
           ou10.organisationunitid AS ten, 
           ou11.organisationunitid AS eleven 
    FROM orgunitgroupmembers ougm 
    -- 这里补上你原查询里的JOIN、WHERE等条件
    GROUP BY ou.organisationunitid, ou2.organisationunitid, ou3.organisationunitid,
             ou4.organisationunitid, ou5.organisationunitid, ou6.organisationunitid,
             ou7.organisationunitid, ou8.organisationunitid, ou9.organisationunitid,
             ou10.organisationunitid, ou11.organisationunitid
) AS source_table
-- 把每一列拆成单独的行
UNION ALL SELECT one FROM source_table
UNION ALL SELECT two FROM source_table
UNION ALL SELECT three FROM source_table
UNION ALL SELECT four FROM source_table
UNION ALL SELECT five FROM source_table
UNION ALL SELECT six FROM source_table
UNION ALL SELECT seven FROM source_table
UNION ALL SELECT eight FROM source_table
UNION ALL SELECT nine FROM source_table
UNION ALL SELECT ten FROM source_table
UNION ALL SELECT eleven FROM source_table
-- 可选：如果要去掉空值，可以加WHERE条件
WHERE merged_value IS NOT NULL;

注意：如果你的列里可能有NULL值，记得加上WHERE merged_value IS NOT NULL来过滤掉空行。

特定数据库的简化方案 #

1. SQL Server / Oracle：使用UNPIVOT关键字 #

这两个数据库支持原生的UNPIVOT语法，写起来更简洁：

SELECT organisationunitid
FROM (
    SELECT ou.organisationunitid AS one, 
           ou2.organisationunitid AS two, 
           -- 其他列和原查询一致
           ou11.organisationunitid AS eleven 
    FROM orgunitgroupmembers ougm 
    -- 原查询的JOIN、WHERE、GROUP BY
) AS source_table
UNPIVOT (
    organisationunitid FOR column_names IN (one, two, three, four, five, six, seven, eight, nine, ten, eleven)
) AS unpivoted_table
WHERE organisationunitid IS NOT NULL;

2. PostgreSQL：使用UNNEST和数组 #

PostgreSQL可以把列打包成数组，再用UNNEST展开成行：

SELECT UNNEST(ARRAY[one, two, three, four, five, six, seven, eight, nine, ten, eleven]) AS organisationunitid
FROM (
    SELECT ou.organisationunitid AS one, 
           ou2.organisationunitid AS two, 
           -- 其他列和原查询一致
           ou11.organisationunitid AS eleven 
    FROM orgunitgroupmembers ougm 
    -- 原查询的JOIN、WHERE、GROUP BY
) AS source_table
WHERE UNNEST(ARRAY[one, two, three, four, five, six, seven, eight, nine, ten, eleven]) IS NOT NULL;

3. MySQL 8.0+：使用JSON_TABLE #

如果你的MySQL版本是8.0及以上，也可以用JSON函数来实现：

SELECT j.organisationunitid
FROM (
    SELECT ou.organisationunitid AS one, 
           ou2.organisationunitid AS two, 
           -- 其他列和原查询一致
           ou11.organisationunitid AS eleven 
    FROM orgunitgroupmembers ougm 
    -- 原查询的JOIN、WHERE、GROUP BY
) AS source_table
JOIN JSON_TABLE(
    CONCAT('[', one, ',', two, ',', three, ',', four, ',', five, ',', six, ',', seven, ',', eight, ',', nine, ',', ten, ',', eleven, ']'),
    '$[*]' COLUMNS (organisationunitid INT PATH '$')
) j
WHERE j.organisationunitid IS NOT NULL;

选哪种方案取决于你用的数据库，要是追求兼容性就选UNION ALL，想写得简洁就用对应数据库的原生语法~

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