如何将MSSQL CTE查询转换为MySQL? [英] How to transform a MSSQL CTE query to MySQL?

问题描述

在我的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(公用表表达式).这是国际海事组织早该采取的行动.通常，您可以只使用子查询来代替，但是这种特殊的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?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！