如何编写SQL将Table1与T2列合并生成指定格式的T3表

SQL的堆表没有默认行顺序，因此我们需要先为两张表按现有展示顺序生成行号，再按行号关联拼接得到结果，以下是支持窗口函数的主流数据库通用SQL示例（以MySQL语法为例）：

WITH t1_with_rn AS (
    SELECT 
        col AS num,
        -- 按表现有自然顺序生成行号
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn 
    FROM Table1
),
t2_with_rn AS (
    SELECT 
        col AS chars,
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS rn
    FROM T2
)
SELECT CONCAT(t1.num, REPEAT(t2.chars, t1.num)) AS Table3
FROM t1_with_rn t1
INNER JOIN t2_with_rn t2 ON t1.rn = t2.rn;

适配不同数据库可调整对应函数：

• SQL Server：将REPEAT函数替换为REPLICATE即可
• Oracle：可使用RPAD(t2.chars, t1.num, t2.chars)替代REPEAT逻辑
• 不支持窗口函数的低版本MySQL可使用变量生成行号：

SELECT CONCAT(t1.num, REPEAT(t2.chars, t1.num)) AS Table3
FROM (
    SELECT @rn1 := @rn1 + 1 AS rn, col AS num
    FROM Table1, (SELECT @rn1 := 0) init_t1
) t1
INNER JOIN (
    SELECT @rn2 := @rn2 + 1 AS rn, col AS chars
    FROM T2, (SELECT @rn2 := 0) init_t2
) t2 ON t1.rn = t2.rn;

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