create table producttype ( iproducttypeid int,iparenttypeid int, cproducttypename char(10) )
insert producttype values(1,0,'百货')
insert producttype values(2,1,'纺织')
insert producttype values(3,1,'汽车')
insert producttype values(4,1,'电器')
insert producttype values(5,1,'医药')
insert producttype values(6,1,'图书')
insert producttype values(7,4,'家电')
insert producttype values(8,4,'IT')
insert producttype values(9,7,'电视机')
insert producttype values(10,7,'电冰箱')
insert producttype values(11,7,'洗衣机')
insert producttype values(12,7,'空调')
insert producttype values(13,7,'电风扇')
insert producttype values(14,8,'电脑')
insert producttype values(15,8,'网络')
insert producttype values(16,8,'数码')
insert producttype values(17,14,'台式')
insert producttype values(18,14,'笔记本')
insert producttype values(19,14,'配件')
insert producttype values(20,14,'外设')
insert producttype values(21,16,'照像机')
insert producttype values(22,16,'摄像机')
insert producttype values(23,16,'MP3播放器')
insert producttype values(24,19,'CPU')
insert producttype values(25,19,'主板')
insert producttype values(26,19,'内存')
insert producttype values(27,19,'显卡')
insert producttype values(28,19,'声卡')
insert producttype values(29,19,'网卡')
insert producttype values(30,19,'硬盘')
insert producttype values(31,19,'软驱')
insert producttype values(32,19,'光驱')
insert producttype values(33,19,'输出设备')
insert producttype values(34,19,'键盘')
insert producttype values(35,19,'鼠标')
insert producttype values(36,19,'音箱')
insert producttype values(37,19,'耳麦')
insert producttype values(38,33,'打印机')
insert producttype values(39,33,'扫描仪')
insert producttype values(40,33,'投影仪')
--求解一
create function getclassinfo(@id int) returns @tabinfo table
(
cProductid int,
cParentId int,
cProductName char(10),iLevel int
)
as
begin
declare @cid int,@level int
set @level=0
insert @tabinfo select *,@level from producttype where iproducttypeid=@id declare cur1 cursor local for select cProductid,iLevel from @tabinfo open cur1 fetch next from cur1 into @cid,@level while @@fetch_status=0 begin insert @tabinfo select *,@level+1 from producttype where iparenttypeid=@cid fetch next from cur1 into @cid,@level end return end select * from getclassinfo(33) select * from producttype
--求解二
create function getclassinfo2(@id int) returns @tabinfo table (cProductid int,cParentId int,cProductName char(10),iLevel int) as begin declare @level as int set @level=0 insert @tabinfo select *,@level from producttype where iproducttypeid=@id while @@rowcount>0 begin set @level=@level+1 insert @tabinfo select x.iproducttypeid,x.iparenttypeid, x.cproducttypename,@level from producttype as x join @tabinfo as y on x.iparenttypeid=y.cProductid and y.ilevel=@level-1 end return end select * from getclassinfo2(33)