如何使用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

how to run this query in MySql?

如何在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

1 个解决方案

#1

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:

使用递归语句/方法可以在PostgreSQL和Sybase中使用(我想可能还有更多)，所以您可以查看以下内容:

http://www.artfulsoftware.com/mysqlbook/sampler/mysqled1ch20.html

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的方法(还有一两种PHP的方法，我知道它不在标签列表中)

#1