MySql不写存储过程一步得出顶级节点的方法

版权声明:本文为博主原创文章,未经博主允许不得转载。 https://blog.csdn.net/qq_34824564/article/details/79978655
 String sql = "SELECT T2.channel_id, T2.channel_path FROM ( SELECT @r AS _id, (SELECT @r \\:= parent_id" +
                " FROM jc_channel WHERE channel_id = _id) AS parent_id, @l \\:= @l + 1 AS lvl " +
                " FROM (SELECT @r \\:= "+channel_id+", @l \\:= 0) vars, jc_channel h WHERE @r <> 0) T1 JOIN jc_channel T2 " +
                " ON T1._id = T2.channel_id ORDER BY T1.lvl DESC;";

在这段SQL中jc_channel即为需要查询顶级节点的表,该表中的channel_id存在有父级节点parent_id这一属性,当channel_id有多重父节点时,使用该语句即可得出顶级节点,顶级节点经过排序后在最顶层。

PS:代码中的\\:=等价于:=,之所以要加\\是因为使用hibrate查询时,查询语句不能识别:=这一赋值写法,需要转义。

阅读更多
想对作者说点什么?

博主推荐

换一批

没有更多推荐了,返回首页