我有以下数据集,按深度和loc分组后得到:
dept id loc count
100 a 2
300 c 1
我需要把下面的东西放出来
deptid loc
100年
100年
300 c
请帮
另一种选择是使用Recursive Queries
来获得期望的结果。
with cte(deptid, loc, cnt) as
(select deptid, loc, cnt - 1 from dept
union all
select cte.deptid, cte.loc, cte.cnt - 1
from cte, dept
where dept.deptid = cte.deptid and dept.loc = cte.loc
and cte.cnt > 0)
select deptid, loc from cte order by deptid, loc;
DB Fiddle Link:https://dbfiddle.uk/?rdbms=oracle_18&小提琴= 0580680 ecf0b47b4933e3a4731b62b4a
您需要根据count列的最大值生成行(内联视图rws),然后将其连接到基表,如下所示:
select t.DEPT_ID, t.LOC
from your_table t
join (
select level lvl from dual
connect by level <= (select max(count) from your_table )
)rws
on rws.lvl <= t.count
order by 1, 2
;