SQL如何实现按列值重复输出对应行数（含无函数及自定义函数方案）

该需求完全可以通过SQL实现，以下分别给出两类实现方案，输入表统一命名为t_input，字段为Col。

无需自定义函数的实现方案 #

主流关系型数据库（MySQL 8.0+、PostgreSQL、SQL Server、Oracle 11gR2+）都支持递归CTE语法，可以直接用该特性实现，无需额外开发自定义函数：

WITH RECURSIVE repeat_cte AS (
    -- 锚点成员：每条记录初始化计数为1
    SELECT Col, 1 AS cnt FROM t_input
    UNION ALL
    -- 递归成员：计数小于Col值时继续生成新行，直到计数等于Col值停止
    SELECT Col, cnt + 1 FROM repeat_cte WHERE cnt < Col
)
SELECT Col FROM repeat_cte ORDER BY Col;

如果使用PostgreSQL数据库，还可以用自带的generate_series函数更简便实现：

SELECT t.Col 
FROM t_input t
JOIN generate_series(1, (SELECT MAX(Col) FROM t_input)) s 
ON s.s <= t.Col
ORDER BY t.Col;

上述方案都可以完美兼容Col列存在重复值的场景。

需要自定义函数的实现方案 #

如果使用不支持递归CTE的低版本数据库，可以通过自定义序列生成函数的方式实现，以下以MySQL为例：

-- 第一步：创建自定义序列生成函数，入参为最大值，返回1到最大值的逗号分隔字符串
DELIMITER //
CREATE FUNCTION generate_num_seq(max_val INT)
RETURNS VARCHAR(4096)
DETERMINISTIC
BEGIN
    DECLARE result_str VARCHAR(4096) DEFAULT '';
    DECLARE curr_num INT DEFAULT 1;
    WHILE curr_num <= max_val DO
        SET result_str = CONCAT(result_str, curr_num, ',');
        SET curr_num = curr_num + 1;
    END WHILE;
    RETURN TRIM(TRAILING ',' FROM result_str);
END //
DELIMITER ;

-- 第二步：关联函数生成的序列实现重复行输出
SELECT t.Col 
FROM t_input t
JOIN JSON_TABLE(
    CONCAT('["', REPLACE(generate_num_seq(t.Col), ',', '","'), '"]'),
    '$[*]' COLUMNS (seq_num INT PATH '$')
) jt ON 1=1
ORDER BY t.Col;

如果是MySQL 5.7及更低版本不支持JSON_TABLE，也可以配合提前创建的数字辅助表+自定义函数实现。

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