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查询时,查询语句不能识别:=这一赋值写法,需要转义。