我们如何从mlm表结构中搜索记录。 [英] how can we search record from mlm table structure.

问题描述

我有一个包含数据的表。

 ID ParentID NodeName 
 1 NULL管理
 2 NULL主数据
 3 NULL输入表格
 4空用户报告
 5其他页面
 6 1添加用户
 7 2产品维护
 8 2产品BOM 
 9 3预计销售
 10 3产品BOM 
 11 4金融
 12 4制造业
 13 6 GOGS报告
 14 7购买历史
 15 8生产报告
 16 5 Google 
 17 5公司网站

现在我想编写一个查询，根据父子关系区分上述查询结果，如Parent_Original>> Parent1>>儿童。如果数据库子级上升到n级别，它也会导致父级n>父n-1>父n-2> ...>最后一个孩子。



在上表情景中，结果如下。

父母亲1儿童
 
管理添加用户GOGS报告
主数据产品维护购买历史
主数据产品BOM生产报告
 ...........等等

任何人都可以建议我怎样才能做到这一点。任何建议都非常感谢。

解决方案

制作疑难查询（MLM金字塔）

动态尝试递归CTE或下面的查询并执行

 选择 
 p2.Nodename，
 p1 .Nodename，
 p.Nodename 
 来自 [tablem] p（ nolock ） 
  left   join  [tablem] p1（ nolock ）  p.parentid = p1.id 
  left   join  [tablem] p2（ nolock ）  p1.parentid = p2.id 
 其中​​ p2.Nodename   不  null  
  AND  p1.Nodename   不  null  
  AND  p.Nodename   不  null  

大家好！我在一个聪明的家伙的帮助下解决了这个问题。请参阅下面的解决方案&如果您将面临此类任务，请享受此解决方案。

 选择 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 < span class =code-keyword> as  [child-5] 
  from   @ tbl  r 
  left  外部  join   @ tbl  c1  on  r.id = c1.parentid 
  left  外部  join   @ tbl  c2  on  c1.id = c2.parentid 
  left  外部  join   @tbl  c3  on  c2.id = c3.parentid 
  left  < span class =code-keyword> outer   join   @ tbl  c4 >  c3.id = c4.parentid 
  left   outer   join   @ tbl  c5  on  c4 .id = c5.parentid 
 其中 r.parentid    null  
 订单  by  r.nodename，c1.nodename，c2.nodename，c3.nodename，c4.nodename，c5.nodename 

I have a table with data.

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

Now I want to write a query which distinguish above query result as per parent-child relationship, as Parent_Original>>Parent1>>Child. If the database child goes upto n level it also result like Parent n> Parent n-1> Parent n-2 > ... > Last Child.

In above table scenario it result like.

Parent              Parent-1               Child

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

Can any one suggest me how can we do this. Any suggestion really appreciate.

解决方案

Makeing difficult query (MLM pyramid)

Try recursive CTE's or form below query dynamically and execute

select 	
	p2.Nodename,
	p1.Nodename,
	p.Nodename
from [tablem] p (nolock)
left join [tablem] p1 (nolock) on p.parentid=p1.id
left join [tablem] p2 (nolock) on p1.parentid=p2.id
where p2.Nodename is not null
AND p1.Nodename is not null
AND p.Nodename is not null

Hi guys ! I have solved this with the help of one of the brilliant guy. Please see solution below & enjoy with this solution if you'll face such type of task.

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

这篇关于我们如何从mlm表结构中搜索记录。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！