b****e
发帖数: 1275 | 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*****h
发帖数: 139 | 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..
|
|
