如何在MySql中使用递归查询? [英] how to work with recursive query in MySql?
本文介绍了如何在MySql中使用递归查询?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!
问题描述
WITH RECURSIVE transitive_closure(a, b, distance, path_string) AS
( SELECT a, b, 1 AS distance,
a || '.' || b || '.' AS path_string,
b AS direct_connection
FROM edges2
WHERE a = 1 -- set the starting node
UNION ALL
SELECT tc.a, e.b, tc.distance + 1,
tc.path_string || e.b || '.' AS path_string,
tc.direct_connection
FROM edges2 AS e
JOIN transitive_closure AS tc ON e.a = tc.b
WHERE tc.path_string NOT LIKE '%' || e.b || '.%'
AND tc.distance < 3
)
SELECT * FROM transitive_closure
--WHERE b=3 -- set the target node
ORDER BY a,b,distance
如何在MySql中运行此查询?
how to run this query in MySql?
它将显示如下错误消息:
it will show error message like this:
#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'RECURSIVE transitive_closure(a, b, distance, path_string) AS ( SELECT a, b, 1 A' at line 1
推荐答案
WITH RECURSIVE
语句/方法适用于PostgreSQL和Sybase(我想可能还有更多),所以也许您可以看一下:
The WITH RECURSIVE
statement/method is applicable in PostgreSQL and Sybase (and maybe a few more, I think), so maybe you can look at this instead:
http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html
它应该向您展示一些使用MySQL的方法(以及PHP中的一两个方法,我只知道它不在您的标签列表中)
It should show you some approaches using MySQL (and one or two in PHP, just to mention -- I know it's not in your tag list)
这篇关于如何在MySql中使用递归查询?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!
查看全文