mysql - 在同一张表上加入外键并显示父母和孩子

Question

我有一个外键，它指向同一张表的主键（子 - 父）。

我正在尝试编写一个查询，该查询将以如下所示的形式显示结果：

Parent 1 
Parent 1 > Child 1
Parent 1 > Child 2
Parent 2 
Parent 2 > Child 2 
Parent 3 
Parent 4

我想离开加入桌子，即使我很接近它，我也无法做到。让我知道我做错了什么，或者是否有更简单的解决方案。

SELECT 
    a.OrganizationTypeID,
    b.ParentOrganizationTypeID,
    a.Name ParentName,
    b.Name ChildName,
    CASE
        WHEN b.ParentOrganizationTypeID IS NULL THEN a.Name
        ELSE CONCAT_WS('>', a.Name, b.Name)
    END AS res
FROM
    tblOrganizationTypes a
        left JOIN
    tblOrganizationTypes b ON b.ParentOrganizationTypeID = a.OrganizationTypeID
WHERE a.DateDeleted is null
GROUP BY b.OrganizationTypeID
ORDER BY a.OrganizationTypeID , b.ParentOrganizationTypeID

标签： mysqlsqlself-join

Answer 1

请Group by从您的查询中删除子句。

SELECT 
    a.OrganizationTypeID,
    b.OrganizationTypeID,
    
    a.Name ParentName,
    b.Name ChildName,
    CASE
        WHEN b.ParentOrganizationTypeID IS NULL THEN a.Name
        ELSE CONCAT_WS('>', a.Name, b.Name)
    END AS res
FROM
    tblOrganizationTypes a
    left join    
    tblOrganizationTypes b on a.OrganizationTypeID=b.ParentOrganizationTypeID 
    or (a.ParentOrganizationTypeID is null and a.OrganizationTypeID=b.OrganizationTypeID)
where a.DateDeleted is null and b.Name is not null
ORDER BY a.ParentOrganizationTypeID , b.OrganizationTypeID

DB-小提琴：

模式和插入语句：

 create table tblOrganizationTypes (OrganizationTypeID int, ParentOrganizationTypeID int, Name varchar(100),DateDeleted date);
 insert into tblOrganizationTypes values(1,null, 'parent',null);
 insert into tblOrganizationTypes values(2,1, 'child 1',null);
 insert into tblOrganizationTypes values(3,1, 'child 2',null);
 insert into tblOrganizationTypes values(5,null, 'parent 5',null);

询问：

 SELECT 
     a.OrganizationTypeID,
     b.OrganizationTypeID,
     
     a.Name ParentName,
     b.Name ChildName,
     CASE
         WHEN b.ParentOrganizationTypeID IS NULL THEN a.Name
         ELSE CONCAT_WS('>', a.Name, b.Name)
     END AS res
 FROM
     tblOrganizationTypes a
     left join    
     tblOrganizationTypes b on a.OrganizationTypeID=b.ParentOrganizationTypeID 
     or (a.ParentOrganizationTypeID is null and a.OrganizationTypeID=b.OrganizationTypeID)
 where a.DateDeleted is null and b.Name is not null
 ORDER BY a.ParentOrganizationTypeID , b.OrganizationTypeID

输出：

组织类型ID	组织类型ID	父母名字	子名	资源
1	1	父母	父母	父母
1	2	父母	孩子 1	父母>孩子 1
1	3	父母	孩子 2	父母>孩子 2
5	5	父母 5	父母 5	父母 5

db<>在这里摆弄