INSERT INTO TABLE1(ID1,NUM1) VALUES(1,'2,32')
INSERT INTO TABLE1(ID1,NUM1) VALUES(2,'4,3')
INSERT INTO TABLE2(NUM2,NAME2) VALUES(2,'小李')
INSERT INTO TABLE2(NUM2,NAME2) VALUES(3,'小周')
INSERT INTO TABLE2(NUM2,NAME2) VALUES(4,'小吴')
INSERT INTO TABLE2(NUM2,NAME2) VALUES(32,'小王')
CREATE FUNCTION RETURN_NAME(@NUM1 VARCHAR(20))
RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @RTVALUE VARCHAR(20)
SET @RTVALUE = ''
DECLARE @TEMP VARCHAR(20)
WHILE(LEN(@NUM1)>0)
BEGIN
IF(CHARINDEX(',',LTRIM(@NUM1))=0)
BEGIN
SELECT @TEMP = NAME2 FROM TABLE2 WHERE NUM2 = @NUM1
SET @NUM1 = ''
END
ELSE
BEGIN
SELECT @TEMP = NAME2 FROM TABLE2 WHERE NUM2 = SUBSTRING(@NUM1,1,CHARINDEX(',',@NUM1)-1)
END
SET @RTVALUE = @RTVALUE + @TEMP + ','
SET @NUM1 = SUBSTRING(@NUM1,CHARINDEX(',',@NUM1)+1,LEN(@NUM1)-CHARINDEX(',',@NUM1))
END
RETURN SUBSTRING(@RTVALUE,1,LEN(@RTVALUE)-1)
END
加个函数吧
CREATE FUNCTION dbo.returnname (@a varchar(1000))
RETURNS varchar(1000)
AS
BEGIN
DECLARE @splitlen int
declare @split char
set @split = ','
declare @re table(col varchar(50))
SET @splitlen=LEN(@split+'a')-2
WHILE CHARINDEX(@split,@a)>0
BEGIN
INSERT @re VALUES(LEFT(@a,CHARINDEX(@split,@a)-1))
SET @a=STUFF(@a,1,CHARINDEX(@split,@a)+@splitlen,'')
END
INSERT @re VALUES(@a)
set @a = ''
select @a = @a + b.uname + ',' from @re,b where col = b.uno
if @a <> ''
set @a = left(@a,len(@a) - 1)
RETURN @a
END
=================
执行
--======================测试数据
create table a (id int,uno varchar(50))
insert into a select '1','2,32'
union all select '2','4,3'
create table b (uno int,uname varchar(50))
insert into b select '2','小李'
union all select '3','小周'
union all select '4','小吴'
union all select '32','小王'
--==============================
这样子写:
先写个分隔的函数:
CREATE function fun_SplitString
(
@Str VARCHAR(8000),
@Chr varchar(10)
)
Returns @TempTable TABLE(String VARCHAR(20))
--实现split功能的函数
--说明:@aString,字符串,如“27,28,29”;@pattern,分隔标志,如“ ,”
as
begin
declare @i int
set @Str=rtrim(ltrim(@Str))
set @i=charindex(@Chr,@Str)
while @i>=1
begin
insert @TempTable values(left(@Str,@i-1))
set @Str=substring(@Str,@i+1,len(@Str)-@i)
set @i=charindex(@Chr,@Str)
end
if @Str<>''
insert @TempTable values(@Str)
return
end
---------------
再写个合并函数:
CREATE FUNCTION [dbo].[fun_UniteName]
(
-- Add the parameters for the function here
@id int
)
RETURNS nvarchar(255)
AS
BEGIN
-- Declare the return variable here
Declare @strchars nvarchar(255)
set @strchars=''
SET @strchars = (Select Name from 表 where ID=@id)
DECLARE @message nvarchar(255)
SET @message =''
if(rtrim(@strchars) !='')
begin
DECLARE @place nvarchar(50)
DECLARE place_cursor CURSOR FOR
Select String from dbo.fun_SplitString(@strchars,',')
open place_cursor
FETCH NEXT FROM place_cursor INTO @place
WHILE @@FETCH_STATUS = 0
BEGIN
select @message = @message+ ' ' + (select name from 表2 where id=@place )
FETCH NEXT FROM place_cursor INTO @place
END
CLOSE place_cursor
DEALLOCATE place_cursor
end
-- Return the result of the function
return @message