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 ont1.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;
上面的排序有问题,最后成型:
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;
该方法缺陷:前提必须知道树形结构共有几层,不带有通用型,当前方法只适用于两层树形结构,重点是提供了一种解决问题的思路:根据已有数据造数据