duanjiu6697
duanjiu6697
2016-08-05 11:25

Mysql递归查询无法在MariaDB上运行

已采纳

I have a table "knowledgemodel" [enter image description here][1]

My Query

SELECT 
  title, id, @parent:=parent_id as prnt
FROM
(SELECT @parent:=3 ) a
JOIN
(SELECT * FROM knowledgemodel ORDER BY id DESC) b
where 
@WHERE=id

enter image description here

IN MYsql Server: 127.0.0.1 via TCP/IP Server type: MySQL Server version: 5.6.16 - MySQL Community Server (GPL) Protocol version: 10 Above Query was working fine.

But on MariaDB im getting only one record enter image description here

Any help ??

  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 复制链接分享
  • 邀请回答

1条回答

  • douyan4470 douyan4470 5年前

    I found the problem and fix the issue.

    SELECT T2.id, T2.title,T2.parent_id as prnt

    FROM (

    SELECT @r AS _id,

    (SELECT @r := parent_id FROM knowledgemodel WHERE id = _id) AS parent_id,

    @l := @l + 1 AS lvl

    FROM (

    SELECT @r := ".$value['path'].", @l := 0) vars, knowledgemodel m WHERE @r <> 0)

    T1 JOIN knowledgemodel T2 ON T1._id = T2.id ORDER BY T1.id asc

    The LNine SELECT "$value['path']" @r := 5 is the ID . The result is as follows:

    点赞 评论 复制链接分享

为你推荐