dEhrCSki

1MySQL: How to find leaves in specific node [closed]
2CREATE TABLE `DATA_TREE` (
3  `ID` int(11) NOT NULL,
4  `PARENT_ID` int(11) NOT NULL,
5  `DATA` varchar(45) DEFAULT NULL,
6  PRIMARY KEY (`ID`),
7  UNIQUE KEY `ID_UNIQUE` (`ID`),
8  KEY `fk_DATA_TREE_1_idx` (`PARENT_ID`),
9  CONSTRAINT `fk_DATA_TREE_1` FOREIGN KEY (`PARENT_ID`) REFERENCES `DATA_TREE` (`ID`) ON DELETE NO ACTION ON UPDATE NO ACTION
10) ENGINE=InnoDB DEFAULT CHARSET=utf
11	
12delimiter $$
13drop procedure if exists GetLeafNodes $$
14create procedure GetLeafNodes(nodeid int)
15begin
16declare N int default 1;
17
18-- create two working sets of IDs, we'll go back and forth between these two sets
19drop temporary table if exists A;
20drop temporary table if exists B;
21create temporary table A(node int, child int);
22create temporary table B(node int, child int);
23
24-- insert our single input node into the working set
25insert into A values (null, nodeid);
26
27while (N>0) do
28  -- keep selecting child nodes for each node we are now tracking
29  -- leaf nodes will end up with the child set to null
30  insert into B
31  select ifnull(A.child,A.node), tree.ID
32    from A
33    left outer join DATA_TREE as tree on A.child=tree.parent_id;
34
35  -- now swap A and B
36  rename table A to temp, B to A, temp to B;
37
38  -- remove non-leaf nodes from table B
39  delete from B;
40
41  -- exit when there are no longer any non-leaf nodes in A
42  set N=(select count(*) from A where child is not null);
43end while;
44
45-- now output our list of leaf nodes
46select node from A;
47
48drop temporary table A;
49drop temporary table B;
50end $$
51DELIMITER ;
52call GetLeafNodes(4);
53	
54CREATE TABLE `DATA_TREE` (
55  `ID` int(11) NOT NULL,
56  `PARENT_ID` int(11) NOT NULL,
57  PRIMARY KEY (`ID`),
58  UNIQUE KEY `ID_UNIQUE` (`ID`),
59  KEY `fk_DATA_TREE_1_idx` (`PARENT_ID`)
60) ENGINE=InnoDB
61;
62
63insert into DATA_TREE values
64(1,0),(2,1),(3,1),(4,1),(5,3),(6,3),(7,4),(8,4),(9,4),(10,6),(11,6),(12,7),(13,9),(14,9),(15,12),(16,12),(17,12),(18,14);
65	
66SELECT
67    CASE WHEN child.parent_id is null THEN
68        parent.id 
69    END as leaves 
70FROM data_tree AS parent JOIN data_tree AS child ON
71    parent.id = child.parent_id 
72WHERE
73    parent.parent_id = <node_id>;