|
su.dbms.sql
- SU.DBMS.SQL ------------------------------------------------------------------
From : Dmitry Vasiliev 2:5030/1055 15 Feb 2001 00:40:08
To : Ilya Zvyagin
Subject : ?Деревья - хочу мыслей
--------------------------------------------------------------------------------
# Hi Ilya !
#
# [Tue 13/Feb/2001 10:42] Ilya Zvyagin > All:
>> Для получения простого списка всех потомков определенного можно
IZ> использовать
>> SP. У меня это сделано примерно так (в SP)
IZ> Все замечательно. Только можно еще этот список поддерживать не во
IZ> временной, а в постоянной таблице, тогда вообще в любой момент можно
IZ> получить всех потомков или всех предков для любого узла.
[ S K I P ]
Если изменить стpyктypy таблицы с деpевом, то возможно вынимать
деpево с ypовнями одним селектом. Я сейчас не помню точные url-ы,
но эта тема легко ищется по запpосам типа: Trees in SQL.
# --In Windows Clipboard----------------------------------
It can be done if you are willing to change your table layout (and your
way of thinking). If you think of your tree as a set. Then redesign
your table for that purpose. What this does is redesign your table to
where selecting is less costly, but insert is fairly expensive
CREATE TABLE mytree (l INT, r INT, data TEXT);
INSERT INTO mytree (l, r, data) VALUES (1, 2, 'head');TREE: 1(head)2
Total count of elements: select count(*) from mytree;Insert new element:
1. You need to know the elements parent.r (in this case 'head's r or2);
2. BEGIN TRANSACTION; UPDATE mytree SET r=r+2 WHERE r>=parent.r;
UPDATE mytree SET l=l+2 WHERE l>=parent.r;
INSERT INTO mytree (l, r, data) VALUES (parent.r, parent.r+1,'c1');
END TRANSACTION;TREE: 1(head)4 / 2(c1)3
Lets insert 2 more for 'head' and two under 'c1' (why because any more
and I'd refuse to type out the tree).TREE: 1(head)12
/ | \ 2(c1)7 8(c2)9 10(c3)11
/ \ 3(c11)4 5(c12)6
(Here's the answer to the question you actually asked.)
Get all parents of 'c11' in order (to include row in question use
BETWEEN rather than '<' and '>' for all of the below):
SELECT t1.* FROM mytree t1, mytree t2 WHERE t2.l>t1.l AND t2.l<t1.r AND
t2.data = 'c11' ORDER BY t1.l;RESULT:l|r |data-+--+----1|12|head
2| 7|c1(2 rows)Get all children of 'c1' in branch order:
SELECT t1.* FROM mytree t1, mytree t2 WHERE t2.l<t1.r AND t2.r>t1.r AND
t2.data = 'c1' ORDER BY t1.l;RESULTS;l|r|data-+-+----3|4|c11
5|6|c12 (2 rows)Get all leaves (And in Postgres you could index r-l):
SELECT * from mytree WHERE r-l = 1 ORDER BY l;RESULT: l| r|data--+--+----
3| 4|c11 5| 6|c12 8| 9|c2 10|11|c3 (4 rows)Get all nodes in branch order:
SELECT * FROM mytree ORDER BY l;RESULT: l| r|data--+--+---- 1|12|head
2| 7|c1 3| 4|c11 5| 6|c12 8| 9|c2 10|11|c3 (6 rows)
Now this gave me fits for a long time (actually figured it out in this
message :^P ), How do you delete something? Answer: Just delete it and
the rest of the logic fills in the whole.
It becomes a un-name set but still contains the same elements.Let's DELETE 'c1';
DELETE FROM mytree WHERE data = 'c1';TREE: 1(head)12
/ | \ () 8(c2)9 10(c3)11
/ \ 3(c11)4 5(c12)6
Now let's redo the parents of 'c11':RESULT:l| r|data-+--+----1|12|head(1 row)
If you want to use this for a message board I'd suggest inserting a
dummy head that gets excluded from display in your app. instead of
trying to have multiple heads.
Let me know if you figure out an easy way to display treading, haven't
tried it myself.
# --Out Windows Clipboard---------------------------------
# Dima (hdima@ctorstudio.com) (icq: 96713063) (http://www.hlabs.spb.ru)
--- FREQ: NODELIST, NET5030, NET5030Z, PNT5030, SPBBS, STNET, STBBS...
* Origin: HIGH Labs Station (2:5030/1055)
Вернуться к списку тем, сортированных по: возрастание даты уменьшение даты тема автор
|
