关于字符串的检索问题（续一）
现有表A
ID intString
-----------
1 17,14,15
2 13,11,16
3 Null
4 12,22,10
表B
ID Name
---------
10 abcd
11 aaaa
12 abcc
13 bbbb
14 abab
15 bcbc
16 cccc
17 acac
18 cdcd
需要得到下列结果：
ID charString
----------------
1 abab,bcbc,acac
2 aaaa,bbbb,cccc
3 Null
4 abcd,abcc
即找出表A中intString字段中用逗号分隔的数字与表B中对应的Name
如果没有则忽略，并将检索出来的字符串中的Name按ID排序.
前两天在下面帖子里问过了
http://community.csdn.net/Expert/topic/5433/5433049.xml?temp=.1254999
我现在有个想法，能不能不用建函数和临时表来实现呢？？？
因为函数也涉及到表数据，感觉不是很好
[解决办法]
--减少至2个临时表，不能再少了。。。
--建立测试数据
CREATE TABLE tb_a(ID INT,intString VARCHAR(50))
INSERT tb_a
SELECT 1, '17,14,15 ' UNION ALL
SELECT 2, '13,11,16 ' UNION ALL
SELECT 3,NULL UNION ALL
SELECT 4, '12,22,10 ' UNION ALL
SELECT 5,NULL
GO
create table tb_b(id int,name varchar(40))
insert into tb_b select 10, 'abcd '
insert into tb_b select 11, 'aaaa '
insert into tb_b select 12, 'abcc '
insert into tb_b select 13, 'bbbb '
insert into tb_b select 14, 'abab '
insert into tb_b select 15, 'bcbc '
insert into tb_b select 16, 'cccc '
insert into tb_b select 17, 'acac '
insert into tb_b select 18, 'cdcd '
go
--生成临时表
SELECT TOP 8000 id = IDENTITY(int, 1, 1) INTO temp_1 FROM syscolumns a, syscolumns b
--分拆处理
SELECT a.ID,a.intString,b.name
INTO temp_2
FROM
(
SELECT A.id, 'intString '=SUBSTRING(A.intString, B.id, CHARINDEX( ', ', A.intString + ', ', B.id) - B.id)
FROM tb_a A, temp_1 B
WHERE SUBSTRING( ', ' + A.intString, B.id, 1) = ', '
) a, tb_b b
WHERE a.intString=b.ID
UNION
SELECT ID,0,NULL FROM tb_a
WHERE intString IS NULL
ORDER BY a.ID,a.intString
SELECT * FROM temp_2
GO
--写一个聚合函数：
create function dbo.fn_Merge(@F1 int)
returns varchar(8000)
as
begin
declare @r varchar(8000)
set @r= ' '
select @r=@r+ ', '+name from temp_2 where ID=@F1
return stuff(@r,1,1, ' ')
end
go
-- 调用函数
select ID, dbo.fn_Merge(ID) as name
from temp_2
group by ID
go
--删除测试
DROP TABLE tb_a
DROP TABLE tb_b
DROP TABLE temp_1
DROP TABLE temp_2
DROP FUNCTION fn_Merge
/*
IDintStringname
-------------------------------
114abab
115bcbc
117acac
211aaaa
213bbbb
216cccc
30NULL
410abcd
412abcc
50NULL
IDname
------------------------------
1abab,bcbc,acac
2aaaa,bbbb,cccc
3NULL
4abcd,abcc
5NULL
*/
[解决办法]
楼主是更新还是查询
如果是查询用函数吧
如果是更新是可以有临时表，2005不用函数和临时表都可以实现
[解决办法]
加了OrderBy就有問題，還是再加個表變量吧。
修改後，還是在一個函數中完成。
Create Table A
(IDInt,
intStringVarchar(100))
Insert A Select 1, '17,14,15 '
Union All Select 2, '13,11,16 '
Union All Select 3, Null
Union All Select 4, '12,22,10 '
Create Table B
(ID Int,
NameVarchar(100))
Insert B Select 10, 'abcd '
Union All Select 11, 'aaaa '
Union All Select 12, 'abcc '
Union All Select 13, 'bbbb '
Union All Select 14, 'abab '
Union All Select 15, 'bcbc '
Union All Select 16, 'cccc '
Union All Select 17, 'acac '
Union All Select 18, 'cdcd '
GO
Create Function F_UpdateName(@intString Varchar(100))
Returns Varchar(100)
As
Begin
Declare @S Varchar(100)
Declare @T Table (ID Int Identity(1, 1), Name Char(1))
Declare @T2 Table (ID Int , Name Varchar(100))
Insert @T Select Top 100 Null From Syscolumns A, Syscolumns B
Select @S = ' '
Insert
@T2
Select
B.ID,
B.Name
From
(
Select
Substring(@intString, ID, CharIndex( ', ', @intString + ', ', ID) - ID) As ID
From @T
Where Substring( ', ' + @intString, ID, 1) = ', '
) A
Inner Join
B
On A.ID = B.ID
Order By B.ID
Select @S = @S + ', ' + Name From @T2 Order By ID
Select @S = Stuff(@S, 1, 1, ' ')
Return @S
End
GO
Select ID, dbo.F_UpdateName(intString) As intString From A
GO
Drop Function F_UpdateName
Drop Table A, B
--Result
/*
IDintString
1abab,bcbc,acac
2aaaa,bbbb,cccc
3NULL
4abcd,abcc
*/
