急！！！挺难的sql，高手请进！
CREATE TABLE [dbo].[c](
[id] [int] NOT NULL,
[Thelevel] [int] NULL,
[Farther] [int] NULL,
[amount] [int] NULL,
CONSTRAINT [PK_c] 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]
这是一个树形的菜单。怎么样用一句sql实现计算每个节点的amount，不是用函数或存储过程。表：
id Thelevel（层次） Farther amount
1 1 null 0
2 2 1 0
3 2 1 0
4 3 2 0
5 3 2 10
6 3 3 10
7 3 3 10
8 4 4 10
amount有值的均是叶子节点。自己画一下树形结构就知道了。求指教！！！
GO
/*CREATE TABLE [dbo].[c](
[id] [int] NOT NULL,
[Thelevel] [int] NULL,
[Farther] [int] NULL,
[amount] [int] NULL,
CONSTRAINT [PK_c] 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]
--这是一个树形的菜单。怎么样用一句sql实现计算每个节点的amount，不是用函数或存储过程。表：
insert into c(id , Thelevel, Farther , amount)
select 1, 1, null, 0
union all select 2, 2, 1, 0
union all select 3, 2, 1, 0
union all select 4, 3, 2, 0
union all select 5, 3, 2, 10
union all select 6, 3, 3, 10
union all select 7, 3, 3, 10
union all select 8, 4, 4, 10
select * from c
amount有值的均是叶子节点。自己画一下树形结构就知道了。求指教！！！
GO*/
WITH huang AS (
SELECT id , Thelevel, Farther , amount
FROM c
WHERE amount>0
UNION ALL
SELECT c.id,c.thelevel,c.farther,a.amount+c.amount
FROM huang a INNER JOIN c ON a.farther=c.id
)
SELECT id,SUM(amount)amount FROM Huang GROUP BY id
/*
id amount
----------- -----------
1 40
2 20
3 20
4 10
5 10
6 10
7 10
8 10
(8 行受影响)
*/
