根据特定模式查找树的父节点的总金额 [英] find total money of parent node of tree according to a particular pattern

问题描述

实际上我正在开发一个项目，其中成员以树模式添加，并相应地获得付款。



我的tbltestingtree结构如下：

 Id ParentId IsLeft IsRight 
 1 Null Null Null 
 2 1 1 Null 
 3 1 Null 1 
 4 2 1 Null 
 5 2 Null 1 
 6 3 1 Null 
 7 3 Null 1 
 8 4 1 Null 
 9 4 Null 1 
 10 5 1 Null 
 

**问题是，当左边有两个节点，右边有一个节点时，父亲最初会给1500美元（2： 1）。然后每对500美元。**



我的问题是找到可以返回任何给定节点的总收入的查询。 />


http://demosite2.netbridgeindia.com/imagesashu/payment1。 jpg [ ^ ]



根据图节点1必须获得2500 $（1500 + 500 + 500）第一个500 $用于节点4，第二个500 $用于节点3.



根据图节点2必须得到1500 $，因为它左边有两个节点，右边有一个节点，这意味着比率为（2：1）。并且没有对



根据图节点3必须得到0 $，因为它没有任何比例节点（2：1）



必须记住，1500美元将是第一笔付款，然后只计算其他对，当节点比例为2：1时将给出1500美元（两个）左边的节点和右边的节点）但是比例为1：2时没有钱（左边一个节点，右边一个节点）





我发现查询将计算特定节点下的所有对，并根据500 $给出接收金额，但查询无法考虑第一个条件是2：1条件

 声明  @ ParentId  < span class =code-keyword> as   int  
  set  @ParentId = 1 
 
  crea te   table  #temp_table_name 
（
 ParentId  varchar （ 30 ） null ，
）
 
 
;  with  Child  as  
（
  select  id，ParentId 来自 tblTestingTree 其中 id = @ ParentId 
  union   all  
 选择 tblTestingTree.Id，tblTestingTree。 parentId 来自 tblTestingTree 
 内部  join 儿童
   tblTestingTree.ParentId = Child.Id 
）
 
  insert   into  #temp_table_name 
 选择 c.ParentId 来自 tblTestingTree T < span class =code-keyword> join 子c 
  on  c.Id = t.Id 
  WHERE  ISNULL（T.ParentId， 0 ）<>  0  和 c.ParentId!=@ParentId 
  group   by  c.ParentId 
   COUNT（c.ParentId）> ; 1 
 
 选择 COUNT（*）* 500   totalmoney 来自 #temp_table_name 
 
  drop   table  #temp_table_name 
 

请帮助

解决方案

当两个节点被添加到其左侧并且一个节点被添加到其右侧（2：1）时被赋予父节点。然后500

每对。**



我的问题是找到可以返回的查询任何给定节点的总收入。



http：// demosite2.netbridgeindia.com/imagesashu/payment1.jpg [ ^ ]



根据图节点1必须得到2500

（1500 + 500 + 500）第一个500

actually i am working on a project in which members are added in a tree pattern, and get the payment accordingly.

My tbltestingtree structure is ass follow:

Id         ParentId        IsLeft           IsRight
 1          Null             Null             Null
 2           1                1               Null
 3           1               Null               1
 4           2                1               Null
 5           2               Null               1
 6           3                1               Null
 7           3               Null               1
 8           4                1               Null
 9           4               Null               1
 10          5                1               Null

**the problem is that initially 1500$ are given to parent when two nodes are added to its left and one to his right(2:1) . and then 500$ for each pair.**

My problem is to find the query which can return the total income of any given node.

http://demosite2.netbridgeindia.com/imagesashu/payment1.jpg[^]

According to figure node 1 must get 2500$ (1500+500+500) first 500$ is for node 4 and second 500$ is for node 3.

According to figure node 2 must get 1500$ because it has two nodes to its left and one node to its right this means a ratio of (2:1). and has no pairs

According to figure node 3 must get 0$ because it does not have any nodes in ratio(2:1)

one thing has to be kept in mind that 1500$ will be the first payment and then only the other pairs will be counted, and 1500$ will be given when node has ratio 2:1(two nodes on left and one on right) but no money when ratio is 1:2(one node on left and two on right)


I have found the query which will count all the pairs below a particular node and give receiving amount according to 500$, but the query has not been able to consider the first condition that is the 2:1 condition

      declare @ParentId as int
       set @ParentId=1

     create table #temp_table_name
      (
        ParentId varchar(30) null,
       )


    ;with Child as
     (
  select id,ParentId from tblTestingTree where id=@ParentId
  union all
  Select tblTestingTree.Id,tblTestingTree.parentId from tblTestingTree
  inner join Child
  on tblTestingTree.ParentId=Child.Id
     )

insert into #temp_table_name
select c.ParentId  from tblTestingTree T join Child c
on c.Id=t.Id
WHERE ISNULL(T.ParentId, 0) <> 0 and c.ParentId!=@ParentId
group by c.ParentId
having COUNT(c.ParentId)>1

select COUNT(*)*500 as totalmoney from #temp_table_name

drop table #temp_table_name

Please Help

解决方案

are given to parent when two nodes are added to its left and one to his right(2:1) . and then 500

for each pair.**

My problem is to find the query which can return the total income of any given node.

http://demosite2.netbridgeindia.com/imagesashu/payment1.jpg[^]

According to figure node 1 must get 2500

(1500+500+500) first 500

这篇关于根据特定模式查找树的父节点的总金额的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！