SHOW:
|
|
- or go back to the newest paste.
| 1 | mysql> describe a; | |
| 2 | +-----------+-------------+------+-----+---------+----------------+ | |
| 3 | | Field | Type | Null | Key | Default | Extra | | |
| 4 | +-----------+-------------+------+-----+---------+----------------+ | |
| 5 | | id | int(11) | NO | PRI | NULL | auto_increment | | |
| 6 | | parent_id | int(11) | YES | MUL | NULL | | | |
| 7 | | name | varchar(45) | NO | | NULL | | | |
| 8 | +-----------+-------------+------+-----+---------+----------------+ | |
| 9 | 3 rows in set (0.04 sec) | |
| 10 | ||
| 11 | mysql> select * from a; | |
| 12 | +----+-----------+------+ | |
| 13 | | id | parent_id | name | | |
| 14 | +----+-----------+------+ | |
| 15 | | 1 | NULL | ROOT | | |
| 16 | | 2 | 1 | L1_0 | | |
| 17 | | 3 | 1 | L1_1 | | |
| 18 | | 4 | 2 | L2_0 | | |
| 19 | | 5 | 4 | L3_0 | | |
| 20 | | 6 | 4 | L3_1 | | |
| 21 | | 7 | 3 | L2_1 | | |
| 22 | +----+-----------+------+ | |
| 23 | 7 rows in set (0.00 sec) | |
| 24 | ||
| 25 | mysql> set @t=5; select distinct b.node_id, a.* from a, (select @t:=if(a.id = @t, a.parent_id, @t) as node_id from a order by a.id DESC) as b where a.id=b.node_id; # ищем путь от ноды с индексом 5 до корня, при условии, что дочерний элемент имеет индекс выше, чем свои родитель | |
| 26 | Query OK, 0 rows affected (0.00 sec) | |
| 27 | ||
| 28 | +---------+----+-----------+------+ | |
| 29 | | node_id | id | parent_id | name | | |
| 30 | +---------+----+-----------+------+ | |
| 31 | | 5 | 5 | 4 | L3_0 | | |
| 32 | | 4 | 4 | 2 | L2_0 | | |
| 33 | | 2 | 2 | 1 | L1_0 | | |
| 34 | | 1 | 1 | NULL | ROOT | | |
| 35 | +---------+----+-----------+------+ | |
| 36 | - | 4 rows in set (0.02 sec) |
| 36 | + | 4 rows in set (0.02 sec) |
| 37 | ||
| 38 | mysql> truncate a; | |
| 39 | Query OK, 0 rows affected (0.19 sec) | |
| 40 | ||
| 41 | mysql> insert into a (id, parent_id, name) values (10, NULL, 'ROOT'), (2, 10, 'L1_0'), (7, 10, 'L1_1'), (8, 7, 'L2_0'), (9, 7, 'L2_1'), (6, 9, 'L3_0'), (4, 9, 'L3_1'), (1,6, 'L4_0'), (3,1,'L5_0'); | |
| 42 | Query OK, 9 rows affected (0.13 sec) | |
| 43 | Records: 9 Duplicates: 0 Warnings: 0 | |
| 44 | ||
| 45 | mysql> select* from a; | |
| 46 | +----+-----------+------+ | |
| 47 | | id | parent_id | name | | |
| 48 | +----+-----------+------+ | |
| 49 | | 1 | 6 | L4_0 | | |
| 50 | | 2 | 10 | L1_0 | | |
| 51 | | 3 | 1 | L5_0 | | |
| 52 | | 4 | 9 | L3_1 | | |
| 53 | | 6 | 9 | L3_0 | | |
| 54 | | 7 | 10 | L1_1 | | |
| 55 | | 8 | 7 | L2_0 | | |
| 56 | | 9 | 7 | L2_1 | | |
| 57 | | 10 | NULL | ROOT | | |
| 58 | +----+-----------+------+ | |
| 59 | 9 rows in set (0.00 sec) | |
| 60 | ||
| 61 | mysql> set @t=1; select distinct b.node_id, a.* from a, (select @t:=if(a.id = @t, a.parent_id, @t) as node_id from a order by a.id DESC) as b where a.id=b.node_id; # начиная со второй ноды на пути к корню, условие не соблюдено. поэтому получаем только 2 ноды - осходную и ее родителя. | |
| 62 | Query OK, 0 rows affected (0.00 sec) | |
| 63 | ||
| 64 | +---------+----+-----------+------+ | |
| 65 | | node_id | id | parent_id | name | | |
| 66 | +---------+----+-----------+------+ | |
| 67 | | 1 | 1 | 6 | L4_0 | | |
| 68 | | 6 | 6 | 9 | L3_0 | | |
| 69 | +---------+----+-----------+------+ | |
| 70 | 2 rows in set (0.00 sec) |
