如何将含变量的MySQL查询转换为PostgreSQL非递归查询

需求：将使用用户变量的MySQL层级查询转换为PostgreSQL语句，已知查询最大深度为10，无需递归实现。

原MySQL查询代码 #

select
       name
from
       (
              select
                     @lidx := field(
                            coalesce(
                                   @root_id, coalesce(p10.id,p9.id,p8.id,p7.id,p6.id,p5.id,p4.id,p3.id,p2.id,p1.id)
                            ),
                            p1.id,p2.id,p3.id,p4.id,p5.id,p6.id,p7.id,p8.id,p9.id,p10.id
                     ) as leftmost,
                     concat(
                            if(@lidx >= 10, rpad(p10.name, 24, ' '), ''),
                            if(@lidx >= 9, rpad(p9.name, 24, ' '), ''),
                            if(@lidx >= 8, rpad(p8.name, 24, ' '), ''),
                            if(@lidx >= 7, rpad(p7.name, 24, ' '), ''),
                            if(@lidx >= 6, rpad(p6.name, 24, ' '), ''),
                            if(@lidx >= 5, rpad(p5.name, 24, ' '), ''),
                            if(@lidx >= 4, rpad(p4.name, 24, ' '), ''),
                            if(@lidx >= 3, rpad(p3.name, 24, ' '), ''),
                            if(@lidx >= 2, rpad(p2.name, 24, ' '), ''),
                            rpad(p1.name, 24, ' ')
                     ) as locator,
                     -- Prepend a tab to the name for every level of the tree after the root.
                     concat(repeat('\t', @lidx - 1), p1.name) as name
              from
                     (
                            -- Declare constants
                            select
                                   -- company_id. Leave null for all companies
                                   @company_id := null,
                                   -- root_id. Leave null for all departments
                                   @root_id := null
                     ) sqlVars,
                     hierarchy p1
                     -- repeatedly left join until the desired max depth (10)
                     left join hierarchy p2 on p2.id = p1.parent_id
                     left join hierarchy p3 on p3.id = p2.parent_id
                     left join hierarchy p4 on p4.id = p3.parent_id
                     left join hierarchy p5 on p5.id = p4.parent_id
                     left join hierarchy p6 on p6.id = p5.parent_id
                     left join hierarchy p7 on p7.id = p6.parent_id
                     left join hierarchy p8 on p8.id = p7.parent_id
                     left join hierarchy p9 on p9.id = p8.parent_id
                     left join hierarchy p10 on p10.id = p9.parent_id
              where
                     (      -- filter on company_id if non null
                            @company_id is null
                            or @company_id = p1.company_id
                     )
                     and (  -- filter on root_id if non null
                            @root_id is null
                            or @root_id in (p1.id,p2.id,p3.id,p4.id,p5.id,p6.id,p7.id,p8.id,p9.id,p10.id)
                     )
              -- alpha ordering
              order by 
                     locator
       ) flattened;

转换后的PostgreSQL查询代码 #

WITH sql_vars AS (
    SELECT 
        NULL::INT AS company_id,  -- 留空表示查询所有公司
        NULL::INT AS root_id      -- 留空表示查询所有部门
)
SELECT name
FROM (
    SELECT
        -- 替代MySQL的FIELD函数，找到匹配的层级位置
        CASE
            WHEN sv.root_id IS NOT NULL THEN
                CASE sv.root_id
                    WHEN p1.id THEN 1
                    WHEN p2.id THEN 2
                    WHEN p3.id THEN 3
                    WHEN p4.id THEN 4
                    WHEN p5.id THEN 5
                    WHEN p6.id THEN 6
                    WHEN p7.id THEN 7
                    WHEN p8.id THEN 8
                    WHEN p9.id THEN 9
                    WHEN p10.id THEN 10
                    ELSE 0
                END
            ELSE
                COALESCE(
                    CASE WHEN p10.id IS NOT NULL THEN 10 END,
                    CASE WHEN p9.id IS NOT NULL THEN 9 END,
                    CASE WHEN p8.id IS NOT NULL THEN 8 END,
                    CASE WHEN p7.id IS NOT NULL THEN 7 END,
                    CASE WHEN p6.id IS NOT NULL THEN 6 END,
                    CASE WHEN p5.id IS NOT NULL THEN 5 END,
                    CASE WHEN p4.id IS NOT NULL THEN 4 END,
                    CASE WHEN p3.id IS NOT NULL THEN 3 END,
                    CASE WHEN p2.id IS NOT NULL THEN 2 END,
                    1
                )
        END AS leftmost,
        -- 拼接排序用的locator，用CASE替代MySQL的IF
        CONCAT(
            CASE WHEN leftmost >= 10 THEN RPAD(p10.name, 24, ' ') ELSE '' END,
            CASE WHEN leftmost >= 9 THEN RPAD(p9.name, 24, ' ') ELSE '' END,
            CASE WHEN leftmost >= 8 THEN RPAD(p8.name, 24, ' ') ELSE '' END,
            CASE WHEN leftmost >= 7 THEN RPAD(p7.name, 24, ' ') ELSE '' END,
            CASE WHEN leftmost >= 6 THEN RPAD(p6.name, 24, ' ') ELSE '' END,
            CASE WHEN leftmost >= 5 THEN RPAD(p5.name, 24, ' ') ELSE '' END,
            CASE WHEN leftmost >= 4 THEN RPAD(p4.name, 24, ' ') ELSE '' END,
            CASE WHEN leftmost >= 3 THEN RPAD(p3.name, 24, ' ') ELSE '' END,
            CASE WHEN leftmost >= 2 THEN RPAD(p2.name, 24, ' ') ELSE '' END,
            RPAD(p1.name, 24, ' ')
        ) AS locator,
        -- 生成带缩进的名称
        CONCAT(REPEAT(E'\t', leftmost - 1), p1.name) AS name
    FROM sql_vars sv
    CROSS JOIN hierarchy p1
    LEFT JOIN hierarchy p2 ON p2.id = p1.parent_id
    LEFT JOIN hierarchy p3 ON p3.id = p2.parent_id
    LEFT JOIN hierarchy p4 ON p4.id = p3.parent_id
    LEFT JOIN hierarchy p5 ON p5.id = p4.parent_id
    LEFT JOIN hierarchy p6 ON p6.id = p5.parent_id
    LEFT JOIN hierarchy p7 ON p7.id = p6.parent_id
    LEFT JOIN hierarchy p8 ON p8.id = p7.parent_id
    LEFT JOIN hierarchy p9 ON p9.id = p8.parent_id
    LEFT JOIN hierarchy p10 ON p10.id = p9.parent_id
    WHERE
        (sv.company_id IS NULL OR sv.company_id = p1.company_id)
        AND (sv.root_id IS NULL OR sv.root_id IN (p1.id,p2.id,p3.id,p4.id,p5.id,p6.id,p7.id,p8.id,p9.id,p10.id))
    ORDER BY locator
) flattened;

关键转换说明 #

• 用户变量替换：PostgreSQL没有MySQL风格的@变量，用WITH子句定义常量替代原sqlVars子查询。
• FIELD函数替代：PostgreSQL无FIELD函数，用嵌套CASE语句实现匹配层级位置的逻辑。
• IF函数替换：用PostgreSQL标准的CASE表达式替代MySQL的IF函数。
• 字符串转义：PostgreSQL中制表符需用E'\t'表示，替代MySQL的'\t'。
• JOIN逻辑：保留原有的多次LEFT JOIN结构，确保层级关联逻辑与原查询一致。

内容的提问来源于stack exchange，提问作者Yaswanth Kumar