如何使用递归查询选择数据 [英] how to select data with recursive query

问题描述

我有这个简单的表格，其中包含2列数字和前一个数字，用这个值定义：

number |以前的号码

281 | 0

280 | 281

279 | 280

278 | 279

277 | 278

276 | 277

275 | 276

274 | 275

273 | 274

272 | 273



和以下选择

选择
 tmp.op_nr，
 @op_nr：= tmp.previous_op_nr，
 @depth：= @ depth + 1 
来自
（选择@ op_nr = 280，@ depth：= 0）作为vars，
 dwh .temporary_table tmp 
 
其中tmp.op_nr =@op_nr和
 @ depth< 10; 

并且对于此查询，它应返回

  281  |  0  
  280  |  281  
  279  |  280  

如果我将深度更改为2而@op_nr更改为275

它应该返回类似

  277  |  278  
  276  |  277  
  275  |  276  

但是我在select语句中遗漏了一些内容而且没有这样工作。

我应该做什么修改？

解决方案

你可能想看看使用公用表表达式的递归查询 [ ^ ]。从我从那个页面收集的信息来看，CTE正是您正在寻找的。该页面底部有一个精心设计的例子，它让我觉得它可以解决你的问题。



[已修改]

我意识到你在谈论MySql有点晚了所以我看了一下并发现了这个：在MySQL中管理分层数据 [ ^ ]。您可能想要跳到关于嵌套集模型的部分，因为这似乎比邻接列表模型更适合您的问题。

[/ Modified ]



让我们发布你的发现！ :)



问候，

- Manfred

找到了问题，它是一个拼写，我在这里放了：：

改变了这个

（ select  @ op_nr = 280，@ depth：= 0） as  vars，

to

（选择 @op_nr：= 280，@ depth：= 0） as  vars，

i have this simple table with 2 columns "number" and "previous number"defined with this values:
number | previous number
281 | 0
280 | 281
279 | 280
278 | 279
277 | 278
276 | 277
275 | 276
274 | 275
273 | 274
272 | 273

and the following select

select 
    tmp.op_nr,
    @op_nr := tmp.previous_op_nr,
    @depth:=@depth+1
from 
     (select @op_nr=280, @depth:=0 ) as vars,
    dwh.temporary_table tmp
   
where tmp.op_nr=@op_nr and 
    @depth<10;

and for this query it should return

281 | 0
280 | 281
279 | 280

if i change the depth to 2 and @op_nr to 275
it should return something like

277 | 278
276 | 277
275 | 276

but i am missing something in the select statement and doesn''t work like this.
what modification should i make?

解决方案

You may want to look into Recursive Queries Using Common Table Expressions[^]. From what I gathered from that page CTEs are exactly what you are looking for. There is an elaborate example at the bottom of that page that looks to me as if it would solve your problem.

[Modified]
I realised a bit late that you are talking about MySql, so I had a look and turned up this: Managing Hierarchical Data in MySQL[^]. You may want to skip down to the part about "The Nested Set Model" as this seems to be a better fit to your problem than the "The Adjacency List Model".
[/Modified]

Keep us posted of your findings! :)

Regards,

— Manfred

found the problem, it''s a spelling one, i have put ":" here:
changed this

(select @op_nr=280, @depth:=0 ) as vars,

to

(select @op_nr:=280, @depth:=0 ) as vars,

这篇关于如何使用递归查询选择数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！