递归SQLite SELECT：我无法理解

t9aqgxwy 于 2023-02-23 发布在 SQLite

关注(0)|答案(1)|浏览(192)

我有这个SQLite表：

CREATE TABLE seed (
  code TEXT PRIMARY KEY,
  name TEXT,
  grams_per_seed REAL,
  mother TEXT REFERENCES seed (code) DEFERRABLE INITIALLY DEFERRED,
  notes TEXT,
  mother_notes TEXT
) STRICT;

包含的数据包括：

sqlite> select code, mother from seed
   ...> where code in ("RP20-3", "RP19-1", "BC18-MG", "BC18-TI");
code     mother
-------  -------
BC18-MG
BC18-TI
RP19-1   BC18-TI
RP20-3   RP19-1

注意RP20-3 → RP19-1 → BC18-TI的祖先链，在真实的数据中有很多这样的例子。
我需要的是一个将种子代码Map到其祖先链长度的查询。例如：

sqlite> select [... something ...] from [... something else ...];
code     a_ct
-------  ----
BC18-MG     0
BC18-TI     0
RP19-1      1
RP20-3      2

我已经用这个杂牌完成了一部分，但是我知道这是不正确的，因为最大链长编码在连接宽度中：

sqlite> select child.code, parent.code, grand.code
   ...> from seed as child, seed as parent, seed as grand
   ...> where     child.code = "RP20-3"
   ...>       and child.mother = parent.code
   ...>       and parent.mother = grand.code;
code    code    code
------  ------  -------
RP20-3  RP19-1  BC18-TI

我非常确定这需要递归查询，但我一直无法使它工作。我尝试了几个查询，这些查询被设计为针对链中每个我可以计数的成员返回一行，但我要么没有得到行，要么得到无限多的行。
This question与我想要的非常相似，但它只为单个项目返回祖先，而不是所有项目。
我已经找到了很多递归查询的例子，包括父子关系的例子。看起来这应该是相对简单的，但是我在某个地方卡住了。我确实很好地处理了过程语言中的递归。我错过了什么？

sqlite

来源：https://stackoverflow.com/questions/75515884/recursive-sqlite-select-can-t-wrap-my-head-around-it

1条答案

按热度按时间

polkgigr1#

It may be useful, examples with recursive queries

with recursive r as (
 select code chainhead,1 chainnum,code,mother from seed s1 
 where not exists(select * from seed s2 where s2.code=s1.mother)
 union all 
 select chainhead ,chainnum+1 n,s.code,s.mother
 from r inner join seed s on s.mother=r.code
 )
select chainhead
  ,min(case when chainnum=1 then code else null end) link0 
  ,min(case when chainnum=2 then code else null end) link2 
  ,min(case when chainnum=3 then code else null end) link3 
  ,min(case when chainnum=4 then code else null end) link4 
from r
group by chainhead;

chainhead	link0	link2	link3	link4
BC18-MG	BC18-MG	(null)	(null)	(null)
BC18-TI	BC18-TI	RP19-1	RP20-3	(null)

You may add as many as needed columns "linkN". In all cases column count is fixed.
In next example, accumulate chain in text. Limitation is recursion depth, or max TEXT size, not fixed.

with recursive rs as (
 select code chainhead,1 linknum,code,mother, code path from seed s1 
 where not exists(select * from seed s2 where s2.code=s1.mother)
 union all 
 select chainhead ,linknum+1 n,s.code,s.mother,r.path||','||s.code
 from rs r inner join seed s on s.mother=r.code
 )
select * from rs;

chainhead	linknum	code	mother	path
BC18-MG	1	BC18-MG		BC18-MG
BC18-TI	1	BC18-TI		BC18-TI
BC18-TI	2	RP19-1	BC18-TI	BC18-TI,RP19-1
BC18-TI	3	RP20-3	RP19-1	BC18-TI,RP19-1,RP20-3

赞(0）回复(0）举报 2023-02-23

我来回答

递归SQLite SELECT：我无法理解

1条答案

相关问题

热门标签

最新问答