项目表中的每个项目都有一个部门编号，每个部门编号最多可以有3个祖父母。现在，我需要显示每个项目及其部门和家长。
我做了3个简单的联接，就成功了。
SELECT tDepartment.id, tDepartment.Name, tDepartment_1.Name AS Parent1, tDepartment_2.Name AS Parent2, tDepartment_3.Name AS Parent3
FROM tDepartment AS tDepartment_3 RIGHT OUTER JOIN
tDepartment LEFT OUTER JOIN
tDepartment AS tDepartment_1 ON tDepartment.ParentID = tDepartment_1.id LEFT OUTER JOIN
tDepartment AS tDepartment_2 ON tDepartment_1.ParentID = tDepartment_2.id ON tDepartment_3.id = tDepartment_2.ParentID
ORDER BY tDepartment.Name
我的问题是，是否可以通过使用CTE或其他任何想法以更清洁的方式完成？
为什么要左右混合？
SELECT tDepartment.id, tDepartment.Name
,tDepartment_1.Name AS Parent1
,tDepartment_2.Name AS Parent2
,tDepartment_3.Name AS Parent3
FROM tDepartment
LEFT OUTER JOIN tDepartment AS tDepartment_1
ON tDepartment.ParentID = tDepartment_1.id
LEFT OUTER JOIN tDepartment AS tDepartment_2
ON tDepartment_1.ParentID = tDepartment_2.id
LEFT OUTER JOINtDepartment AS tDepartment_3
ON tDepartment_3.id = tDepartment_2.ParentID
ORDER BY tDepartment.Name
本文收集自互联网，转载请注明来源。
如有侵权，请联系[email protected] 删除。
我来说两句