下面的递归函数实现查找顶级ID:
create table A
(
Area_id int,Area_area_id int,Name varchar(10)
)
insert A
select 1,4,'广东' union
select 2,1,'广州' union
select 3,1,'深圳' union
select 4,0,'中国' union
select 5,2,'天河'
go create function gettop
(
@Area_id int
)
returns int
as
begin
if exists(select 1 from A where Area_id=@Area_id and Area_area_id=0)
return @Area_id
declare @tmp int
select @tmp=Area_area_id from A where Area_id=@Area_id
return dbo.gettop(@tmp)
end
go--测试select dbo.gettop(5)
create table A
(
Area_id int,Area_area_id int,Name varchar(10)
)
insert A
select 1,4,'广东' union
select 2,1,'广州' union
select 3,1,'深圳' union
select 4,0,'中国' union
select 5,2,'天河'
go create function gettop
(
@Area_id int
)
returns int
as
begin
if exists(select 1 from A where Area_id=@Area_id and Area_area_id=0)
return @Area_id
declare @tmp int
select @tmp=Area_area_id from A where Area_id=@Area_id
return dbo.gettop(@tmp)
end
go--测试select dbo.gettop(5)
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货