SQL如何根据行内字段值将数据行重复指定次数

现有存储班级分性别人数的表mytable，字段定义：

• class：班级标识，文本类型
• malecount：班级内男性人数，整数类型
• femalecount：班级内女性人数，整数类型

需要输出Class、Gender两列结果：按照每个班的男女人数，生成对应条数的记录，男性标记为M，女性标记为F，人数为0时不生成对应性别的记录。比如A班男性人数2、女性人数1，就生成2条(A,M)记录、1条(A,F)记录。

测试表初始化SQL：

create table mytable (class text, malecount int, femalecount int);

insert into mytable (class, malecount, femalecount) values
( 'A',2,1),
('B',3,1),
('C',0,3),
('D',2,4);

核心思路是用递归CTE生成连续数字序列，通过数字和人数的关联控制每个班级对应性别的记录生成条数：

WITH RECURSIVE num_seq AS (
    SELECT 1 AS seq
    UNION ALL
    SELECT seq + 1 FROM num_seq
    WHERE seq < (SELECT MAX(GREATEST(malecount, femalecount)) FROM mytable)
)
SELECT class AS Class, 'M' AS Gender
FROM mytable
INNER JOIN num_seq ON num_seq.seq <= mytable.malecount
UNION ALL
SELECT class AS Class, 'F' AS Gender
FROM mytable
INNER JOIN num_seq ON num_seq.seq <= mytable.femalecount
ORDER BY Class, Gender;

PostgreSQL自带序列生成函数generate_series，不需要手写递归CTE，写法更简洁：

SELECT class AS Class, 'M' AS Gender
FROM mytable, generate_series(1, malecount)
UNION ALL
SELECT class AS Class, 'F' AS Gender
FROM mytable, generate_series(1, femalecount)
ORDER BY Class, Gender;

执行上述SQL后会得到16条记录，和预期输出完全匹配：

• A班：2条M、1条F
• B班：3条M、1条F
• C班：3条F
• D班：2条M、4条F

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