请忽略其中的计算逻辑错误,只关注sqlserver语句就好了
树形结构见图:
每一行的total=number*price
父节点的number是其子节点number之和,price是其子节点price的平均值现在需要在改动一个叶节点的number后,递归计算其父、祖节点的各值。这个sqlserver语句应该怎么写?
树形结构见图:
每一行的total=number*price
父节点的number是其子节点number之和,price是其子节点price的平均值现在需要在改动一个叶节点的number后,递归计算其父、祖节点的各值。这个sqlserver语句应该怎么写?
https://blog.csdn.net/sinat_28984567/article/details/79576583
https://blog.csdn.net/sinat_28984567/article/details/79576583
大佬你这篇文章我看了,可是我需要更新父节点的数据啊,这个怎么写?
https://blog.csdn.net/sinat_28984567/article/details/79576583
大佬你这篇文章我看了,可是我需要更新父节点的数据啊,这个怎么写?
贴测试数据和结果
https://blog.csdn.net/sinat_28984567/article/details/79576583
大佬你这篇文章我看了,可是我需要更新父节点的数据啊,这个怎么写?
贴测试数据和结果
版主,我上传不了附件,怎么发送给你?
GOSET QUOTED_IDENTIFIER ON
GOSET ANSI_PADDING ON
GOCREATE TABLE [dbo].[treetest](
[id] [int] IDENTITY(1,1) NOT NULL,
[pid] [int] NULL,
[category] [varchar](30) NULL,
[number] [float] NULL,
[price] [float] NULL,
[total] [float] NULL,
CONSTRAINT [PK_treetest] 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]GOSET ANSI_PADDING OFF
GOinsert into treetest (category,number,price,total) values ('水果',106,2.2,233.2)
insert into treetest (pid,category,number,price,total) values (1,'南方水果',18,2,36)
insert into treetest (pid,category,number,price,total) values (1,'北方水果',88,2.4,211.2)
insert into treetest (pid,category,number,price,total) values (3,'苹果',10,1,10)
insert into treetest (pid,category,number,price,total) values (3,'梨',22,2,44)
insert into treetest (pid,category,number,price,total) values (3,'桃',42,3,126)
insert into treetest (pid,category,number,price,total) values (3,'杏',6,4,24)
insert into treetest (pid,category,number,price,total) values (2,'芒果',5,2,10)
insert into treetest (pid,category,number,price,total) values (2,'香蕉',6,3,18)
insert into treetest (pid,category,number,price,total) values (2,'荔枝',7,1,7)
insert into treetest (pid,category,number,price,total) values (3,'葡萄',8,2,16)
如果我更新了‘桃’的数量为10,那么先将这一行的total改为30。(10*2)
然后计算父项‘北方水果’的number改为56,total改为134.4。(56*2.4)
然后再计算‘北方水果’的父项‘水果’的number为74,total为162.8。(74*2.2)
价格是其子项的平均值,先不管了