递归查询行到单列？

发布于 2024-10-05 03:07:44 字数 346 浏览 6 评论 0原文

1）什么是递归查询？ 2）它们危险吗？ 3) 如何进行递归查询以给出从

ID Date
1  10/10/2010
1  20/10/2010
1  20/10/2010
2  11/10/2010
2  22/10/2010

到

   ID  Dates
    1  10/10/2010,20/10/2010,20/10/2010
    2  11/10/2010,22/10/2010

的结果4) 您能解释一下递归在查询中是如何运行的吗？我用谷歌搜索但无法了解递归实际上是如何工作的。我的数据库是 DB2 ISeries V5R4。

原文

1) What are recursive queries ?
2) Are they dangerous ?
3) How can I make a recursive query to give me results from

ID Date
1  10/10/2010
1  20/10/2010
1  20/10/2010
2  11/10/2010
2  22/10/2010

   ID  Dates
    1  10/10/2010,20/10/2010,20/10/2010
    2  11/10/2010,22/10/2010

4) Can you explain how recursion operates inside the query? I googled but can't get how the recursion works actually. My database is DB2 ISeries V5R4.

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

筱果果 2024-10-12 03:07:45

递归查询是一种可以进行递归计算的SQL查询。换句话说，它可以使用自身的结果来继续查询。这是一个抽象的描述：

1/ ancestor (x, y) = parent (x, y)

2/ ancestor (x, y) = parent (x, z) && ancestor (Z, Y).

可以简单地理解，你列出 Z 的所有祖先，你列出它的所有父母，然后列出这些父母的所有父母......

例如，如果你有一个 Family 表格2 列父级和子级，如下所示：

pkey  char  1  not null  primary key
ckey  char  1  not null  primary key

('A','B') 
('A','C') 
('A','D')  
('C','E')  
('D','A')  
('D','E')  
('D','F')  
('F','G')

左侧是父级，右侧是子级。现在你想找到 A 的所有后代，那么这里是一些代码：

with parent_ctl (ckey) as 
(
select ckey
    from parents
        where pkey='A'
UNION ALL
select c.ckey
    from parents C, parent_ctl P
        where P.ckey = C.Pkey
)
select ckey from parent_ctl;

Recursive query is a SQL query that can do a recursive computation. In other words, it can use the results of itself to continue query. Here is an abstract description:

1/ ancestor (x, y) = parent (x, y)

2/ ancestor (x, y) = parent (x, z) && ancestor (Z, Y).

It can be understood in a brief way that you to list all ancestor of Z, you list all of its parents and then all parents of those parents...

For example, if you have a table of Family with 2 columns Parent and Child like this:

pkey  char  1  not null  primary key
ckey  char  1  not null  primary key

('A','B') 
('A','C') 
('A','D')  
('C','E')  
('D','A')  
('D','E')  
('D','F')  
('F','G')

The left handside is parent and the right hand side is children. Now you want to find all descedants of A then here is some code:

with parent_ctl (ckey) as 
(
select ckey
    from parents
        where pkey='A'
UNION ALL
select c.ckey
    from parents C, parent_ctl P
        where P.ckey = C.Pkey
)
select ckey from parent_ctl;

回复收藏 0 原文

~没有更多了~