如何在动态SQL中新增num列提取total_abc系列表名后缀？

核心思路是在动态生成每个表的查询语句时，新增num列，将表名中total_abc_后的后缀数字作为该列的值。以下分主流数据库给出具体实现：

SQL Server #

假设你通过系统表获取目标表名，动态拼接UNION ALL语句：

DECLARE @SQL NVARCHAR(MAX) = '';

-- 拼接每个表的查询，新增num列提取后缀
SELECT @SQL = @SQL + 
    'SELECT *, ''' + 
    SUBSTRING(name, CHARINDEX('total_abc_', name) + LEN('total_abc_'), LEN(name)) + 
    ''' AS num FROM ' + QUOTENAME(name) + ' UNION ALL '
FROM sys.tables
WHERE name LIKE 'total_abc_%';

-- 移除最后多余的UNION ALL
SET @SQL = LEFT(@SQL, LEN(@SQL) - 10);

-- 插入到目标表
SET @SQL = 'INSERT INTO TABLE_NEW_XY SELECT * FROM (' + @SQL + ') t';

EXEC sp_executesql @SQL;

如果后缀固定为3位数字，也可以用RIGHT(name, 3)替代SUBSTRING部分，更简洁。

MySQL #

利用GROUP_CONCAT拼接动态语句：

SET @SQL = '';

SELECT GROUP_CONCAT(
    CONCAT(
        'SELECT *, ''', 
        SUBSTRING(table_name, LOCATE('total_abc_', table_name) + LENGTH('total_abc_')), 
        ''' AS num FROM `', table_name, '`'
    ) SEPARATOR ' UNION ALL '
) INTO @SQL
FROM information_schema.tables
WHERE table_name LIKE 'total_abc_%';

SET @SQL = CONCAT('INSERT INTO TABLE_NEW_XY SELECT * FROM (', @SQL, ') t');

PREPARE stmt FROM @SQL;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Oracle #

通过LISTAGG拼接语句，注意Oracle表名默认大写：

DECLARE
    v_sql VARCHAR2(32767);
BEGIN
    SELECT LISTAGG(
        'SELECT *, ''' || 
        SUBSTR(table_name, INSTR(table_name, 'TOTAL_ABC_') + LENGTH('TOTAL_ABC_')) || 
        ''' AS num FROM ' || table_name,
        ' UNION ALL '
    ) WITHIN GROUP (ORDER BY table_name)
    INTO v_sql
    FROM user_tables
    WHERE table_name LIKE 'TOTAL_ABC_%';

    v_sql := 'INSERT INTO TABLE_NEW_XY SELECT * FROM (' || v_sql || ') t';
    EXECUTE IMMEDIATE v_sql;
END;
/

注意事项 #

• 如果后缀长度不固定，优先使用基于total_abc_定位的截取方式，避免固定长度导致的错误。
• 用QUOTENAME（SQL Server）、反引号（MySQL）包裹表名，防止表名含特殊字符引发语法错误。
• 确保动态语句拼接完成后无多余的UNION ALL，避免执行报错。

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