当我修改t1表打篮球的nameid顺序后例如原来打篮球的是'1,2,3'现在修改为'3,2,1' 查询结果没有变,也就是说没有对应上,如何解决呢?
create table t(id int, name varchar(10))
insert into t
select 1, '小明'union all
select 2, '小刚'union all
select 3, '小刘'union all
select 4, '小王'
create table t1(id int, class varchar(10),nameid varchar(10))
insert into t1
select 1, '打篮球','1,2,3' union all
select 2, '踢足球','2,3,4' union all
select 3, '打兵乓球','1,3,4'alter function tr_1(@name varchar(10))
returns varchar(100)
as
begin
declare @str varchar(100)
select @str=isnull(@str,'')+','+name from t where charindex(cast(id as varchar(5)),@name)>0
return stuff(@str,1,1,'')
end select id,class,dbo.tr_1(nameid) name from t1
原帖:http://topic.csdn.net/u/20100803/15/dfcd0637-ad8f-4a04-9d53-0ed2f6cc8fbe.html
returns varchar(100)
as
begin
declare @str varchar(100)
DECLARE @tb TABLE(id VARCHAR(100),NAME VARCHAR(100))
insert @tb SELECT id,name from t where charindex(cast(id as varchar(5)),@name)>0 ORDER BY charindex(cast(id as varchar(5)),@name)
select @str=isnull(@str,'')+','+[NAME] from @tb where charindex(cast(id as varchar(5)),@name)>0
return stuff(@str,1,1,'')
end select id,class,dbo.tr_1(nameid) name from t1 /*
--result
id class name
----------- ---------- ----------------------------------------------------------------------------------------------------
1 打篮球 小刘,小刚,小明
2 踢足球 小刚,小刘,小王
3 打兵乓球 小明,小刘,小王
*/
returns varchar(100)
as
begin
declare @str varchar(100)declare @table table (sort int identity, id int)
while charindex(',',@name)>0
begin
insert @table select left(@name, charindex(',',@name)-1)
set @name = stuff(@name,1,charindex(',',@name),'')
end
insert @table select @nameselect @str=isnull(@str,'')+','+name from t join @table b on t.id=b.id order by b.sort
return stuff(@str,1,1,'')
end
go
create table t(id int,[name] varchar(10))
insert into t
select 1,N'小明'union all
select 2,N'小刚'union all
select 3,N'小刘'union all
select 4,N'小王'if object_id('test.dbo.t1') is not null drop table t1
go
create table t1(id int, class varchar(10),nameid varchar(10))
insert into t1
select 1, '打篮球','1,2,3' union all
select 2, '踢足球','2,3,4' union all
select 3, '打兵乓球','3,1,4' union all
select 4, '打台球','1,3,4'
goif object_id('test.dbo.tr_1') is not null drop function tr_1
go
create function tr_1(@name varchar(100))
returns varchar(100)
as
begin
declare @id varchar(5)
while(patindex('%[0-9]%',@name)>0)
begin
set @id=substring(@name,patindex('%[0-9]%',@name),charindex(',',','+@name+',',patindex('%[0-9]%',','+@name+','))-patindex('%,[0-9]%',','+@name+',')-1)
set @name=reverse(stuff(reverse(stuff(replace(','+@name+',',','+@id+',',','+(select name from t where id=@id)+','),1,1,'')),1,1,''))
end
return @name
end
go
select id,class,dbo.tr_1(nameid) name from t1/*
id class name
------------------------------------------
1 打篮球 小明,小刚,小刘
2 踢足球 小刚,小刘,小王
3 打兵乓球 小刘,小明,小王
4 打台球 小明,小刘,小王
*/