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

我有一个表“knowledgemodel”
[在此输入图像描述] [1] </ p>

我的查询</ p>

  SELECT 
title,id,@ parent:= parent_id as prnt
FROM
(SELECT @ parent:= 3)a
JOIN

(SELECT * FROM knowledgemodel ORDER BY id DESC)b
其中
@ WHERE = id
</ code> </ pre>

</ p>

在MYsql
Server:127.0.0.1中通过TCP / IP
Server类型:MySQL
Server版本: 5.6.16 - MySQL社区服务器(GPL)
协议版本:10
以上查询工作正常。</ p>

但在MariaDB上我只获取一条记录 </ p>

任何帮助?? </ p>
</ div>

展开原文

原文

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 ??

douju5062
douju5062 获取所有行似乎非常低效,然后按id过滤。将WHEREid=@where移动到第二个子查询中。然后意识到你不需要它作为子查询。
大约 4 年之前 回复
dongyan1993
dongyan1993 不需要ON。这是一个CROSSJOIN,其中第一个表仅用于初始化@parent。
大约 4 年之前 回复
dtvfshi5248
dtvfshi5248 我不确定但是你不能错过JOIN之后的ON声明吗?
大约 4 年之前 回复

1个回答

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:

Csdn user default icon
上传中...
上传图片
插入图片
抄袭、复制答案,以达到刷声望分或其他目的的行为,在CSDN问答是严格禁止的,一经发现立刻封号。是时候展现真正的技术了!
立即提问