表job
jobID jobName
1 aaa
2 bbb
3 ccc表person
pID pName jobID
1 m 1
2 n 2,3要查询的结果:
pID pName jobID
1 m aaa
2 n bbb,ccc
请大虾们指点一下哦。谢谢
jobID jobName
1 aaa
2 bbb
3 ccc表person
pID pName jobID
1 m 1
2 n 2,3要查询的结果:
pID pName jobID
1 m aaa
2 n bbb,ccc
请大虾们指点一下哦。谢谢
returns varchar(1000)
as
begin
declare @s varchar(1000)
set @s=''
select @s=@s+','+jobName from job where charindex(','+rtrim(jobID)+',',','+@jobID+',')>0
set @s=stuff(@s,1,1,'')
return @s
end
go
select pID,pName,dbo.uf_gets(jobID) as jobID from person
create table job(jobID int,jobName varchar(10))
insert job select 1,'aaa'
union all select 2,'bbb'
union all select 3,'ccc'
create table person(pID int,pName varchar(4),jobID varchar(20))
insert person select 1,'m','1'
union all select 2,'n','2,3'
---查看测试数据
select * from job
select * from person
---创建函数
create function dbo.fnTest(@jobid varchar(10))
returns varchar(8000)
as
begin
declare @sql varchar(8000)
set @sql=''
select @sql=@sql+','+[jobname] from job where charindex(cast(jobid as varchar),@jobid)>0
return stuff(@sql,1,1,'')
end
go
---查看结果
select pid,pname,dbo.fnTest(jobid) as jobID from person/*
pid pname jobID
----------- ----- ---------
1 m aaa
2 n bbb,ccc(所影响的行数为 2 行)*/
create table person(pID int,pName nvarchar(10),jobID nvarchar(100))
insert into job
select 1,'aaa' union all
select 2,'bbb' union all
select 3,'ccc'
insert into person
select 1,'m','1' union all
select 2,'n','2,3'
gocreate function f_getJobName(@jobID nvarchar(100))
returns nvarchar(100)
as
begin
declare @str nvarchar(100),@s nvarchar(200)
select @str = '',@s = '',@jobID = @jobID + ','
while len(@jobID ) > 0
begin
select @str = substring(@jobID,1,charindex(',',@jobID) - 1)
select @jobID = stuff(@jobID,1,charindex(',',@jobID),'')
select @s = @s + ',' + jobName from job where jobID = @str
end;
if len(@s) > 0
select @s = stuff(@s,1,1,'')
return @s
end;
go
select pID,pName,jobID = dbo.f_getJobName(jobID) from person
/*结果
pID pName jobID
1 m aaa
2 n bbb,ccc
*/
returns nvarchar(100)
as
begin
declare @str nvarchar(100)
set @str = ''
select @str = @str + ',' + jobName from job where charindex(rtrim(jobid),@jobid)>0
return stuff(@str,1,1,'')
end;
go