Oracle的connect by prior如何在MySql中表达

Question

select user_id from t_a_user where user_type <= 80 connect by prior user_id = leader_id start with user_id = ？;

Answer 1

你好，我是做PostgreSQL数据库内核开发，这个是Oracle的层次查询语句，可以使用MySQL的递归查询来实现，即：with子句

下面是改造的示例：

-- Oracle

SQL> select keyid,parent_keyid,name,prior name,salary
  2       from player
  3       start with keyid=1
  4       connect by prior keyid = parent_keyid;

-- PostgreSQL

postgres=# with recursive search_graph(keyid,parent_keyid,name,salary,sex,seq) as (
postgres(# select keyid,parent_keyid,name,salary,sex,array[row(keyid,parent_keyid)] from player p where keyid = 1
postgres(# union all
postgres(# select p.*,seq || array[row(p.keyid,p.parent_keyid)] from player p, search_graph sg where p.parent_keyid = sg.keyid
postgres(# ) select * from search_graph order by seq;

你可以参考这个例子，在MySQL进行类似改写即可。