通过SQL查询创建面包屑结构

一尘不染

通过SQL查询创建面包屑结构

sql

我正在使用SQL Query，下面是表格。

Organization

OrgID    Name          RAOGID     RAOID   SubGroupID
1       Org RAOG      1   NULL       NULL
2       Org RAO         NULL       1        NULL
3       Org Sub Group   NULL       NULL      1

RAOG

RAOGID  AccredID
1              2

RAO

RAOID   RAOGID
1         1

Sub Group

SubGroupID  RAOID
1            1

我有四个表，如上所示，我需要从上表的结构中制作面包屑，因此我想编写查询，该查询将以以下格式返回数据，请参见下文。

RAOGID >> RAOID >>子组ID

例如，根据上述数据，我的面包屑将是

Org RAOG >> Org RAO >> Org子组

``组织’‘表包含ROAG，RAO和SubGroup的所有ID，我需要查询将以上述格式返回数据。

还需要一项功能。我不想显示面包屑，如果没有子组 标识， 那么我的面包屑将是 Org RAOG >> Org
RAO且没有RAOID，那么将只有一个面包屑，即 Org RAOG

谢谢，最好的问候，Manoj

阅读 142

2021-05-16

共1个答案

一尘不染

我使用以下查询解决了SQL面包屑的上述问题

SELECT     c.Name + ' >> ' + b.Name + '>>' + a.Name AS breadcrumb
FROM         tblOrganisation AS a LEFT OUTER JOIN
                      tblSubGroup AS sg ON a.SubGroupID = sg.SubGroupID LEFT OUTER JOIN
                      tblOrganisation AS b ON sg.RAOID = b.RAOID LEFT OUTER JOIN
                      tblRAO AS rao ON rao.RAOID = b.RAOID LEFT OUTER JOIN
                      tblOrganisation AS c ON c.RAOGID = rao.RAOGID
WHERE     (sg.RAOID IS NOT NULL) AND (a.OrgID = @ORGID)
UNION
SELECT     c.Name + ' >> ' + a.Name AS breadcrumb
FROM         tblOrganisation AS a LEFT OUTER JOIN
                      tblRAO AS rao ON rao.RAOID = a.RAOID LEFT OUTER JOIN
                      tblOrganisation AS c ON c.RAOGID = rao.RAOGID
WHERE     (rao.RAOID IS NOT NULL) AND (a.OrgID = @ORGID)
UNION
SELECT     Name AS breadcrumb
FROM         tblOrganisation AS a
WHERE     (RAOGID IS NOT NULL) AND (OrgID = @ORGID)

2021-05-16