sql 根据子级ID获取所有父级
CREATE FUNCTION [dbo].[f_GetParentCode](@id int)
RETURNS @re TABLE(id int,pid int,level int)
AS
begin
declare @level int
set @level = 1
declare @pid int
select @pid = ParentCode from T_GWZJ_CityMapping where HCityCode = @id
insert @re
select HCityCode,ParentCode,@level from T_GWZJ_CityMapping where HCityCode = @id
set @level = @level + 1
insert @re
select HCityCode,ParentCode,@level from T_GWZJ_CityMapping where HCityCode = @pid
while @@rowcount > 0
begin
set @level = @level + 1
select @pid = ParentCode from T_GWZJ_CityMapping where HCityCode = @pid
insert @re
select HCityCode,ParentCode,@level from T_GWZJ_CityMapping where HCityCode = @pid
end
return
end
GO
更多精彩
