MySQL中将所有一对一关联表合并为单张表（含现有表结构）

嘿，我来帮你把这三张关联的表合并成一张单表。先理清楚它们的关联逻辑：

• sub_category的pdmcatid和category的pdmcatid关联
• category的pdlcatid和main_category的pdlcatid关联

虽然看起来category和main_category是一对多的关系，但我们可以用JOIN语句把所有层级的信息整合到一张表里。下面分两种场景给你方案：

场景1：只保留有完整层级的记录（子分类→分类→主分类都存在） #

用INNER JOIN确保每一条记录都有对应的上级分类，只输出层级完整的数据：

SELECT 
    sc.pdcatid AS sub_category_id,
    sc.name AS sub_category_name,
    c.pdmcatid AS category_id,
    c.name AS category_name,
    mc.pdlcatid AS main_category_id,
    mc.name AS main_category_name
FROM sub_category sc
INNER JOIN category c ON sc.pdmcatid = c.pdmcatid
INNER JOIN main_category mc ON c.pdlcatid = mc.pdlcatid;

场景2：保留所有子分类，哪怕上级分类缺失 #

如果有些子分类没有对应的分类或主分类，用LEFT JOIN保留这些记录（缺失的字段会显示为NULL）：

SELECT 
    sc.pdcatid AS sub_category_id,
    sc.name AS sub_category_name,
    c.pdmcatid AS category_id,
    c.name AS category_name,
    mc.pdlcatid AS main_category_id,
    mc.name AS main_category_name
FROM sub_category sc
LEFT JOIN category c ON sc.pdmcatid = c.pdmcatid
LEFT JOIN main_category mc ON c.pdlcatid = mc.pdlcatid;

合并后的示例结果（INNER JOIN场景） #

（注：示例中补全了main_category表的合理数据，因为你提供的内容未完整展示这张表）

内容的提问来源于stack exchange，提问作者Michael Eugene Yuen