如何从mlm表结构中搜索记录？

Question

我有一张有数据的桌子。

ID  ParentID    NodeName
1   NULL    Administration
2   NULL    Master Data
3   NULL    Input Forms
4   NULL    User Reports
5   NULL    Other Pages
6   1   Add User
7   2   Product Maintanence
8   2   Product BOM
9   3   Expected Sales
10  3   Product BOM
11  4   Finance
12  4   Manufacturing
13  6   GOGS Report
14  7   Purchase History
15  8   Production Report
16  5   Google
17  5   Company Site

现在我想写一个查询，它将上述查询结果区分为每个父-子关系，如Parent_Original>>Parent1>>Child。如果数据库子级上升到n级，也会产生类似于父级n>、父级n-1>父级n-2 >.>最后一个子的结果。

在上表场景中，其结果类似于。

Parent              Parent-1               Child

Administration      Add User               GOGS Report
Master Data         Product Maintanence    Purchase History
Master Data         Product BOM            Production Report
........... so on

有谁能建议我怎么做。任何建议都很感激。

Answer 1

有两种方法可以解决这个问题。如果您需要在它自己的列中为每个关系提供数据，并且如果关系的数量不会超过x级深(例如，5)，那么您可以在一个查询中多次加入相同的表(请参见查询1)。

如果不需要单独列中的数据，但可以使用单个分隔值(例如，“根父-> next父级->最近父级->子级”)，则可以使用CTE查询来构建连接字符串(请参见查询2)。

declare @tbl table (id int, parentid int, nodename varchar(20))

insert into @tbl values
(1, NULL, 'Administration'),
(2, NULL, 'Master Data'),
(3, NULL, 'Input Forms'),
(4, NULL, 'User Reports'),
(5, NULL, 'Other Pages'),
(6, 1, 'Add User'),
(7, 2, 'Product Maintanence'),
(8, 2, 'Product BOM'),
(9, 3, 'Expected Sales'),
(10, 3, 'Product BOM'),
(11, 4, 'Finance'),
(12, 4, 'Manufacturing'),
(13, 6, 'GOGS Report'),
(14, 7, 'Purchase History'),
(15, 8, 'Production Report'),
(16, 5, 'Google'),
(17, 5, 'Company Site'),
(18, 13, 'Archived Data'),
(19, 13, 'Active Data'),
(20, 18, 'On Tape'),
(21, 18, 'On Disc')

/* query 1 */
select r.nodename as root
      ,c1.nodename as [child-1]
      ,c2.nodename as [child-2]
      ,c3.nodename as [child-3]
      ,c4.nodename as [child-4]
      ,c5.nodename as [child-5]
from   @tbl r
       left outer join @tbl c1 on r.id = c1.parentid
       left outer join @tbl c2 on c1.id = c2.parentid
       left outer join @tbl c3 on c2.id = c3.parentid
       left outer join @tbl c4 on c3.id = c4.parentid
       left outer join @tbl c5 on c4.id = c5.parentid
where  r.parentid is null
order by r.nodename, c1.nodename, c2.nodename, c3.nodename, c4.nodename, c5.nodename

/* query 2 */
;with cte(id, parentid, nodename) as (
  select id, parentid, cast(nodename as varchar(max))
  from   @tbl
  where  parentid is null

  union all

  select t.id, t.parentid, cast(cte.nodename + ' -> ' + t.nodename as varchar(max))
  from   @tbl t
         inner join cte on t.parentid = cte.id
)
select nodename
from   cte c1
where  not exists (
         select 1
         from   cte c2
         where  c1.id = c2.parentid
       )
order by nodename

查询1结果

root                 child-1              child-2              child-3              child-4              child-5
-------------------- -------------------- -------------------- -------------------- -------------------- --------------------
Administration       Add User             GOGS Report          Active Data          NULL                 NULL
Administration       Add User             GOGS Report          Archived Data        On Disc              NULL
Administration       Add User             GOGS Report          Archived Data        On Tape              NULL
Input Forms          Expected Sales       NULL                 NULL                 NULL                 NULL
Input Forms          Product BOM          NULL                 NULL                 NULL                 NULL
Master Data          Product BOM          Production Report    NULL                 NULL                 NULL
Master Data          Product Maintanence  Purchase History     NULL                 NULL                 NULL
Other Pages          Company Site         NULL                 NULL                 NULL                 NULL
Other Pages          Google               NULL                 NULL                 NULL                 NULL
User Reports         Finance              NULL                 NULL                 NULL                 NULL
User Reports         Manufacturing        NULL                 NULL                 NULL                 NULL

查询2结果

nodename
------------------------------------------------------------------------
Administration -> Add User -> GOGS Report -> Active Data
Administration -> Add User -> GOGS Report -> Archived Data -> On Disc
Administration -> Add User -> GOGS Report -> Archived Data -> On Tape
Input Forms -> Expected Sales
Input Forms -> Product BOM
Master Data -> Product BOM -> Production Report
Master Data -> Product Maintanence -> Purchase History
Other Pages -> Company Site
Other Pages -> Google
User Reports -> Finance
User Reports -> Manufacturing