如何通过subgroup_mappings表实现customers_groups表自连接获取父子组信息

根据你的表结构，要同时获取父群组和子群组的信息，需要将customers_groups表自连接，并通过subgroup_mappings映射表建立关联关系。以下是具体实现方案：

基础查询（仅返回存在父子关联的群组对） #

SELECT
    parent.id AS parent_group_id,
    parent.group_shortname AS parent_group_name,
    child.id AS subgroup_id,
    child.group_shortname AS subgroup_name
FROM
    subgroup_mappings sm
JOIN
    customers_groups parent ON sm.group_id = parent.id
JOIN
    customers_groups child ON sm.subgroup_id = child.id;

可选：左连接变体（包含无关联的群组） #

如果需要覆盖无子女的父群组、或无父组的子群组场景，可使用左连接：

列出所有父群组及对应子组（无子女则显示NULL） #

SELECT
    parent.id AS parent_group_id,
    parent.group_shortname AS parent_group_name,
    child.id AS subgroup_id,
    child.group_shortname AS subgroup_name
FROM
    customers_groups parent
LEFT JOIN
    subgroup_mappings sm ON parent.id = sm.group_id
LEFT JOIN
    customers_groups child ON sm.subgroup_id = child.id;

列出所有子群组及对应父组（无父组则显示NULL） #

SELECT
    parent.id AS parent_group_id,
    parent.group_shortname AS parent_group_name,
    child.id AS subgroup_id,
    child.group_shortname AS subgroup_name
FROM
    customers_groups child
LEFT JOIN
    subgroup_mappings sm ON child.id = sm.subgroup_id
LEFT JOIN
    customers_groups parent ON sm.group_id = parent.id;

核心逻辑说明 #

• 给customers_groups表分别指定parent（父群组）、child（子群组）别名，避免表名冲突
• 通过subgroup_mappings的group_id关联父群组ID，subgroup_id关联子群组ID
• 查询结果直接输出父、子群组的ID和简称，满足业务展示需求

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