MySQL查询获取所有二级分类行及对应所属三级分类数组的方法

SELECT 
    cl2.id, 
    cl2.sub_cat_name, 
    JSON_OBJECT("id", cl1.id, "name", cl1.cat_name) AS categoryOne, 
    (
        SELECT JSON_ARRAYAGG(
            JSON_OBJECT(
                'id', cl3.id,
                'name', cl3.sub_cat_name
            )
        )
        FROM category_level_three cl3
        WHERE cl3.cat_l2_id = cl2.id
    ) AS categoryThree
FROM category_level_two cl2
JOIN category_level_one cl1 ON cl2.cat_l1_id = cl1.id;

• 删掉了外层SQL中固定筛选二级分类id=1的条件，同时修复了原SQL外层WHERE重复书写的语法错误
• 三级分类子查询不再重复关联category_level_two表，直接通过cl3.cat_l2_id = cl2.id和外层的当前二级分类行做关联，自动匹配对应三级分类
• 修正了原SQL中三级分类字段名写错的问题（原语句写的sub_sub_cat_name和表结构定义的sub_cat_name不匹配）
• 给所有表增加别名简化语句，可读性更高
• 如果需要二级分类没有对应三级分类时返回空数组而非NULL，可以把子查询里的JSON_ARRAYAGG部分替换为IFNULL(JSON_ARRAYAGG(JSON_OBJECT('id', cl3.id, 'name', cl3.sub_cat_name)), JSON_ARRAY())

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