将GnuCash的Accounts层级表转为扁平表的SQL优化方案问询

我需要在MariaDB（版本10.11.4-MariaDB-1:10.11.4+maria~ubu2204）中为GnuCash数据库生成报表，目标是把accounts层级表转换为扁平表。accounts表中每个账号包含ID（guid）和父级ID（parent_guid），顶级账号的account_type为'ROOT'，名称为'Root Account'。已知从顶级账号向下最多有4级子账号（含顶级共5级），扁平表需要包含以下列：

• 账号ID（guid）
• 账号名称（name）
• Level 1（顶级下第1级账号名称）
• Level 2（顶级下第2级账号名称）
• Level 3（顶级下第3级账号名称）
• Level 4（顶级下第4级账号名称）

accounts表结构 #

CREATE TABLE `accounts` (
    `guid` VARCHAR(32) NOT NULL COLLATE 'utf8mb3_general_ci',
    `name` VARCHAR(2048) NOT NULL COLLATE 'utf8mb3_general_ci',
    `account_type` VARCHAR(2048) NOT NULL COLLATE 'utf8mb3_general_ci',
    `commodity_guid` VARCHAR(32) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `commodity_scu` INT(11) NOT NULL,
    `non_std_scu` INT(11) NOT NULL,
    `parent_guid` VARCHAR(32) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `code` VARCHAR(2048) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `description` VARCHAR(2048) NULL DEFAULT NULL COLLATE 'utf8mb3_general_ci',
    `hidden` INT(11) NULL DEFAULT NULL,
    `placeholder` INT(11) NULL DEFAULT NULL,
    PRIMARY KEY (`guid`) USING BTREE
)
COLLATE='utf8mb3_general_ci'
ENGINE=InnoDB;

当前实现方案 #

SELECT a1.guid AS a_guid,
            a1.name AS L1,
            NULL AS L2,
            NULL AS L3,
            NULL AS L4,
            IF(a1.name LIKE '%Inkomen', -1, 1) AS multiplier
FROM gnucash.accounts a1
JOIN gnucash.accounts a0 ON a0.guid = a1.parent_guid AND a0.name = 'Root Account'

UNION ALL

SELECT a2.guid AS a_guid,
            a1.name AS L1,
            a2.name AS L2,
            NULL AS L3,
            NULL AS L4,
            IF(a1.name LIKE '%Inkomen', -1, 1) AS multiplier
FROM gnucash.accounts a2
JOIN gnucash.accounts a1 ON a1.guid = a2.parent_guid
JOIN gnucash.accounts a0 ON a0.guid = a1.parent_guid AND a0.name = 'Root Account'

UNION ALL

SELECT a3.guid AS a_guid,
            a1.name AS L1,
            a2.name AS L2,
            a3.name AS L3,
            NULL AS L4,
            IF(a1.name LIKE '%Inkomen', -1, 1) AS multiplier
FROM gnucash.accounts a3
JOIN gnucash.accounts a2 ON a2.guid = a3.parent_guid
JOIN gnucash.accounts a1 ON a1.guid = a2.parent_guid
JOIN gnucash.accounts a0 ON a0.guid = a1.parent_guid AND a0.name = 'Root Account'

UNION ALL

SELECT a4.guid AS a_guid,
            a1.name AS L1,
            a2.name AS L2,
            a3.name AS L3,
            a4.name AS L4,
            IF(a1.name LIKE '%Inkomen', -1, 1) AS multiplier
FROM gnucash.accounts a4
JOIN gnucash.accounts a3 ON a3.guid = a4.parent_guid
JOIN gnucash.accounts a2 ON a2.guid = a3.parent_guid
JOIN gnucash.accounts a1 ON a1.guid = a2.parent_guid
JOIN gnucash.accounts a0 ON a0.guid = a1.parent_guid AND a0.name = 'Root Account'

疑问 #

是否存在更高效、更优雅的方式来创建该视图？我尝试过不同的JOIN方式，但无法避免对每个层级使用UNION。也曾尝试过递归查询方案，但它仅返回与单个顶级父级相关的所有账号列表，无法生成包含所有账号（含中间层级账号）的目标扁平表。

补充说明 #

• 无法控制GnuCash的底层数据库设计，因此无法修改guid的使用。
• 之前遗漏了CTE代码，现已调整。
• 感谢各位的建议。

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