我们如何从mlm表结构中搜索记录-howcanwesearchrecordfrommlmtablestructure

3  

There are two ways to approach this. If you need the data for each relationship in its own column, and if the number of relationships won't exceed x levels deep (e.g., 5), then you can join the same table several times in a single query (see query 1).

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

If you don't need the data in separate columns, but can work with a single delimited value (e.g., "root parent -> next parent -> last parent -> child"), then you can use a CTE query to build the concatenated strings (see query 2).

如果您不需要单独列中的数据,但可以使用单个分隔值(例如,“root parent - > next parent - > last parent - > child”),那么您可以使用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

Query 1 Results

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

Query 2 Results

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