34,590
社区成员
发帖
与我相关
我的任务
分享
Create Table a
(id int identity(1,1), [name] varchar(50), des varchar(50), parentid int, [path] varchar(30))
insert a
select 'Zhang San', 'Employee', null, null
union all select 'Li Si', 'Employee', 1, null
union all select 'Xiao Hua', 'Staff', 2, null
union all select 'Xiao Meng', 'Staff', 1, null
create function GetPath(@id int)
returns varchar(30)--path字段的大小
as
begin
declare @parentid int
declare @path varchar(30)
select @parentid=[parentid] from a where object_name(id)= @id
select @path=[path] from a where object_name(id)= @id
if @parentid is not null
set @path = @path + '>' + dbo.GetPath(@parentid)
return @path
end
GO
update a set [path]=(select dbo.GetPath(id))
楼上 welove1983兄弟那个问题我也回帖了,哈哈,挺巧
CREATE table a(id int, parentID int, sClassName nvarchar(20))
insert into a
select 1, 0, '1111'
union all select 2, 1, '1111_1'
union all select 3, 2, '1111-1-1'
union all select 4, 3, '1111-1-1-1'
union all select 5, 1, '1111-2'
create FUNCTION getParentID(@id INT)
RETURNS NVARCHAR(50)
AS
BEGIN
declare @parentid int
DECLARE @allParentID NVARCHAR(50)
set @allParentID=''
select @parentid=[parentid] from a where id= @id
IF(@parentid <>0)
begin
set @allParentID = cast(@parentid as nvarchar) +','+ DBO.getParentID(@parentid)
end
return cast(@allParentID as nvarchar)
END
SELECT 'ParentID'=dbo.getParentID(4)
/*
ParentID
--------------------------------------------------
3,2,1,
*/
楼上兄弟那个问题我也回帖了,哈哈,挺巧
CREATE table a(id int, parentID int, sClassName nvarchar(20))
insert into a
select 1, 0, '1111'
union all select 2, 1, '1111_1'
union all select 3, 2, '1111-1-1'
union all select 4, 3, '1111-1-1-1'
union all select 5, 1, '1111-2'
create FUNCTION getParentID(@id INT)
RETURNS NVARCHAR(50)
AS
BEGIN
declare @parentid int
DECLARE @allParentID NVARCHAR(50)
set @allParentID=''
select @parentid=[parentid] from a where id= @id
IF(@parentid <>0)
begin
set @allParentID = cast(@parentid as nvarchar) +','+ DBO.getParentID(@parentid)
end
return cast(@allParentID as nvarchar)
END
SELECT 'ParentID'=dbo.getParentID(4)
/*
ParentID
--------------------------------------------------
3,2,1,
*/