ИСПОЛЬЗУЯ предложение CONNECT BY в ORACLE
У меня есть следующая таблица:
CREATE TABLE TASK_2
(
ROLE_NAME VARCHAR2(50 BYTE),
MIN_CNT NUMBER,
MAX_CNT NUMBER
)
СО СЛЕДУЮЩИМИ ДАННЫМИ:
INSERT INTO TASK_2 VALUES ( 'SE', 3, 5);
INSERT INTO TASK_2 VALUES ( 'SSE', 2, 6);
INSERT INTO TASK_2 VALUES ( 'MGR', 3, 5);
INSERT INTO TASK_2 VALUES ( 'SR_MGR', 1, 4);
Желаемый результат:
se there are 3;
se there are 4;
se there are 5;
sse there are 2;
sse there are 3;
sse there are 4;
sse there are 5;
sse there are 6;
mgr there are 3;
mgr there are 4;
mgr there are 5;
sr_mgr there are 1;
sr_mgr there are 2;
sr_mgr there are 3;
sr_mgr there are 4;
Я попытался использовать подключение и уровень.
SELECT role_name||' there are '||level
FROM task_2
CONNECT BY level < max_cnt
AND level > min_cnt
ORDER BY role_name;
И РЕЗУЛЬТАТ:
mgr there are 4
mgr there are 4
mgr there are 4
mgr there are 1
mgr there are 4
mgr there are 4
mgr there are 4
mgr there are 4
mgr there are 4
se there are 4
se there are 4
se there are 4
se there are 1
se there are 4
se there are 4
se there are 4
se there are 4
se there are 4
sr_mgr there are 2
sr_mgr there are 3
sr_mgr there are 2
sr_mgr there are 3
sr_mgr there are 2
sr_mgr there are 1
sr_mgr there are 3
sr_mgr there are 2
sr_mgr there are 3
sse there are 5
sse there are 5
sse there are 5
sse there are 5
sse there are 5
sse there are 5
sse there are 3
sse there are 3
sse there are 4
sse there are 5
sse there are 4
sse there are 5
sse there are 5
sse there are 5
sse there are 5
sse there are 5
sse there are 5
sse there are 4
sse there are 5
sse there are 5
sse there are 5
sse there are 4
sse there are 5
sse there are 3
sse there are 4
sse there are 5
sse there are 4
sse there are 5
sse there are 5
sse there are 5
sse there are 1
sse there are 5
sse there are 5
sse there are 3
sse there are 5
sse there are 4
sse there are 4
64 строки выбраны
Итак, я не вижу, что и как именно мне следует использовать. Потому что уровень меняется в зависимости от имени_ роли каждый раз. Так может кто-нибудь сказать мне, что мне не хватает?
3 ответа
Решение
В 11g просто используйте рекурсивный факторинг подзапрос:
with data (role_name , max_cnt, val)
as (select role_name , max_cnt, min_cnt
from task_2
union all
select role_name , max_cnt, val+1
from data
where val+1 <= max_cnt)
select role_name || ' there are ' || val
from data
order by role_name, val;
если вы хотите connect by подход (не так хорошо, как указано выше), то:
select role_name || ' there are ' || (min_cnt + d.r - 1)
from task_2 t
cross join (select rownum r
from dual
connect by level <= (select max(max_cnt - min_cnt + 1)
from task_2)) d
where d.r <= max_cnt - min_cnt + 1
order by role_name, d.r;
Типовое предложение:
with data as (select role_name, min_cnt, max_cnt, max_cnt-min_cnt+1 vals
from task_2)
select role_name|| ' there are ' || vals
from data
model
partition by (role_name)
dimension by (0 as i)
measures (vals, min_cnt)
rules (
vals[for i from 0 to vals[0] increment 1] = min_cnt[0] + cv(i)
)
with TASK_2 as (
select 'SE' as ROLE_NAME , 3 as MIN_CNT , 5 as MAX_CNT from dual union
select 'SSE', 2, 6 from dual union
select 'MGR', 3, 5 from dual union
select 'SR_MGR', 1, 4 from dual
)
select t.ROLE_NAME ||' there are '||l.lv||';' as txt from TASK_2 t left join
(select level as lv from dual connect by level <= (select max(MAX_CNT) from TASK_2)) l
on l.lv between t.MIN_CNT and t.MAX_CNT
order by txt
select a.role_name||' there are '|| b.n Txt from TASK_2 a,
(select rownum n from dual connect by level <= (select sum(max_cnt) from TASK_2)) b
where b.n >= min_cnt and b.n <= max_cnt
order by a.role_name,b.n;