本文介绍了一种使用SQL实现的设备类型层级查询方法，通过递归查询的方式获取设备类型的层级结构，并展示了如何利用变量来记录层级信息。
SELECT T2.id, T2.typeName , T2.deviceType
FROM (
SELECT
@r AS _id,
(SELECT @r := parentId FROM op_equipment_type WHERE id = _id) AS parentId,
@l := @l + 1 AS lvl
FROM
(SELECT @r := "e05f9e5258ae4bfa93ac451f472cefbb", @l := "0") vars,
op_equipment_type h
WHERE @r <> "0") T1
left JOIN op_equipment_type T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC
FROM (
SELECT
@r AS _id,
(SELECT @r := parentId FROM op_equipment_type WHERE id = _id) AS parentId,
@l := @l + 1 AS lvl
FROM
(SELECT @r := "e05f9e5258ae4bfa93ac451f472cefbb", @l := "0") vars,
op_equipment_type h
WHERE @r <> "0") T1
left JOIN op_equipment_type T2
ON T1._id = T2.id
ORDER BY T1.lvl DESC
扫一扫
798
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
