Question

我的MySQL schema中,我有<编码>(同上,括号,姓名)表。

在MSSQL中,我有CTE问(从底层为供应型国际发展法建设一类树):

with CTE (id, pid, name) 
as
(
    select id, parentid as pid,name
    from category
    where id = 197
      union all
        select CTE.pid as id , category.parentid as pid, category.name
        from CTE 
          inner join category 
            on category.id = CTE.pid
 )
 select * from CTE

如何将这一问答改为我的SQL?

Answer 1

不幸的是,MySQL不支持CTE(共同表格表达)。海事组织早该这样做。通常,你只能使用一个子座,但这一特殊电传是recursive:指自己在电离层。奖励CTE对于等级数据极为有用,但同样:MySql根本不支持这些数据。你们必须执行储存程序,以便取得同样的结果。

A previous answer of mine should provide a good starting point:

Generating 源自MySQL(无CTEs)的等级数据()的深植树

Answer 2

感谢的是,由于MySQL从8.0.1 _supports C 。

Answer 3

unfortunately MYSQl or XAMPP(MARIADB) mysql doesnot support CTEs(COMMON TABLE EXPRESSIONS), for the same you will have to use nested queries.

详情请上点:

https://mariadb.com/kb/en/library/with

Answer 4

Please check what version of MySQL you have using SELECT VERSION(); If it is 8 or above you can then proceed further with my comment. With MySQL 8.0, MariaDB 10.2, and later versions, you can use recursive CTEs:

WITH RECURSIVE CTE (id, pid, name) AS (
    select id, parentid as pid,name
from category
where id = 197
  union all
    select CTE.pid as id , category.parentid as pid, category.name
    from CTE 
      inner join category 
        on category.id = CTE.pid
)
select * from CTE ;

请注意,技转中心受MySQL和MariaDB的Cte_max_recursion_ deep(default1000, max 4,294,967,295 (232-1)和最大程度_recursive_iterations(default 4,294,967,295)的限制。

可以通过执行:

SET cte_max_recursion_depth = 4294967295;

这只会影响你的本届会议,而且会持续下去。

友情链接