MySQL关联6张表生成逗号分隔值的实现方案咨询

首先基于常规业务表的主键外键关联逻辑（若你的表字段命名有差异，可对应调整关联条件），以下是不同数据库环境下的可行SQL方案，核心是通过字符串聚合函数实现分销商名称的逗号分隔展示：

关联逻辑说明 #

1. 通过dealer_distributor_mapping关联经销商表（dealer_master）与分销商表（distributor_master）
2. 经销商表关联地区表（district_master），地区表关联州表（state_master），州表关联国家表（country_master）获取地域信息
3. 使用字符串聚合函数将同一经销商关联的所有分销商名称合并为逗号分隔的字符串

MySQL/MariaDB 版本 #

SELECT
    dm.dealer_id,
    dm.dealer_name,
    cm.country_name,
    sm.state_name,
    dcm.district_name,
    GROUP_CONCAT(dist.distributor_name SEPARATOR ', ') AS distributor_names
FROM
    dealer_master dm
JOIN district_master dcm ON dm.district_id = dcm.district_id
JOIN state_master sm ON dcm.state_id = sm.state_id
JOIN country_master cm ON sm.country_id = cm.country_id
LEFT JOIN dealer_distributor_mapping ddm ON dm.dealer_id = ddm.dealer_id
LEFT JOIN distributor_master dist ON ddm.distributor_id = dist.distributor_id
GROUP BY
    dm.dealer_id, dm.dealer_name, cm.country_name, sm.state_name, dcm.district_name
ORDER BY
    dm.dealer_id;

PostgreSQL 版本 #

SELECT
    dm.dealer_id,
    dm.dealer_name,
    cm.country_name,
    sm.state_name,
    dcm.district_name,
    STRING_AGG(dist.distributor_name, ', ') AS distributor_names
FROM
    dealer_master dm
JOIN district_master dcm ON dm.district_id = dcm.district_id
JOIN state_master sm ON dcm.state_id = sm.state_id
JOIN country_master cm ON sm.country_id = cm.country_id
LEFT JOIN dealer_distributor_mapping ddm ON dm.dealer_id = ddm.dealer_id
LEFT JOIN distributor_master dist ON ddm.distributor_id = dist.distributor_id
GROUP BY
    dm.dealer_id, dm.dealer_name, cm.country_name, sm.state_name, dcm.district_name
ORDER BY
    dm.dealer_id;

SQL Server 版本 #

SELECT
    dm.dealer_id,
    dm.dealer_name,
    cm.country_name,
    sm.state_name,
    dcm.district_name,
    STRING_AGG(dist.distributor_name, ', ') AS distributor_names
FROM
    dealer_master dm
JOIN district_master dcm ON dm.district_id = dcm.district_id
JOIN state_master sm ON dcm.state_id = sm.state_id
JOIN country_master cm ON sm.country_id = cm.country_id
LEFT JOIN dealer_distributor_mapping ddm ON dm.dealer_id = ddm.dealer_id
LEFT JOIN distributor_master dist ON ddm.distributor_id = dist.distributor_id
GROUP BY
    dm.dealer_id, dm.dealer_name, cm.country_name, sm.state_name, dcm.district_name
ORDER BY
    dm.dealer_id;

Oracle 11g+ 版本 #

SELECT
    dm.dealer_id,
    dm.dealer_name,
    cm.country_name,
    sm.state_name,
    dcm.district_name,
    LISTAGG(dist.distributor_name, ', ') WITHIN GROUP (ORDER BY dist.distributor_name) AS distributor_names
FROM
    dealer_master dm
JOIN district_master dcm ON dm.district_id = dcm.district_id
JOIN state_master sm ON dcm.state_id = sm.state_id
JOIN country_master cm ON sm.country_id = cm.country_id
LEFT JOIN dealer_distributor_mapping ddm ON dm.dealer_id = ddm.dealer_id
LEFT JOIN distributor_master dist ON ddm.distributor_id = dist.distributor_id
GROUP BY
    dm.dealer_id, dm.dealer_name, cm.country_name, sm.state_name, dcm.district_name
ORDER BY
    dm.dealer_id;

说明：使用LEFT JOIN是为了避免漏掉未关联任何分销商的经销商数据；若需要对分销商名称排序，可在聚合函数中添加排序规则（如Oracle示例中的ORDER BY dist.distributor_name）。

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