如何使用ORDER BY CASE实现按地区层级及下属农场排序

你的需求是实现地区层级从高到低嵌套排序（全球→大洲→国家→地区→农场），同时子节点紧跟对应父节点。当前的SQL仅按type的层级排序，会把所有同类型的节点放在一起，无法实现嵌套效果，需要结合递归查询生成排序路径，再配合CASE使用。

完整SQL代码 #

WITH RECURSIVE farm_hierarchy AS (
    -- 锚点：获取根节点（全球级）
    SELECT 
        id, 
        name, 
        type, 
        parent,
        -- 初始化排序路径：层级优先级 + 名称，确保同层级按名称排序
        CAST(CASE 
            WHEN type = 'global' THEN 1
            WHEN type = 'continent' THEN 2
            WHEN type = 'country' THEN 3
            WHEN type = 'region' THEN 4
            ELSE 5 
        END || '-' || name AS VARCHAR(255)) AS sort_path
    FROM farm_components
    WHERE type = 'global' -- 若根节点parent为NULL，可改为WHERE parent IS NULL
    UNION ALL
    -- 递归：获取子节点，拼接父节点的排序路径
    SELECT 
        fc.id, 
        fc.name, 
        fc.type, 
        fc.parent,
        CAST(fh.sort_path || '-' || CASE 
            WHEN fc.type = 'global' THEN 1
            WHEN fc.type = 'continent' THEN 2
            WHEN fc.type = 'country' THEN 3
            WHEN fc.type = 'region' THEN 4
            ELSE 5 
        END || '-' || fc.name AS VARCHAR(255)) AS sort_path
    FROM farm_components fc
    JOIN farm_hierarchy fh ON fc.parent = fh.id
    WHERE fc.type IN ('continent', 'country', 'region', 'farm')
)
SELECT id, name, type, parent
FROM farm_hierarchy
ORDER BY sort_path;

代码说明 #

1. 递归CTE的作用：
   • 锚点部分先抓取最顶层的全球节点，生成初始排序路径，路径包含用CASE生成的层级优先级（1=全球，2=大洲，以此类推）和节点名称，保证同层级内按名称排序。
   • 递归部分通过parent = id关联父节点，把父节点的路径和当前节点的层级、名称拼接起来，形成完整的嵌套路径。
2. 最终排序：按生成的sort_path排序，就能实现你想要的嵌套效果——全球下面紧跟所属大洲，大洲下面紧跟所属国家，以此类推，每个父节点的子节点都会紧随其后。

如果你的数据中根节点（全球）的parent字段为NULL，记得把锚点部分的WHERE type = 'global'改成WHERE parent IS NULL，适配你的数据结构。

内容的提问来源于stack exchange，提问作者Scott Paterson