recursive sql? - 未名空间MITBBS历史存档

国际科技财经博客移民网络热点娱乐民生时事公众号

Redian新闻

>未名空间

>Database - 数据库

recursive sql?

recursive sql?# Database - 数据库

b*e2000-05-16 07:05

1 楼

i have a question..
if i have a table which has two columns, parent_id and child_id
which is used to represent a tree structure.. ie.. there're many
levels.. and each node can have many children.. but only 1 parent.
so, records would be like (1, 2), (1, 3), (1, 4), (2, 5), (2, 6),
(3, 7), (5,8) etc etc
now how do you select all leaf level children of a known parent_id?
what about selecting all children that are <=x levels below the
parent_id?
i guess there must be a recursive way of doing this..

w*h2000-05-16 07:05

2 楼

This is a typical BOM question.
SQL99 already supports recursive SQL.
You have a table: assembly(part, subpart,...)
CREATE RECURSIVE view all_subparts(Major, Minor) AS
SELECT PART SUBPART
FROM assembly
UNION
SELECT all.Major assb.SUBPART
FROM all_subparts all, assembly assb
WHERE all.minor = assb.PART
SELECT * FROM all_subparts

【在 b****e 的大作中提到】

: i have a question..
: if i have a table which has two columns, parent_id and child_id
: which is used to represent a tree structure.. ie.. there're many
: levels.. and each node can have many children.. but only 1 parent.
: so, records would be like (1, 2), (1, 3), (1, 4), (2, 5), (2, 6),
: (3, 7), (5,8) etc etc
: now how do you select all leaf level children of a known parent_id?
: what about selecting all children that are <=x levels below the
: parent_id?
: i guess there must be a recursive way of doing this..