请求协助编写SQL层级查询：合并父节点下属子节点展示

针对需求，我们需要从Hierarchy表中聚合指定父节点的所有子节点，以下是不同数据库环境下的实现方案：

MySQL/MariaDB #

使用GROUP_CONCAT函数实现子节点合并：

SELECT 
    FATHER_KEY AS Fatherkey,
    GROUP_CONCAT(CHILD_KEY SEPARATOR ', ') AS subordinates
FROM 
    Hierarchy
WHERE 
    FATHER_KEY = 'Rajachola1'
GROUP BY 
    FATHER_KEY;

SQL Server/Azure SQL #

使用STRING_AGG函数（适用于SQL Server 2017及以上版本）：

SELECT 
    FATHER_KEY AS Fatherkey,
    STRING_AGG(CHILD_KEY, ', ') AS subordinates
FROM 
    Hierarchy
WHERE 
    FATHER_KEY = 'Rajachola1'
GROUP BY 
    FATHER_KEY;

Oracle #

使用LISTAGG函数：

SELECT 
    FATHER_KEY AS Fatherkey,
    LISTAGG(CHILD_KEY, ', ') WITHIN GROUP (ORDER BY CHILD_KEY) AS subordinates
FROM 
    Hierarchy
WHERE 
    FATHER_KEY = 'Rajachola1'
GROUP BY 
    FATHER_KEY;

PostgreSQL #

使用STRING_AGG函数：

SELECT 
    FATHER_KEY AS Fatherkey,
    STRING_AGG(CHILD_KEY, ', ') AS subordinates
FROM 
    Hierarchy
WHERE 
    FATHER_KEY = 'Rajachola1'
GROUP BY 
    FATHER_KEY;

以上查询会将Rajachola1作为Fatherkey列的值，其对应的所有子节点（Rajendra1、Rajendra2、Rajendra3）会以逗号分隔的形式合并到subordinates列中。

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