日期:2014-05-17 浏览次数:20582 次
----------------------------------------------------------------
-- 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
*/
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&