如何在两张关联表中查询三级结构的顶层金额汇总数据？

现有两张表，Table A为三级结构，表结构如下：

|    id    |   name    |  level   | up_level_id |
| :------- | :-------: | :------: |  ----------:|
|      1   | lv1_name1 |     1    |   null      |
|      2   | lv1_name2 |     1    |   null      |
|      3   | lv2_name1 |     2    |   1         |
|      4   | lv2_name2 |     2    |   2         |
|      5   | lv3_name1 |     3    |   3         |
|      6   | lv3_name2 |     3    |   3         |
|      7   | lv3_name3 |     3    |   4         |
|      8   | lv3_name4 |     3    |   4         |

Table B表结构如下：

| amount   |    org_id    |
| -------- | --------     |
| 12,000   |      5       |
| 15,000   |      6       |
| 20,000   |      7       |
| 18,000   |      8       |

Table A与Table B可通过A.id = B.org_id关联，且仅Table A的level-3层级对应Table B的金额数据。需要查询顶层（level-1）名称及对应金额汇总，期望结果如下：

| sum_amount |  top_lvl_name |
| --------   | --------      |
| 27,000     |   lv1_name1   |
| 38,000     |   lv1_name2   |

测试时，已实现通过Table A中level-3的id查询对应level-1名称的SQL：

SELECT name
FROM TableA
WHERE id IN (
    SELECT up_level_id
    FROM TableA
    WHERE id IN (
        SELECT up_level_id
        FROM TableA
        WHERE id=5) -- 查询id为5的顶层名称
    );

但关联两张表时编写的SQL无结果，请问如何修正该SQL以得到正确的查询结果？

你之前的子查询仅能查询单个level-3节点的顶层名称，要关联Table B并完成汇总，需要把层级关联逻辑和金额统计结合，以下两种方法均可实现：

方法1：多次自连接（推荐） #

通过两次自连接直接关联level-3、level-2、level-1的记录，再和Table B关联求和，逻辑清晰且性能更优：

SELECT 
    TO_CHAR(SUM(REPLACE(b.amount, ',', '')::DECIMAL), 'FM999,999') AS sum_amount,
    a1.name AS top_lvl_name
FROM TableB b
JOIN TableA a3 ON b.org_id = a3.id AND a3.level = 3
JOIN TableA a2 ON a3.up_level_id = a2.id AND a2.level = 2
JOIN TableA a1 ON a2.up_level_id = a1.id AND a1.level = 1
GROUP BY a1.name
ORDER BY a1.name;

方法2：适配原嵌套子查询逻辑 #

如果要沿用你原来的嵌套子查询思路，需将硬编码的id=5改为动态引用b.org_id，同时结合分组汇总：

SELECT 
    TO_CHAR(SUM(REPLACE(b.amount, ',', '')::DECIMAL), 'FM999,999') AS sum_amount,
    (SELECT name 
     FROM TableA 
     WHERE id = (SELECT up_level_id 
                 FROM TableA 
                 WHERE id = (SELECT up_level_id 
                             FROM TableA 
                             WHERE id = b.org_id))) AS top_lvl_name
FROM TableB b
GROUP BY top_lvl_name
ORDER BY top_lvl_name;

关键注意点 #

1. amount字段带千分位逗号，必须先用REPLACE去除逗号再转为数值类型，否则字符串无法正确求和；最后用TO_CHAR格式化回带逗号的格式，匹配期望结果。
2. 原嵌套子查询硬编码了id=5，关联Table B时需改为动态获取b.org_id，否则无法覆盖所有level-3节点。

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