create table ta (id int, value varchar(20)) Insert into ta select '1','aaa' union all select '2','bbb' union all select '3','ccc'create table tb (id int, value varchar(20)) Insert into tb select '1','aaa,bbb,' union all select '2','bbb,ccc,' union all select '3','aaa,bbb,ccc,'select * from ta select * from tb--函數 create function dbo.fn_m(@vchstring varchar(1000)) returns varchar(1000) as begin declare @intstart int,@intlocation int declare @s varchar(1000),@vchsubstring varchar(100)
select @intstart =1,@s='' select @intlocation = charindex(',',@vchstring,@intstart) while (@intlocation <>0 ) begin select @vchsubstring=substring(@vchstring,@intstart,@intlocation-@intstart) select @s=@s+cast([id] as varchar)+',' from ta where value=@vchsubstring select @intstart = @intlocation +1 select @intlocation = charindex(',',@vchstring,@intstart) end return(@s) end--刪除 drop table ta drop table tb drop function dbo.fn_mselect id, value=dbo.fn_m(value) from tb --結果 id value ------------------ 1 1,2, 2 2,3, 3 1,2,3, 这是一个类似的例子!!
你的表结构和你的查询要求我没有看明白。下面的参考是合并字段值的例子,供参考。http://community.csdn.net/Expert/topic/3245/3245226.xml?temp=9.682864E-02
Insert into ta
select '1','aaa'
union all select '2','bbb'
union all select '3','ccc'create table tb (id int, value varchar(20))
Insert into tb
select '1','aaa,bbb,'
union all select '2','bbb,ccc,'
union all select '3','aaa,bbb,ccc,'select * from ta
select * from tb--函數
create function dbo.fn_m(@vchstring varchar(1000))
returns varchar(1000)
as
begin
declare @intstart int,@intlocation int
declare @s varchar(1000),@vchsubstring varchar(100)
select @intstart =1,@s=''
select @intlocation = charindex(',',@vchstring,@intstart)
while (@intlocation <>0 )
begin
select @vchsubstring=substring(@vchstring,@intstart,@intlocation-@intstart)
select @s=@s+cast([id] as varchar)+',' from ta where value=@vchsubstring
select @intstart = @intlocation +1
select @intlocation = charindex(',',@vchstring,@intstart)
end
return(@s)
end--刪除
drop table ta
drop table tb
drop function dbo.fn_mselect id, value=dbo.fn_m(value) from tb
--結果
id value
------------------
1 1,2,
2 2,3,
3 1,2,3,
这是一个类似的例子!!
1007 n1007
1006 n1006
1005 n1005
1003 n1003
1001 n1001本来想用 distinct b_link 来处理表B的,但是这样的结果是排序按b_link 来了,不是我所希望的 b_id