34,590
社区成员
发帖
与我相关
我的任务
分享
----------------------------------------------------------------
-- Author :DBA_Huangzj(發糞塗牆)
-- Date :2013-12-12 21:19:45
-- 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]([a_id] int,[a_name] int)
insert [a]
select 1,11 union all
select 2,12
--> 测试数据:[B]
if object_id('[B]') is not null drop table [B]
go
create table [B]([b_id] int,[a_id] int,[b_name] varchar(2))
insert [B]
select 1,1,'aa' union all
select 2,1,'bb' union all
select 3,2,'cc' union all
select 4,2,'dd'
--------------开始查询--------------------------
;WITH ym AS
(select a.a_name, b_name
from [a] INNER JOIN [b] ON a.a_id=b.a_id)
select a.a_name,
stuff((select ','+b_name from ym b
where b.a_name=a.a_name
for xml path('')),1,1,'') 'b_name'
from ym a
group by a.a_name
----------------结果----------------------------
/*
a_name b_name
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
11 aa,bb
12 cc,dd
*/
CREATE TABLE a(a_id int,a_name int)
INSERT INTO a
select 1, 11 UNION all
select 2, 12
CREATE TABLE b(b_id INT,a_id INT,b_name VARCHAR(50))
INSERT INTO b
select 1, 1, 'aa' UNION ALL
select 2, 1, 'bb' UNION ALL
select 3, 2, 'cc' UNION ALL
select 4, 2, 'dd'
go
select distinct
a_name ,
STUFF((select ','+b_name from b bb
where bb.a_id = a.a_id
for xml path('')),1,1,'') as b_name
from a
inner join b
on a.a_id = b.a_id
/*
a_name b_name
11 aa,bb
12 cc,dd
*/
CREATE TABLE #a(a_id int,a_name int)
INSERT INTO #a
select 1, 11 UNION all
select 2, 12
CREATE TABLE #b(b_id INT,a_id INT,b_name VARCHAR(50))
INSERT INTO #b
select 1, 1, 'aa' UNION ALL
select 2, 1, 'bb' UNION ALL
select 3, 2, 'cc' UNION ALL
select 4, 2, 'dd'
;WITH cte AS
(
SELECT #a.a_name,#b.b_name FROM #a
LEFT JOIN #b ON #a.a_id=#b.a_id
)
SELECT a_name,
(SELECT STUFF((
SELECT ','+b_name FROM cte b WHERE a.a_name=b.a_name FOR XML PATH('')),1,1,''))b_name
FROM cte a
GROUP BY a_name
a_name b_name
----------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
11 aa,bb
12 cc,dd
(2 行受影响)