SQL实现：将两INT列间所有数字拼接至第三列的方法求助

下面针对不同主流数据库给出具体实现方案：

MySQL/MariaDB 版本 #

如果你的数据库版本支持递归CTE（MySQL 8.0+），可以用下面的语句：

WITH RECURSIVE num_range AS (
    SELECT ColumnA AS num, ColumnA, ColumnB
    FROM your_table
    UNION ALL
    SELECT num + 1, ColumnA, ColumnB
    FROM num_range
    WHERE num < ColumnB
)
SELECT 
    ColumnA,
    ColumnB,
    GROUP_CONCAT(num SEPARATOR ';') AS ColumnC
FROM num_range
GROUP BY ColumnA, ColumnB;

要是用的是低于8.0的MySQL，得提前建一个数字辅助表（比如存1到1000的数字），再关联查询生成范围。

SQL Server 版本 #

SQL Server 2017及以上可以用STRING_AGG，配合递归CTE：

WITH num_range AS (
    SELECT ColumnA AS num, ColumnA, ColumnB
    FROM your_table
    UNION ALL
    SELECT num + 1, ColumnA, ColumnB
    FROM num_range
    WHERE num < ColumnB
)
SELECT 
    ColumnA,
    ColumnB,
    STRING_AGG(num, ';') AS ColumnC
FROM num_range
GROUP BY ColumnA, ColumnB
OPTION (MAXRECURSION 0);

如果是2017之前的版本，用FOR XML PATH的方式拼接：

SELECT 
    t.ColumnA,
    t.ColumnB,
    STUFF((
        SELECT ';' + CAST((t.ColumnA + n - 1) AS VARCHAR)
        FROM (
            SELECT TOP (t.ColumnB - t.ColumnA + 1) ROW_NUMBER() OVER(ORDER BY (SELECT NULL)) AS n
            FROM sys.all_columns
        ) AS nums
        FOR XML PATH('')
    ), 1, 1, '') AS ColumnC
FROM your_table t;

PostgreSQL 版本 #

递归CTE加上STRING_AGG，注意要把数字转成文本：

WITH RECURSIVE num_range AS (
    SELECT ColumnA AS num, ColumnA, ColumnB
    FROM your_table
    UNION ALL
    SELECT num + 1, ColumnA, ColumnB
    FROM num_range
    WHERE num < ColumnB
)
SELECT 
    ColumnA,
    ColumnB,
    STRING_AGG(num::TEXT, ';') AS ColumnC
FROM num_range
GROUP BY ColumnA, ColumnB;

Oracle 版本 #

用CONNECT BY生成序列，再用LISTAGG拼接：

SELECT 
    ColumnA,
    ColumnB,
    LISTAGG(num, ';') WITHIN GROUP (ORDER BY num) AS ColumnC
FROM (
    SELECT 
        t.ColumnA,
        t.ColumnB,
        t.ColumnA + LEVEL - 1 AS num
    FROM your_table t
    CONNECT BY LEVEL <= t.ColumnB - t.ColumnA + 1
        AND PRIOR t.ColumnA = t.ColumnA
        AND PRIOR t.ColumnB = t.ColumnB
        AND PRIOR SYS_GUID() IS NOT NULL
)
GROUP BY ColumnA, ColumnB;

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