求一条连表查询的SQL语句

[va702b]

表A
结构如下
ID Name
1 小五
2 小李


表B结构如下 B.USER==A.ID
ID USER RESUME
1 1 AAAAAA
2 1 bbbbbb
3 1 cccccc
4 2 dddddd
5 2 eeeeee

有没有可能用一条查询语句得到如下结果
ID Name RESUME
1 小五 1,2,3
2 小李 4,5

将关联的表B的某个字段全部放入到查询中的一个字段中？

[發糞塗牆]

sql server什么版本？

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2014-02-08 17:31:27
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([ID] int,[Name] varchar(4))
insert [A]
select 1,'小五' union all
select 2,'小李'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([ID] int,[USER] int,[RESUME] varchar(6))
insert [B]
select 1,1,'AAAAAA' union all
select 2,1,'bbbbbb' union all
select 3,1,'cccccc' union all
select 4,2,'dddddd' union all
select 5,2,'eeeeee'
--------------开始查询--------------------------





select a.id,a.name,
stuff((select ','+CAST([RESUME] AS VARCHAR) from (select a.id,a.name,b.id AS [RESUME]
from [A] INNER JOIN B ON  B.[USER]=A.ID) b 
       where b.id=a.id and b.name=a.name 
       for xml path('')),1,1,'') [RESUME]
from (select a.id,a.name,b.id AS [RESUME]
from [A] INNER JOIN B ON  B.[USER]=A.ID) a
group by  a.id,a.name
----------------结果----------------------------
/* 
id          name RESUME
----------- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           小五   1,2,3
2           小李   4,5

*/

[發糞塗牆]

----------------------------------------------------------------
-- Author  :DBA_Huangzj(發糞塗牆)
-- Date    :2014-02-08 17:31:27
-- Version:
--      Microsoft SQL Server 2012 (SP1) - 11.0.3128.0 (X64) 
--	Dec 28 2012 20:23:12 
--	Copyright (c) Microsoft Corporation
--	Enterprise Edition (64-bit) on Windows NT 6.2 <X64> (Build 9200: )
--
----------------------------------------------------------------
--> 测试数据：[A]
if object_id('[A]') is not null drop table [A]
go 
create table [A]([ID] int,[Name] varchar(4))
insert [A]
select 1,'小五' union all
select 2,'小李'
--> 测试数据：[B]
if object_id('[B]') is not null drop table [B]
go 
create table [B]([ID] int,[USER] int,[RESUME] varchar(6))
insert [B]
select 1,1,'AAAAAA' union all
select 2,1,'bbbbbb' union all
select 3,1,'cccccc' union all
select 4,2,'dddddd' union all
select 5,2,'eeeeee'
--------------开始查询--------------------------
--2005
select a.id,a.name,
stuff((select ','+CAST([RESUME] AS VARCHAR) from (select a.id,a.name,b.id AS [RESUME]
from [A] INNER JOIN B ON  B.[USER]=A.ID) b 
       where b.id=a.id and b.name=a.name 
       for xml path('')),1,1,'') [RESUME]
from (select a.id,a.name,b.id AS [RESUME]
from [A] INNER JOIN B ON  B.[USER]=A.ID) a
group by  a.id,a.name


--2000
--创建函数：
if object_id('F_Str') is not null
    drop function F_Str
go
create function F_Str(@Col1 nvarchar(100))
returns nvarchar(100)
as
begin
    declare @S nvarchar(100)
    select @S=isnull(@S+',','')+CAST([RESUME] AS nvarchar(100)) from (select a.id,a.name,b.id AS [RESUME]
from [A] INNER JOIN B ON  B.[USER]=A.ID) a where name=@Col1
    return @S
end
go
Select distinct id,name,[RESUME]=dbo.F_Str(name) from (select a.id,a.name,b.id AS [RESUME]
from [A] INNER JOIN B ON  B.[USER]=A.ID) a


----------------结果----------------------------
/* 
id          name RESUME
----------- ---- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1           小五   1,2,3
2           小李   4,5

(2 row(s) affected)

id          name RESUME
----------- ---- ----------------------------------------------------------------------------------------------------
1           小五   1,2,3
2           小李   4,5
*/

[va702b]

谢谢楼上各位

[LongRui888]

适合sql server 2005及以上的版本：

if object_id('[A]') is not null drop table [A]
go 
create table [A]([ID] int,[Name] varchar(4))
insert [A]
select 1,'小五' union all
select 2,'小李'

if object_id('[B]') is not null drop table [B]
go 
create table [B]([ID] int,[USER] int,[RESUME] varchar(6))
insert [B]
select 1,1,'AAAAAA' union all
select 2,1,'bbbbbb' union all
select 3,1,'cccccc' union all
select 4,2,'dddddd' union all
select 5,2,'eeeeee'
go


select ID,
       Name,
       STUFF((select ','+cast(b.id as varchar) 
              from b where a.id = b.[user]
              for xml path('')),1,1,'') as resume
from a
/*
ID	Name	resume
1	小五	1,2,3
2	小李	4,5
*/