BOM子节点汇总数据
------------------------------ Author :TravyLee(物是人非事事休,欲语泪先流!)-- Date :2012-12-06 13:02:57-- Version:-- Microsoft SQL Server 2012 - 11.0.2100.60 (Intel X86) --Feb 10 2012 19:13:17 --Copyright (c) Microsoft Corporation--Enterprise Edition: Core-based Licensing on Windows NT 6.1 <X86> (Build 7601: Service Pack 1)--------------------------------> 测试数据：[animal]if object_id('[animal]') is not null drop table [animal]go create table [animal]([id] int,[name] varchar(8),[parentid] int)insert [animal]select 1,'动物',0 union allselect 2,'鸟',1 union allselect 3,'爬行动物',1 union allselect 4,'哺乳动物',1 union allselect 5,'蛇',3 union allselect 6,'蛙',3 union allselect 7,'猫科动物',4 union allselect 8,'狮子',7 union allselect 9,'老虎',7go--SQL 2000使用临时表实现递归--drop table #tblselect *,levels=0 into #tbl from [animal] where [parentid]=0go--开始递归:while @@ROWCOUNT<>0 begininsert #tblselect a.id,a.name,a.parentid,b.levels+1from [animal] ainner join #tbl bon b.id=a.[parentid]where not exists(select 1 from #tbl c where a.id=c.id)endselect * from #tbl--在这个结果中自己筛选吧/*id name parentid levels----------- -------- ----------- -----------1 动物 0 02 鸟 1 13 爬行动物 1 14 哺乳动物 1 15 蛇 3 26 蛙 3 27 猫科动物 4 28 狮子 7 39 老虎 7 3(9 行受影响)*/