In the same table, this record. Pid=father. Id, parent record. Pid=FuFu. Id level 3 until the pid=0
Want to ask, if there is a field in the table, how can I get all the parent node, and the sum

CodePudding user response:

Write a recursive function to the pid=0 by manual input pid, output all the parent node

CodePudding user response:

The structure of the data table:
Id name parent_id
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- --
Home 0
About 1
Contact 1
Legal 2
Privacy 4
Products 1
Support 1

The SELECT T2. Id, T2. The name
The FROM (
SELECT
@ r AS _id,
: (SELECT @ r=parent_id FROM table1 WHERE id=_id) AS parent_id
The FROM
: (SELECT @ r=5, @ : l=0) vars, - query id for all 5 superior
Table1 h
WHERE @ r & lt;> 0) T1
The JOIN table1 T2
ON T1._id=T2.id