问题描述
在我的 MySQL 架构中，我有 category(id, parentid, name) 表
in my MySQL schema, I have the category(id, parentid, name) table
在 MSSQL 中，我有那个 CTE 查询(为提供的类别 ID 自下而上构建类别树:
In the MSSQL, I have that CTE query (to build a category tree from the bottom up for a supplied category 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
如何将该查询转换"为 MySQL?
How to 'transform' that query to MySQL ?
推荐答案
遗憾的是 MySQL 不支持 CTE(通用表表达式).这是早就应该的 IMO.通常，您可以只使用子查询，但这个特殊的 CTE 是递归:它在查询中引用自身.递归 CTE 对于分层数据非常有用，但同样:MySql 根本不支持它们.您必须实现一个存储过程才能获得相同的结果.
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.
我之前的回答应该提供一个很好的起点:
A previous answer of mine should provide a good starting point:
从 MySQL 中的分层数据生成基于深度的树(无 CTE)>
这篇关于如何将 MSSQL CTE 查询转换为 MySQL?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！