SQL查询语句,该如何处理

create table tb(fid varchar(50),fname varchar(50))insert into tb select '51','四川'insert into tb select '5101','成都'insert into tb select '510101','锦江区'create function getstr(@fid varchar(50))returns varchar(500)asbegindeclare @s varchar(500)select @s=isnull(@s+'/','')+fname from tb where charindex(fid,@fid)>0 order by len(fid)return @sendselect fid,fname,dbo.getstr(fid) as fullname from tb
------解决方案--------------------
SQL code
--> 测试数据: Tif object_id('tempdb.dbo.T') is not null drop table Tcreate table T ([fID] int,[fName] varchar(6))insert into Tselect 51,'四川' union allselect 5101,'成都' union allselect 510101,'锦江区'gocreate/*--调用示例
------解决方案--------------------
估计函数可行,

------解决方案--------------------
SQL code
--> --> (Roy_88)生成測試數據 if not object_id('T') is null    drop table TGoCreate table T([fID] int,[fName] nvarchar(3))Insert Tselect 51,N'四川' union allselect 5101,N'成都' union allselect 510101,N'锦江区'Gocreate function F_str(@FID nvarchar(10))returns nvarchar(1000)asbegin    declare @s nvarchar(1000)     select @s=''    select @[email protected]+'\'+[fName] from t where @FID like rtrim([FID])+'%' order by FIDreturn right(@s,len(@s)-1)endgoSelect FID,FName=dbo.F_str(FID) from T
------解决方案--------------------
SQL code
--> --> (Ben)生成測試數據 if not object_id('Tempdb..#T') is null    drop table #TGoCreate table #T([fID] int,[fName] nvarchar(3))Insert #Tselect 51,'四川' union allselect 5101,'成都' union allselect 510101,'锦江区' union allselect 61,'湖南' union allselect 6101,'常德' union allselect 610101,'武陵区' Go--Select * from #T----我想查询出来成这样： --[fID]      [fName]      [fullName] --51          四川          四川 --5101        成都          四川\成都 --510101      锦江区        四川\成都\锦江区 with tb as (    select fid,fname,fullname=cast(fname as varchar) from #T where len(fid)=2    union all    select a.fid,a.fname,fullname=cast(tb.fullname+'\'+a.fname as varchar) from #T a ,tb     where a.fid like cast(tb.fid as varchar)+'%' and len(a.fid)-len(tb.fid)=2)select* from tb order by cast(fid as varchar)--51        四川    四川--5101        成都    四川\成都--510101    锦江区    四川\成都\锦江区--61        湖南    湖南--6101        常德    湖南\常德--610101    武陵区    湖南\常德\武陵区
------解决方案--------------------
--> 测试数据: T
if object_id('t_Area') is not null drop table t_Area
	create table t_Area (fID nvarchar(50),fName nvarchar(50))
insert into t_Area(fid,fname)
select 51,N'四川' union all
select 5101,N'成都' union all
select 510101,N'锦江区' union all
select 61,N'陕西' union all
select 6101,N'西安' union all
select 610101,N'雁塔区'

--取所需要的数据放入临时表
if OBJECT_ID('tempdb..#T') is not null drop table #T
	create table #T(FID nvarchar(20),FName nvarchar(50),FFullName NVarchar(100))
insert into #T(FID,FName,FFullName)
	select fid,FName,'' as FFullName
	from t_Area
	order by fid

declare @s nvarchar(100)
declare @v nvarchar(50)
set @s=''
set @v=null