mysql - 使用 SQL 生成模式

Question

我有一个表，其中有一列包含数字 1-100。想要生成每个数字 N 重复 N 次的输出(因此 5 应出现 5 次):

1
2
2
3
3
3
4
4
4
4
and so on

Answer 1

对于8.0之前的mysql版本，

create table _set(n int);

insert into _set(n) values (1), (2), (3), (4), (5), (6); -- this goes on

select s2.* from _set as s
  join _set as s2 on s.n <= s2.n;

临时表不能打开两次documented issue .

对于mysql版本8.0，试试这个，

with _set as (select 1 as n 
            union all
            select n+1 as n from _set
            where n < 100
            )
select s2.* from _set s
    join _set s2 on s.n <= s2.n