从分组记录中删除分组

Question

我在一个表上有一条记录，分组如下：

name	计数
车道	2
Jane	1
Wale	6

使用递归CTE展开它们

T-Sql(MS Sql Server(

;with RCTE as (
select name, 1 as lvl, [count] as cnt
from yourtable

union all
select name, lvl+1, cnt
from RCTE
where lvl < cnt
)
select name
from RCTE;

Postgresql，SQLite

with RECURSIVE RCTE as (
select name, 1 as lvl, "count" as cnt
from yourtable

union all
select name, lvl+1, cnt
from RCTE
where lvl < cnt
)
select name
from RCTE;

db<上的演示>小提琴这里

Pl/Sql(Oracle(

with RCTE (name, lvl, cnt) as (
select name, 1 as lvl, "count" as cnt
from yourtable

union all
select name, lvl+1, cnt
from RCTE
where lvl < cnt
)
select name
from RCTE;

MySql 8，MariaDB 10.3

with RECURSIVE RCTE as (
select name, 1 as lvl, `count` as cnt
from yourtable

union all
select name, lvl+1, cnt
from RCTE
where lvl < cnt
)
select name
from RCTE;

对于Oracle，您可以使用从第6行开始的查询(因为第1-5行表示示例数据(。

SQL> with test (name, cnt) as
2    (select 'Lanre', 2 from dual union all
3     select 'Jane' , 1 from dual union all
4     select 'Wale' , 6 from dual
5    )
6  select name
7  from test cross join table(cast(multiset(select level from dual
8                                           connect by level <= cnt
9                                          ) as sys.odcinumberlist))
10  order by name;
NAME
-----
Jane
Lanre
Lanre
Wale
Wale
Wale
Wale
Wale
Wale
9 rows selected.
SQL>