英文:
Why does my recursive query stop after one step?
问题
以下是您要翻译的内容:
"I created a sample organization (in SQLite, view on DB Fiddle) with john
as the boss, who has two reports (luc
and bob
) and bob
has one report (steve
).
In order to seed the recursive query, I want to use the nickname
(and get a list of nickname
) but the relationship between the people is based on their login
."
create table users (
login text,
nickname text,
manager text
);
insert into users values ('john', 'j', NULL), ('luc', 'l', 'john'), ('bob', 'b', 'john'), ('steve', 's', 'bob');
"I created a recursive query
WITH orgtree AS (
select login from users where users.nickname='j';
UNION all
SELECT users.nickname FROM users, orgtree
WHERE users.manager=orgtree.login
)
SELECT users.nickname FROM users
WHERE users.nickname IN orgtree ;
What I get as a result is just the 'first line' of 'j' (of 'john'):
nickname |
---|
l |
b |
"What am I missing in my query to go deeper? (in either the anchor or the recursive member?)"
英文:
I would like to get a list of employees in an organization that ultimately report to a given person (a flattened sub-tree of the org chart). I created a sample organization (in SQLite, view on DB Fiddle) with john
as the boss, who has two reports (luc
and bob
) and bob
has one report (steve
).
In order to seed the recursive query, I want to use the nickname
(and get a list of nickname
) but the relationship between the people is based on their login
.
create table users (
login text,
nickname text,
manager text
);
insert into users values ('john', 'j', NULL), ('luc', 'l', 'john'), ('bob', 'b', 'john'), ('steve', 's', 'bob');
I created a recursive query
WITH orgtree AS (
select login from users where users.nickname='j'
UNION all
SELECT users.nickname FROM users, orgtree
WHERE users.manager=orgtree.login
)
SELECT users.nickname FROM users
WHERE users.nickname IN orgtree ;
What I get as a result is just the "first line" of j
(of john
):
nickname |
---|
l |
b |
What am I missing in my query to go deeper? (in either the anchor or the recursive member?)
答案1
得分: 2
I'll provide a translation of the code part you've shared:
以下是您分享的代码的翻译:
The immediate problem is that the recursive member of the CTE returns the user's nickname, while you need their login to be able to look up the next hierarchical level. A direct fix is:
直接问题是CTE的递归成员返回用户的昵称,而您需要他们的登录名才能查找下一个层次级别。一个直接的修复方法是:
with orgtree as (
select login from users where users.nickname='j'
union all
select users.login
from users, orgtree
where users.manager=orgtree.login
)
select nickname from users where login in orgtree;
But we can further improve the query, by keeping track of both the nickname and the login in the recursive, so there is no need for in
in the outer query:
但是我们可以进一步改进查询,通过在递归中跟踪昵称和登录名,从而在外部查询中不需要使用 in
:
with orgtree as (
select nickname, login from users where nickname = 'j'
union all
select u.nickname, u.login
from users u
inner join orgtree o on u.manager = o.login
)
select nickname from orgtree
请注意,我使用了标准的显式连接,而不是隐式的旧式连接。
nickname |
---|
j |
b |
l |
s |
英文:
The immediate problem is that the recursive member of the CTE returns the user's nickname, while you need their login to be able to look up the next hierarchical level. A direct fix is:
with orgtree as (
select login from users where users.nickname='j'
union all
select users.login
from users, orgtree
where users.manager=orgtree.login
)
select nickname from users where login in orgtree;
But we can further improve the query, by keeping track of both the nickname and the login in the recursive, so there is no need for in
in the outer query:
with orgtree as (
select nickname, login from users where nickname = 'j'
union all
select u.nickname, u.login
from users u
inner join orgtree o on u.manager = o.login
)
select nickname from orgtree
Note that I used standard, explicit joins rather than implicit, old-school joins.
nickname |
---|
j |
b |
l |
s |
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库,让每个人都能够通过互相帮助和分享经验来进步。
评论