For the composite tree structure of data storage in mysql, the query results are output in the tree structure _ MySQL

For the composite tree structure of data storage in mysql, bitsCN.com is output according to the tree structure during query.

1. main idea: regular data creation based on existing data

Select * FROM (
Select lId, strName, lId as lParentId,-1 as orderIdx from tbClassify WHERE lParentId = 0
UNION ALL
(Select t1. * from tbClassify t1 join
(Select lId from tbClassify where lParentId = 0 order by orderIdx) t2 on
T1.lParentId = t2.lId
Where 1 = 1 order by t1.lParentId, t1.orderIdx)
) TbLast where 1 = 1 group by tbLast. lParentId, tbLast. lId order by tbLast. lParentId, tbLast. orderIdx
;

There is a problem with the sorting above, and the final shape is:

Select lId FROM (
Select lId, orderIdx as pIdx,-1 as sIdx from tbClassify WHERE lParentId = 0
UNION ALL
(Select a. lId, B. orderIdx pIdx, a. orderIdx sIdx from tbClassify a left JOIN tbClassify B on (a. lParentId = B. lId) where a. lParentId! = 0)
)
TbLast order by tbLast. pIdx, tbLast. sIdx
;

Defect of this method: The premise is that the tree structure has several layers without general purpose. The current method is only applicable to two layers of tree structure, with the focus on providing a solution to the problem: create data based on existing data

BitsCN.com