关于sqlserver数据库中的with as 语句使用union all提问

Question

关于sqlserver数据库中的with as 语句使用union all提问

目前工作中需要把一些存储过程从sqlserver迁到mysql，不熟悉sqlserver，遇到这样的一条语句
我不知道这条语句是怎么执行的

WITH DEPT AS
        (select cost_name,department_id,cost_code,parent_cost_code
            from 表2
            where cost_code  in ('t')
            UNION ALL
            SELECT c.cost_name,c.department_id,c.cost_code,c.parent_cost_code
            FROM DEPT d,表2 C 
            WHERE d.cost_code = c.parent_cost_code)
            select count(*) from DEPT；

运行结果条数是1100

我的解答思路和尝试过的方法

我自己研究一天也百度过，硬是找不到方法
写了一条mysql的

SELECT COUNT(*) FROM (
                SELECT cost_name,department_id,cost_code
                FROM 表2
                WHERE cost_code  IN ('t')
                UNION ALL            
                SELECT c.cost_name,c.department_id,c.cost_code
                FROM
               (SELECT cost_code FROM 表2 WHERE cost_code  IN ('t')) d,bm_report_cost2 C 
                WHERE d.cost_code = c.parent_cost_code) AS s;

结果只有102条数据

他们的表数据是一致的，结果中sqlserver的cost_code字段有‘t’还有其他。mysql中的cost_code字段只有‘t’。
我把mysql的第二条WHERE cost_code IN ('t') 去掉后又比sqlserver的多了500条数据

现在我想他们出来的结果一致，mysql这边应该怎么写

Answer 1

这种是CTE递归实现，mysql需要8.x才支持这种写法，而且mysql的CTE递归需要加RECURSIVE

WITH RECURSIVE DEPT AS
        (select cost_name,department_id,cost_code,parent_cost_code
            from 表2
            where cost_code  in ('t')
            UNION ALL
            SELECT c.cost_name,c.department_id,c.cost_code,c.parent_cost_code
            FROM DEPT d,表2 C 
            WHERE d.cost_code = c.parent_cost_code)
            select count(*) from DEPT；