Why does my recursive query stop after one step?

huangapple go评论68阅读模式
英文:

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

请注意,我使用了标准的显式连接,而不是隐式的旧式连接。

在DB Fiddle上演示

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.

Demo on DB Fiddle

nickname
j
b
l
s

huangapple
  • 本文由 发表于 2023年4月6日 22:19:54
  • 转载请务必保留本文链接:https://go.coder-hub.com/75950607.html
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定