请教一个树形结构的sql语句怎么些写？求救了 ！！！
现有一个表 tb
id price sum
-----------------------------------
12.34 null
12.34.1 2
12.34.2 5
12.35 null
12.35.1. null
12.35.1.1 null
12.35.1.1.1 1
12.35.1.1.2 2
12.35.1.2 1
12.35.2 null
12.35.2.1 1
12.35.2.2 2
现在要逐级的汇总price，请教该怎么写sql???
比如说12.35，就是由12.35.1和12.35.2加上来的，而12.35.1又是由12.35.1.1和12.35.1.2汇总而来的，12.35.1.1又是12.35.1.1.1和12.35.1.1.2加上的，应该最后12.35的price等于7，请教大侠们该怎么写啊？？？
[解决办法]
- SQL code
--测试数据CREATE TABLE tb(ID char(3),PID char(3),Name nvarchar(10))INSERT tb SELECT '001',NULL ,'山东省'UNION ALL SELECT '002','001','烟台市'UNION ALL SELECT '004','002','招远市'UNION ALL SELECT '003','001','青岛市'UNION ALL SELECT '005',NULL ,'四会市'UNION ALL SELECT '006','005','清远市'UNION ALL SELECT '007','006','小分市'GO--查询指定节点及其所有子节点的函数CREATE FUNCTION f_Cid(@ID char(3))RETURNS @t_Level TABLE(ID char(3),Level int)ASBEGIN DECLARE @Level int SET @Level=1 INSERT @t_Level SELECT @ID,@Level WHILE @@ROWCOUNT>0 BEGIN SET @Level=@Level+1 INSERT @t_Level SELECT a.ID,@Level FROM tb a,@t_Level b WHERE a.PID=b.ID AND b.Level=@Level-1 END RETURNENDGO--调用函数查询002及其所有子节点SELECT a.*FROM tb a,f_Cid('002') bWHERE a.ID=b.ID/*--结果ID PID Name ------ ------- ---------- 002 001 烟台市004 002 招远市--*/
[解决办法]
- SQL code
update tb set sum = (select sum(price) from tb b where b.id like a.id+'%')from tb a
[解决办法]
- SQL code
create table tb(id varchar(20), price int) insert into tb values('12.34' , 0 )insert into tb values('12.34.1' , 2 )insert into tb values('12.34.2' , 5 )insert into tb values('12.35' , 0 )insert into tb values('12.35.1' , 0 )insert into tb values('12.35.1.1' , 0 )insert into tb values('12.35.1.1.1', 1 )insert into tb values('12.35.1.1.2', 2 )insert into tb values('12.35.1.2' , 1 )insert into tb values('12.35.2' , 0 )insert into tb values('12.35.2.1' , 1 )insert into tb values('12.35.2.2' , 2 )goselect * , [sum] = (select sum(price) from tb where id like t.id + '%') from tb tdrop table tb/*id price sum -------------------- ----------- ----------- 12.34 0 712.34.1 2 212.34.2 5 512.35 0 712.35.1 0 412.35.1.1 0 312.35.1.1.1 1 112.35.1.1.2 2 212.35.1.2 1 112.35.2 0 312.35.2.1 1 112.35.2.2 2 2（所影响的行数为 12 行）*/
[解决办法]
事先生名，我不要分 ，希望对你有帮助。
（转）
有关树的运用和存储过程
- SQL code
--建立境Create Table department(departmenid Int,parentid Int)Insert department Select 60, nullUnion All Select 1, 0Union All Select 2, 1Union All Select 3, 1Union All Select 4, 2Union All Select 5, 2Union All Select 6, 3Union All Select 7, 3Union All Select 8, 7GO--建立函Create Function F_GetParent(@departmenid Int)Returns @Tree Table (departmenid Int, parentid Int)AsBeginInsert @Tree Select * From department Where departmenid = @departmenidWhile @@Rowcount > 0Insert @Tree Select A.* From department A Inner Join @Tree B On A.departmenid = B.parentid And A.departmenid Not In (Select departmenid From @Tree) Where A.parentid Is Not NullReturnEndGO--Select departmenid From dbo.F_GetParent(8) Order By parentidGO--除境Drop Table departmentDrop Function F_GetParent--果/*departmenid1378*/--建立境Create Table department(departmenid Int,parentid Int)Insert department Select 60, nullUnion All Select 1, 0Union All Select 2, 1Union All Select 3, 1Union All Select 4, 2Union All Select 5, 2Union All Select 6, 3Union All Select 7, 3Union All Select 8, 7GO--建存程Create ProceDure SP_GetParent(@departmenid Int)AsBeginSelect * Into #Tree From department Where departmenid = @departmenidWhile @@Rowcount > 0Insert #Tree Select A.* From department A Inner Join #Tree B On A.departmenid = B.parentid And A.departmenid Not In (Select departmenid From #Tree) Where A.parentid Is Not NullSelect departmenid From #Tree Order By parentidDrop Table #TreeEndGO--EXEC SP_GetParent 8GO--除境Drop Table departmentDrop ProceDure SP_GetParent--果/*departmenid1378*/