急挺难的sql，

急！！！挺难的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&gt;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 行受影响)
 */

[其他解释]
计算每个节点的amount ,什么意思？
[其他解释]

引用:

计算每个节点的amount ,什么意思？

就是说：amount是和，比如1有两个孩子，2和3,2的amount值为10,3的amount值为10,1的amount则为20……当然题目中的树有递归的意思
[其他解释]

引用:

SQL code??

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950

/*CREATE TABLE [dbo].[c]( [id] [int] NOT NULL, [Thelevel] [int] NULL, [……

我靠，这就是用with实现递归的方式啊！！！谢谢啦
[其他解释]
我还以为不对被你骂了呢
[其他解释]

引用:

我还以为不对被你骂了呢

怎么可能呢,很感谢，你太强了！！！不过这个东西理解起来真难，尤其是细分的时候……
[其他解释]
你把数据搞成2条再研究

急挺难的sql

热点推荐