有两个表,主从关系,如何得到如下的查询结果:A表:
a b
1 a
2 bB表:
c d e
5 1 c
6 1 d
7 2 e
8 2 f查询结果(A表的a字段关联B表的d字段,合并B表的e字段,内容用","号隔开):
1 a c,d
2 b e,f
a b
1 a
2 bB表:
c d e
5 1 c
6 1 d
7 2 e
8 2 f查询结果(A表的a字段关联B表的d字段,合并B表的e字段,内容用","号隔开):
1 a c,d
2 b e,f
a b
-----------------------
1 a
2 bB表:
c d e
---------------------------------------
5 1 c
6 1 d
7 2 e
8 2 f
查询结果(A表的a字段关联B表的d字段,合并B表的e字段,内容用","号隔开):
1 a c,d
2 b e,f
returns varchar(50)
as
begin
declare @s varchar(50)
set @s=''
select @s=@s+','+[e] from B表 where [d]=@a
if @s<>''
set @s=stuff(@s,1,1,'')
return @s
endGOselect [a],[b],dbo.fn_str([a]) as [e]
from A表
CREATE FUNCTION f(id int)
RETURNS VARCHAR
ASDECLARE ret varchar(100)
set ret = ''
select ret = ret + ',' + e
from b
where d = idset ret = substring(ret,1,len(ret))
select a.*,t.d
from a
left join (select d,f(d) as d from b group by d) t
on a.a = t.d
returns varchar(100)
as
begin
declare @result varchar(100)
set @result=''
select @result=@result+','+e from B where d=@标识号
return right(@result,len(@result)-1)
end
goselect *,dbo.getresult(a)
from a
insert A select 1, 'a'
union all select 2, 'b'create table B(c int, d int, e char(1))
insert B select 5, 1, 'c'
union all select 6, 1, 'd'
union all select 7, 2, 'e'
union all select 8, 2, 'f' create function fun(@d int)
returns varchar(1000)
as
begin
declare @re varchar(1000)
set @re=''
select @re=@re+e+',' from B where d=@d
set @re=left(@re, len(@re)-1)
return @re
endselect a, b, dbo.fun(a) as e from A--result
a b e
----------- ---- ------------
1 a c,d
2 b e,f(2 row(s) affected)
函數應該是最好的解決方法,
或者你可以用游標跑循環,
或者臨時表