＋设为首页 ＋添加到收藏夹
CategoryID ParentID Depth
14 0
56 14
78 56
100 78
34 56
这个数据库怎么变成
CategoryID ParentID Depth
14 0 1
56 14 2
78 56 3
100 78 4
34 56 3
--生成测试数据
create table BOM(CategoryID INT,ParentID INT,Depth INT)
INSERT INTO BOM SELECT 14 ,0 ,NULL
INSERT INTO BOM SELECT 56 ,14,NULL
INSERT INTO BOM SELECT 78 ,56,NULL
INSERT INTO BOM SELECT 100,78,NULL
INSERT INTO BOM SELECT 34 ,56,NULL
GO
--创建用户定义函数
create function f_getlevel(@ParentID INT)
returns INT
as
begin
declare @i INT,@ID INT
set @i = 1
while exists(select 1 from BOM where CategoryID=@ParentID)
begin
select @ID = ParentID from BOM where CategoryID = @ParentID
set @i = @i + 1
set @ParentID = @ID
end
return @i
end
GO
--执行查询
select CategoryID,ParentID,Depth=dbo.f_getlevel(ParentID) from BOM
--输出结果
/*
CategoryID ParentID Depth
---------- -------- -----
14 0 1
56 14 2
78 56 3
100 78 4
34 56 3
*/
