Ausgehend von diesem Thread,
hier mal die Version für
MySQL...
Folgende Tabelle habe ich für den Baum angelegt...
SQL-Code:
CREATE TABLE `nodes` (
`id` int(10) unsigned NOT NULL,
`parentid` int(10) unsigned default NULL,
`bez` varchar(45) NOT NULL default '',
`reihenfolge` int(10) unsigned NOT NULL default '0',
PRIMARY KEY (`id`),
KEY `FK_nodes_parentid` (`parentid`),
CONSTRAINT `FK_nodes_parentid` FOREIGN KEY (`parentid`) REFERENCES `nodes` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Beispielinhalt...
SQL-Code:
+----+----------+-----+-------------+
| id | parentid | bez | reihenfolge |
+----+----------+-----+-------------+
| 1 | NULL | A | 1 |
| 2 | 1 | AA | 1 |
| 3 | 1 | AB | 2 |
| 4 | NULL | B | 2 |
| 5 | 4 | BA | 1 |
| 6 | 4 | BB | 2 |
| 7 | 1 | AC | 3 |
| 8 | 7 | ACA | 1 |
+----+----------+-----+-------------+
Dann habe ich folgende Prozedur angelegt...
SQL-Code:
CREATE PROCEDURE proc_GetNodes (NodeID INTEGER)
BEGIN
DECLARE _Depth INT DEFAULT 1;
DECLARE _Done INT DEFAULT 0;
CREATE TEMPORARY TABLE tmpTable (
id INT PRIMARY KEY, Depth INT, NodePath VARCHAR(1000)
) TYPE=HEAP;
CREATE TEMPORARY TABLE tmpTable2 (
id INT PRIMARY KEY, Depth INT, NodePath VARCHAR(1000)
) TYPE=HEAP;
IF NodeID IS NULL THEN
INSERT tmpTable (id, Depth, NodePath)
SELECT id, _Depth, id
FROM nodes
WHERE parentid IS NULL;
ELSE
INSERT tmpTable (id, Depth, NodePath)
SELECT id, _Depth, id
FROM nodes
WHERE parentid = NodeID;
END IF;
IF ROW_COUNT() = 0 THEN
SET _Done = 1;
END IF;
WHILE _Done = 0 DO
SET _Depth = _Depth + 1;
INSERT tmpTable2 (id, Depth, NodePath)
SELECT t.id, _Depth, CONCAT(x.NodePath, '|', id)
FROM nodes t
INNER JOIN tmpTable x
ON t.parentid = x.id
WHERE x.Depth = _Depth-1;
IF ROW_COUNT() = 0 THEN
SET _Done = 1;
END IF;
INSERT tmpTable
SELECT *
FROM tmpTable2;
DELETE FROM tmpTable2;
END WHILE;
SELECT t.id, t.bez, x.Depth, x.NodePath
FROM nodes t
INNER JOIN tmpTable x
ON t.id = x.id
ORDER BY x.NodePath;
DROP TEMPORARY TABLE tmpTable;
DROP TEMPORARY TABLE tmpTable2;
END
Ausgabe des gesamten Baums...
SQL-Code:
CALL proc_GetNodes(NULL);
+------+-------------+
| ID | Bez |
+------+-------------+
| 1 | A |
| 2 | AA |
| 3 | AB |
| 7 | AC |
| 8 | ACA |
| 4 | B |
| 5 | BA |
| 6 | BB |
+------+-------------+
Ausgabe aller Elemente, die zum Element mit der ID = 1 gehören...
SQL-Code:
CALL proc_GetNodes(1);
+------+-------------+
| ID | Bez |
+------+-------------+
| 2 | AA |
| 3 | AB |
| 7 | AC |
| 8 | ACA |
+------+-------------+
Gruss
Thorsten