如何将 MSSQL CTE 查询转换为 MySQL？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/8833535/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to transform a MSSQL CTE query to MySQL?
提问by Tony
in my MySQL schema, I have the category(id, parentid, name)table
在我的 MySQL 模式中，我有category(id, parentid, name)表
In the MSSQL, I have that CTE query (to build a category tree from the bottom up for a supplied category ID:
在 MSSQL 中，我有那个 CTE 查询（为提供的类别 ID 自下而上构建类别树：
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
How to 'transform' that query to MySQL ?
如何将该查询“转换”为 MySQL？
回答by Jon Black
Unfortunately MySQL doesn't support CTE (Common Table Expressions). This is long overdue IMO. Often, you can just use a subquery instead, but this particular CTE is recursive: it refers to itself inside the query. Recursive CTE's are extremely useful for hierarchical data, but again: MySql doesn't support them at all. You have to implement a stored procedure to get the same results.
不幸的是，MySQL 不支持 CTE（通用表表达式）。这是早该 IMO。通常，您可以只使用子查询，但这个特定的 CTE 是递归的：它在查询中引用自身。递归 CTE 对于分层数据非常有用，但同样：MySql 根本不支持它们。您必须实现一个存储过程才能获得相同的结果。
A previous answer of mine should provide a good starting point:
我以前的回答应该提供一个很好的起点：
Generating Depth based tree from Hierarchical Data in MySQL (no CTEs)
回答by Hubbitus
Thankfully it's not necessary anymore, as MySQL starting from 8.0.1supports CTE.
回答by NupzNupz
unfortunately MYSQl or XAMPP(MARIADB) mysql doesnot support CTEs(COMMON TABLE EXPRESSIONS), for the same you will have to use nested queries.
不幸的是 MYSQl 或 XAMPP(MARIADB) mysql 不支持 CTEs(COMMON TABLE EXPRESSIONS)，同样你将不得不使用嵌套查询。
for more information click on the below link:-
欲了解更多信息，请点击以下链接：-
