求一个Sql联系查询问题

Question

如现有A，B两表。B表的a1字段关联如A表的a1字段
A表
a1    a2
1     中国
2     美国
3     英国
B表
id    a1   name
1     1    刘某某
2     1    李某某
3     1    黄某某
4     2    Mr.
5     3    Sms.
6     3    Dkkk.
现要返回记录的情况如下
1  中国   刘某某李某某黄某某
2  美国   Mr.
3  英国   Sms.Dkkk.语句应该怎么写

Answer 1

if object_id('tbTestA') is not null
drop table tbTestA
if object_id('tbTestB') is not null
drop table tbTestB
if object_id('fnMerge') is not null
drop function fnMerge
GO
create table tbTestA(a1 int,    a2 varchar(10))
insert tbTestA
select 1,     '中国' union all
select 2,     '美国' union all
select 3,     '英国'
create table tbTestB(id int,    a1 int,  name varchar(10))
insert tbTestB
select 1,     1,    '刘某某' union all
select 2,     1,    '李某某' union all
select 3,     1,    '黄某某' union all
select 4,     2,    'Mr.' union all
select 5,     3,    'Sms.' union all
select 6,     3,    'Dkkk.'
GO
----创建字符串连接函数
create function fnMerge(@id int)
returns varchar(8000)
as
begin
    declare @str varchar(8000)
    set @str = ''
    select @str = @str + ',' + name from tbTestB where a1 = @id
    return stuff(@str,1,1,'')
end
GO
----查询
SELECT a1,a2,dbo.fnMerge(a1) FROM tbTestA
----清除测试环境
drop table tbTestA,tbTestB
drop function fnMerge/*结果
a1          a2                                
----------- ---------- -----------------------
1           中国         刘某某,李某某,黄某某
2           美国         Mr.
3           英国         Sms.,Dkkk.
*/

Answer 2

create table a(a1 int,a2 varchar(20))
insert into a(a1,a2)
select 1,' 中国'
union all select 2,' 美国'
union all select 3,' 英国'create table b ([id] int, a1 int,name  varchar(20))
insert into b([id],a1,name)
select 1,     1,    '刘某某'
union all select 2,     1,    '李某某'
union all select 3,     1,    '黄某某'
union all select 4,     2,    'Mr.'
union all select 5,     3,   'Sms.'
union all select 6,     3,   ' Dkkk.'alter function f_hb(@id int)
returns varchar(200)
begin
declare @exec varchar(800)
set @exec=''
select  @exec=@exec+',' + name  from b where a1=@id
return (@exec)
endselect  a1, dbo.f_hb(a1) as name  from b   group by a1select * from bdrop table a
drop table b

Answer 3

如果姓名之间不需要逗号,请这样:
if object_id('tbTestA') is not null
drop table tbTestA
if object_id('tbTestB') is not null
drop table tbTestB
if object_id('fnMerge') is not null
drop function fnMerge
GO
create table tbTestA(a1 int,    a2 varchar(10))
insert tbTestA
select 1,     '中国' union all
select 2,     '美国' union all
select 3,     '英国'
create table tbTestB(id int,    a1 int,  name varchar(10))
insert tbTestB
select 1,     1,    '刘某某' union all
select 2,     1,    '李某某' union all
select 3,     1,    '黄某某' union all
select 4,     2,    'Mr.' union all
select 5,     3,    'Sms.' union all
select 6,     3,    'Dkkk.'
GO
----创建字符串连接函数
create function fnMerge(@id int)
returns varchar(8000)
as
begin
    declare @str varchar(8000)
    set @str = ''
    select @str = @str + name from tbTestB where a1 = @id
    return @str
end
GO
----查询
SELECT a1,a2,dbo.fnMerge(a1) FROM tbTestA
----清除测试环境
drop table tbTestA,tbTestB
drop function fnMerge/*结果
a1          a2                              
----------- ---------- ---------------------
1           中国         刘某某李某某黄某某
2           美国         Mr.
3           英国         Sms.Dkkk.
*/

Answer 4

select  a.*,b.name  from a,(select  a1, dbo.f_hb(a1) as name  from b   group by a1) b where a.a1=b.a1/*
a1        a2               name
-------------------------------------------
1  中国 ,刘某某,李某某,黄某某
2  美国 ,Mr.
3  英国 ,Sms., Dkkk.
*/