如何创建“递归SQL"? [英] How can I create "recursive sql"

问题描述

我要建立链接"

例如，我有5条帖子(id:"1"，id:"2"，id:"3"，id:"4"，id:"5")

for Example, I have a 5 posts (id: "1", id: "2", id: "3", id: "4", id: "5")

他们有一个序列

{id:"1"，nextId:"2"}，
{id:"2"，nextId:"4"}，
{id:"3"， nextId:"0"}，
{id:"4"，nextId:"3"}，
{id:"5"，nextId:"0"}，

{id:"1", nextId:"2"},
{id:"2", nextId:"4"} ,
{id:"3", nextId:"0"},
{id:"4", nextId:"3"},
{id:"5", nextId:"0"},

当我从"1"搜索时，得到的结果是:{id:"1"}，{id:"2"}，{id:"4"}，{id:"3"} 当我从"5"搜索时，得到的结果是:{id:"5"}

when I search from "1", I got a result : {id:"1"}, {id:"2"}, {id:"4"}, {id:"3"} when I search from "5", I got a result : {id:"5"}

如何在ANSI SQL中查找全部以{id:"1"}开头的

How can I find All start with {id:"1"} in ANSI SQL?

select s.id, s.nextId from sample s
join sample ns on ns.id = s.nextId

它从第一个节点到所有节点.

It makes from first node to all.

我要以"{some id}"开头，并且要使用"limit 10"

I want to start "{some id}" and I want to use "limit 10"

救救我！

推荐答案

create table links (id integer, nextid integer);

insert into links 
values 
(1, 2),
(2, 4),
(3, 0),
(4, 3),
(5, 0);

commit;

with recursive link_tree as (
   select id, nextid
   from links
   where id = 1  -- change this to change your starting node
   union all 
   select c.id, c.nextid
   from links c
     join link_tree p on p.nextid = c.id
)
select *
from link_tree;

这是ANSI SQL，适用于HSQLDB，PostgreSQL，H2，Firebird，DB2，Microsoft SQL Server，Oracle 11.2和其他几个引擎-在MySQL上为 not (不支持任何当今最先进的现代SQL功能).

This is ANSI SQL and works on HSQLDB, PostgreSQL, H2, Firebird, DB2, Microsoft SQL Server, Oracle 11.2 and several other engines - just not on MySQL (which does not support any of the modern SQL features which are state of the art nowadays).

这篇关于如何创建“递归SQL"?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！