我有一个表示类别层次结构的表,层次结构顶部的元素的父 ID 为 0。
CatID 列中有超过 54K 个唯一 ID。每个 ID 都可以是另一个 ID 的父级。类别有 8 个级别。
表格如下例所示:
CatID ParentID CatName
1 0 Home
. . .
. . .
20 1 Vehicles
. . .
35 20 SUV
36 20 Motorbikes
. . .
90 35 BMW
91 35 Toyota
. . .
234 91 Land Cruiser
这就是我想要达到的结果:
Cat0 Cat1 Cat2 Cat3 Cat4 Cat5 Cat6 Cat7
Home Vehicles SUV Toyota LandCruiser
Home Vehicles SUV BMW
Home Vehilces Motorbikes
. . .
我怎样才能做到这一点?我是否需要某种循环来遍历所有 ID?
有一个类似的问题 https://stackoverflow.com/questions/18427154/sql-hierarchy-loop-query之前问过,所以我使用相同的表结构来解释我的观点,但答案并不完全是我想要的。
有人可以帮忙吗?
这里是:
SELECT
L0.CatName AS Cat0,
L1.CatName AS Cat1,
L2.CatName AS Cat2,
L3.CatName AS Cat3,
L4.CatName AS Cat4,
L5.CatName AS Cat5,
L6.CatName AS Cat6,
L7.CatName AS Cat7
FROM
YourTable AS L0
LEFT JOIN YourTable AS L1
ON L0.CatID = L1.ParentID
LEFT JOIN YourTable AS L2
ON L1.CatID = L2.ParentID
LEFT JOIN YourTable AS L3
ON L2.CatID = L3.ParentID
LEFT JOIN YourTable AS L4
ON L3.CatID = L4.ParentID
LEFT JOIN YourTable AS L5
ON L4.CatID = L5.ParentID
LEFT JOIN YourTable AS L6
ON L5.CatID = L6.ParentID
LEFT JOIN YourTable AS L7
ON L6.CatID = L7.ParentID
WHERE
L0.ParentID = 0
本文内容由网友自发贡献,版权归原作者所有,本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容,请联系:hwhale#tublm.com(使用前将#替换为@)