CTE Hierachy降级但从祖先那里接起了孩子节点而不是父母 [英] CTE Hierachy descending but picking up child nodes not parents from ancestor

问题描述

好的，标题可能有点多：）

OK, the title might be a bit much :)

我将在最后粘贴脚本。

想象下面的n元树

.  
|  
---1 **(25)**   
   |  
    -----1.1 **(13)**  
    |    |  
    |    ----1.1.1 (1)  
    |    |  
    |    ----1.1.2 **(7)**  
    |    |    |
    |    |    ----1.1.2.1 (4)        
    |    |    |
    |    |    ----1.1.2.2 (3)  
    |    |    
    |    ----1.1.3 (5)  
    |    
    -----1.2 (2)  
    | 
    |    
    -----1.3 (10)  

依此类推，其中根分支为。。 ;也可以有一个2,3，n分支，并且该分支也将具有自己的任意树形式，并且可以从任何给定节点获得n分支。每个节点末尾方括号中的值就是该节点上的值。可以将它们视为具有子帐户的帐户，其中父帐户是子帐户的总和。

And so on, where the root branch "." can also have a 2,3,n branch and that branch would also have its own arbitrary tree form with n-branches possible from any give node. The values in brackets at the end of each node are the values at the node so to speak. Think of them as accounts with sub-accounts with the parent accounting being the sum of the child-accounts.

我要对CTE进行的操作是检索所有[sub]帐户直接位于父项下。因此，为了提供1.1作为搜索点，它将检索树的整个分支。但是，如果我尝试变得聪明并且对返回的值求和，那么我 将会（对于此特定示例）两次添加1.1.2，一次是通过其子总和

What I'm trying to do with CTE is to retrieve all the [sub] accounts directly beneath a parent. So for providing 1.1 as the search point, it'll retrieve that whole branch of the tree. But, if I try to be smart and sum the returned values, I will be adding (for this specific example) 1.1.2 twice, once through the summation of its sub accounts, the second by the summation of the value it itself contains.

我将如何处理这样的事情？

How would I go about something like this?

感谢一百万：)

以下是脚本：

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[Account](
    [ID] [nvarchar](50) NOT NULL,
    [ParentID] [nvarchar](50) NULL,
    [Value] [float] NOT NULL,
    [HasChild] [bit] NOT NULL,
 CONSTRAINT [PK_Account] PRIMARY KEY CLUSTERED 
(
    [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[Account]  WITH CHECK ADD  CONSTRAINT [FK_Account_Account] FOREIGN     KEY([ParentID])
REFERENCES [dbo].[Account] ([ID])
GO

ALTER TABLE [dbo].[Account] CHECK CONSTRAINT [FK_Account_Account]
GO

ALTER TABLE [dbo].[Account] ADD  CONSTRAINT [DF_Account_HasChild]  DEFAULT ((0)) FOR     [HasChild]
GO

CTE脚本

WITH 
DescendToChild([ID],ParentID,Value)
AS
(

    --base case
    SELECT [ID],ParentID,Value FROM Account
    Where ParentID = '1.1'
        
        
    UNION ALL
        
    ----recursive step
    SELECT
        A.[ID],A.ParentID,A.Value FROM Account as A
        INNER JOIN DescendToChild D on A.ParentID = D.ID
)
select * from DescendToChild;

推荐答案

以下是基于您的示例数据的解决方案。它只对没有孩子的节点求和：

Here's a solution based on your sample data. It works by only summing up those nodes with no children:

DECLARE @tree TABLE
(id INT
,parentid INT
,nodeName VARCHAR(10)
,VALUE INT
)

INSERT @tree (id,parentid,nodeName,VALUE)
VALUES
(1,NULL,'.',NULL),
(2,1,'1',25),
(3,2,'1.1',13),
(4,2,'1.2',2),
(5,2,'1.3',10),
(6,3,'1.1.1',1),
(7,3,'1.1.2',7),
(8,3,'1.1.3',5),
(9,7,'1.1.2.1',4),
(10,7,'1.1.2.2',3)


;WITH recCTE
AS
(
     SELECT id, parentid, nodeName, value, 
            CASE WHEN EXISTS (SELECT 1 FROM @tree AS t1 WHERE t1.parentid = t.id) THEN 1 ELSE 0 END AS hasChildren
     FROM @tree AS t
     WHERE nodeName = '1.1'

     UNION ALL

     SELECT t.id, t.parentid, t.nodeName, t.value, 
            CASE WHEN EXISTS (SELECT 1 FROM @tree AS t1 WHERE t1.parentid = t.id) THEN 1 ELSE 0 END AS hasChildren
     FROM @tree AS t
     JOIN recCTE AS r
     ON   r.id = t.parentid

)
SELECT SUM(VALUE)
FROM recCTE 
WHERE hasChildren = 0
OPTION (MAXRECURSION 0)

这篇关于CTE Hierachy降级但从祖先那里接起了孩子节点而不是父母的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！