我怎样才能将其转换为mysql查询

How can I convert the following to this kind of MySQL query? I tried it to run in MySQL editor, but it looks like the syntax is different in MySQL.

如何将以下内容转换为此类MySQL查询?我试过它在MySQL编辑器中运行,但看起来MySQL的语法不同。

-- Working Example
;WITH CTECompany
AS
(
    SELECT 
        EmpID, 
        ParentID, 
        PersonName , 
        0 AS HLevel,
        CAST(RIGHT(REPLICATE('_',5) +  CONVERT(VARCHAR(20),EmpID),20) AS VARCHAR(MAX)) AS OrderByField
    FROM @Company
    WHERE ParentID IS NULL

    UNION ALL

    SELECT 
        C.EmpID, 
        C.ParentID, 
        C.PersonName , 
        (CTE.HLevel + 1) AS HLevel,
        CTE.OrderByField + CAST(RIGHT(REPLICATE('_',5) +  CONVERT(VARCHAR(20),C.EmpID),20) AS VARCHAR(MAX)) AS OrderByField
    FROM @Company C
    INNER JOIN CTECompany CTE ON CTE.EmpID = C.ParentID
    WHERE C.ParentID IS NOT NULL
)

-- Working Example
SELECT 
    EmpID
    , ParentID
    , HLevel
    , PersonName
    , (REPLICATE( '----' , HLevel ) + PersonName) AS Person
FROM CTECompany
ORDER BY OrderByField,PersonName;

1 个解决方案

#1

MySQL doesn't have WITH. Just move that query into a subquery, replacing CTECompany in the second part of the query

MySQL没有WITH。只需将该查询移动到子查询中,替换查询的第二部分中的CTECompany即可

SELECT 
    EmpID
    , ParentID
    , HLevel
    , PersonName
    , (REPLICATE( '----' , HLevel ) + PersonName) AS Person
FROM (
        SELECT 
            EmpID, 
            ParentID, 
            PersonName , 
            0 AS HLevel,
            CAST(RIGHT(REPLICATE('_',5) +  CONVERT(VARCHAR(20),EmpID),20) AS VARCHAR(MAX)) AS OrderByField
        FROM Company
        WHERE ParentID IS NULL

        UNION ALL

        SELECT 
            C.EmpID, 
            C.ParentID, 
            C.PersonName , 
            (CTE.HLevel + 1) AS HLevel,
            CTE.OrderByField + CAST(RIGHT(REPLICATE('_',5) +  CONVERT(VARCHAR(20),C.EmpID),20) AS VARCHAR(MAX)) AS OrderByField
        FROM Company C
        INNER JOIN CTECompany CTE ON CTE.EmpID = C.ParentID
        WHERE C.ParentID IS NOT NULL
    )
ORDER BY OrderByField,PersonName;

MySQL also doesn't allow variables to be used as table names, so I changed @Company to Company.

MySQL也不允许将变量用作表名,因此我将@Company更改为Company。

#1