Подзапрос SQL с использованием родительского значения в поле "Начать с"
Я пытаюсь объединить несколько запросов, но сталкиваюсь с проблемой, независимо от того, каким образом я пытаюсь соединить их вместе. Вот макет из двух:
Запрос 1 (Получить все G и связанные R):
select distinct
a.g_id,
a.g_name,
b.r_name,
b.r_id
from
g_tab a,
r_tab b,
f_tab c
where
a.g_id = b.g_id
and b.r_id = c.r_id
and c.f_id = 7
order by a.g_id
Запрос 2 (Обход иерархии и фильтрация по определенной группе):
with parentGs as
(
select
f.g_id,
f.g_name
from
g_tab f
where
f.delete_indicator is null
connect by
prior f.parent_g_id = f.g_id
start with
f.g_id = 22 --DO NOT WANT THIS HARDCODED
)
select
d.g_name
from
parentGs d,
g_group_members_tab e
where
d.g_id = e.member_g_id
and e.g_group_description = 'test'
Я не хочу изменять счетчик возврата для запроса 1, но хотел бы присоединиться к g_name из запроса 2 и добавить его в качестве нового столбца с псевдонимами.
Вот один пример того, что я пытаюсь получить, но не работает из-за того, что g_id называют "неверным идентификатором" в "начать с". Я знаю, что родительские значения действительны только в одном подзапросе, но все равно получат ту же ошибку, если реструктурируют только с одним подзапросом (в данном случае в "from"):
select distinct
a.g_id,
a.g_name,
b.r_name,
b.r_id,
(select
d.g_name
from
(select
f.g_id,
f.g_name
from
g_tab f
where
f.delete_indicator is null
connect by
prior f.parent_g_id = f.g_id
start with
f.g_id = a.g_id) d, --THIS GIVES INVALID IDENTIFIER
g_group_members_tab e
where
d.g_id = e.member_g_id
and e.g_group_description = 'test') as parent_g_name
from
g_tab a,
r_tab b,
f_tab c
where
a.g_id = b.g_id
and b.r_id = c.r_id
and c.f_id = 7
order by a.g_id
Любая помощь в объединении этих двух запросов будет принята с благодарностью. Моя целевая платформа - Oracle 11g. Большое спасибо!
* Обновить *
Вот несколько примеров данных SQL Fiddle:
create table g_tab
(
g_id number(9),
g_name varchar2(50),
parent_g_id number(9),
delete_indicator char(1)
)
/
create table g_group_members_tab
(
member_g_id number(9),
g_group_description varchar2(50)
)
/
create table r_tab
(
r_id number(9),
r_name varchar2(50),
g_id number(9)
)
/
create table f_tab
(
f_id number(9),
f_name varchar2(50),
r_id number(9)
)
/
insert into g_tab (g_id, g_name, parent_g_id) values (0, 'a', null)
/
insert into g_tab (g_id, g_name, parent_g_id) values (1, 'b', 0)
/
insert into g_tab (g_id, g_name, parent_g_id) values (2, 'c', 1)
/
insert into g_tab (g_id, g_name, parent_g_id) values (3, 'd', null)
/
insert into g_tab (g_id, g_name, parent_g_id) values (4, 'e', 3)
/
insert into g_tab (g_id, g_name, parent_g_id) values (5, 'f', 4)
/
insert into g_tab (g_id, g_name, parent_g_id) values (6, 'g', null)
/
insert into g_group_members_tab (member_g_id, g_group_description) values (1, 'test')
/
insert into g_group_members_tab (member_g_id, g_group_description) values (3, 'test')
/
insert into r_tab (r_id, r_name, g_id) values (0, 'r', 0)
/
insert into r_tab (r_id, r_name, g_id) values (0, 'r', 1)
/
insert into r_tab (r_id, r_name, g_id) values (0, 'r', 2)
/
insert into r_tab (r_id, r_name, g_id) values (0, 'r', 3)
/
insert into r_tab (r_id, r_name, g_id) values (0, 'r', 4)
/
insert into r_tab (r_id, r_name, g_id) values (1, 'rr', 5)
/
insert into r_tab (r_id, r_name, g_id) values (0, 'r', 6)
/
insert into f_tab (f_id, f_name, r_id) values (7, 'f', 0)
/
insert into f_tab (f_id, f_name, r_id) values (7, 'f', 1)
Желаемый результат:
g_id g_name r_name r_id parent_g_name
0 a r 0 null
1 b r 0 b
2 c r 0 b
3 d r 0 d
4 e r 0 d
5 f rr 1 d
6 g r 0 null
1 ответ
Вы можете пройти большую часть пути, просто превратив свой подзапрос во встроенное представление:
select distinct
a.g_id,
a.g_name,
b.r_name,
b.r_id,
d.g_name as parent_g_name
from
g_tab a,
r_tab b,
f_tab c,
(
select
f.g_id,
f.g_name,
connect_by_root f.g_id as root_g_id
from
g_tab f
where
f.delete_indicator is null
connect by
prior f.parent_g_id = f.g_id
) d,
g_group_members_tab e
where
a.g_id = b.g_id
and b.r_id = c.r_id
and c.f_id = 7
and a.g_id = d.root_g_id
and d.g_id = e.member_g_id
and e.g_group_description = 'test'
order by a.g_id
Который дает:
G_ID G_NAME R_NAME R_ID PARENT_G_NAME
---------- ------ ------ ---------- -------------
1 b r 0 b
2 c r 0 b
3 d r 0 d
4 e r 0 d
5 f rr 1 d
Вы пропускаете нули, поэтому вам нужно превратить это во внешнее соединение; но вам нужно присоединиться d в e в этом. Все это намного приятнее с объединениями ANSI:
select distinct
a.g_id,
a.g_name,
b.r_name,
b.r_id,
d.g_name as parent_g_name
from g_tab a
join r_tab b
on b.g_id = a.g_id
join f_tab c
on c.r_id = b.r_id
left join (
select
e.g_id,
e.g_name,
e.root_g_id
from (
select
f.g_id,
f.g_name,
connect_by_root f.g_id as root_g_id
from
g_tab f
where
f.delete_indicator is null
connect by
prior f.parent_g_id = f.g_id) e
join g_group_members_tab f
on f.member_g_id = e.g_id
where f.g_group_description = 'test'
) d
on d.root_g_id = a.g_id
where c.f_id = 7
order by a.g_id
Который дает:
G_ID G_NAME R_NAME R_ID PARENT_G_NAME
---------- ------ ------ ---------- -------------
0 a r 0
1 b r 0 b
2 c r 0 b
3 d r 0 d
4 e r 0 d
5 f rr 1 d
6 g r 0