CTE Hierachy descending but picking up child nodes not parents from ancestor
Explanation
OK, the title might be a bit much :)
I'll paste the scripts at the end.
Imagine the following n-ary tree
.
|
---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)
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.
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?
Thanks a zillion :)
Here are the scripts:
Scripts
Table
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 Script
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)
http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/959fe835-e43d-4995-882c-910f3aa0ff68/
链接地址: http://www.djcxy.com/p/60220.html