下级绩效mysql

有个场景mysql:
现有张表四个字段: id 、name 、parent id(上级id) 、level (当前层级，最多5级)，这张表是用来维护线下BD的关系。总共这些线下BD可以不断发展，从顶层往下最多5级。现针对这些BD来计算绩效，另有一张交易表，表里存储这些BD的id需求是: 假设我是一个顶层，我下面有个人A，A下面还有三级，算我的绩效时候，除了计算我自身之外，还有把我下面A及A下面的所有人的绩效聚合起来作为我的业绩，算A绩效的时候，也是要带上他本人及他下面所有人的绩效。

-- CREATE TABLE BD (
--   id INT PRIMARY KEY,
--   name VARCHAR(255),
--   parent_id INT,
--   level INT
-- );
-- CREATE TABLE Transaction (
--   BD_id INT,
--   amount DECIMAL(10, 2)
-- );
-- 
-- INSERT INTO BD (id, name, parent_id, level) VALUES
--   (1, '顶层', NULL, 1),
--   (2, 'A', 1, 2),
--   (3, 'B', 2, 3),
--   (4, 'C', 2, 3),
--   (5, 'D', 2, 3),
--   (6, 'E', 3, 4),
--   (7, 'F', 3, 4),
--   (8, 'G', 4, 4),
--   (9, 'H', 5, 4),
--   (10, 'I', 5, 4),
--   (11, 'J', 6, 5),
--   (12, 'K', 6, 5),
--   (13, 'L', 6, 5),
--   (14, 'M', 7, 5),
--   (15, 'N', 8, 5);
-- 
-- INSERT INTO Transaction (BD_id, amount) VALUES
--   (1, 1000),
--   (2, 500),
--   (3, 200),
--   (4, 300),
--   (5, 400),
--   (6, 100),
--   (7, 150),
--   (8, 250),
--   (9, 200),
--   (10, 300),
--   (11, 50),
--   (12, 100),
--   (13, 80),
--   (14, 70),
--   (15, 120);

SELECT
  temp_table.root_id,
  SUM(temp_table.performance) AS total_performance
FROM (
  WITH RECURSIVE hierarchy AS (
    SELECT BD.id, BD.name, BD.parent_id, 1 AS level, BD.id AS root_id
    FROM BD
    UNION ALL
    SELECT BD.id, BD.name, BD.parent_id, hierarchy.level + 1, hierarchy.root_id
    FROM BD
    JOIN hierarchy ON hierarchy.id = BD.parent_id
  )
  SELECT
    hierarchy.root_id,
    hierarchy.id,
    hierarchy.name,
    Transaction.amount AS performance
  FROM
    hierarchy
  JOIN
    `Transaction` ON hierarchy.id = `Transaction`.BD_id
  JOIN
    BD ON hierarchy.root_id = BD.id
) AS temp_table
GROUP BY
  temp_table.root_id
ORDER BY
  temp_table.root_id ASC;