TABLE A
ID NAME
1 zhangsan
2 lisi
3 wangwu
TABLE B A PID CONTENT
1 1 1111
2 1 2222
3 1 3333
4 2 1122
5 2 2233
6 3 5555查找后的結果
ID NAME CONTENT
1 zhangshan 1111,2222,333
2 lisi 1122,2233
3 wangwu 5555
ID NAME
1 zhangsan
2 lisi
3 wangwu
TABLE B A PID CONTENT
1 1 1111
2 1 2222
3 1 3333
4 2 1122
5 2 2233
6 3 5555查找后的結果
ID NAME CONTENT
1 zhangshan 1111,2222,333
2 lisi 1122,2233
3 wangwu 5555
-->Microsoft SQL Server Management Studio Complete 2008***
-->AUTHOR : Mr wang **********
-->CREATE TIME : 2010-11-19 14:05:43 **************
-->*******************************************************
--> 测试数据:TABLEA
if object_id('TABLEA') is not null
drop table TABLEA---->建表
create table TABLEA([ID] int,[NAME] varchar(8))
insert TABLEA
select 1,'zhangsan' union all
select 2,'lisi' union all
select 3,'wangwu'
--> 测试数据:TABLEB
if object_id('TABLEB') is not null
drop table TABLEB---->建表
create table TABLEB([A] int,[PID] int,[CONTENT] int)
insert TABLEB
select 1,1,1111 union all
select 2,1,2222 union all
select 3,1,3333 union all
select 4,2,1122 union all
select 5,2,2233 union all
select 6,3,5555
--> 查询结果
SELECT a.ID,a.NAME,
CONTENT =stuff((
select ','+convert(varchar(20),CONTENT) FROM (SELECT a.ID,a.NAME,b.CONTENT
FROM TABLEA a join TABLEB b
on a.ID=b.PID)b WHERE ID = a.ID for xml path(''))
,1,1,'')
FROM (SELECT a.ID,a.NAME,b.CONTENT
FROM TABLEA a join TABLEB b
on a.ID=b.PID) a
group by a.ID,a.NAME
--> 删除表格
--DROP TABLE TABLEA
--> 删除表格
--DROP TABLE TABLEB
-->*******************************************************
-->Microsoft SQL Server Management Studio Complete 2008***
-->AUTHOR : Mr wang **********
-->CREATE TIME : 2010-11-19 14:05:43 **************
-->*******************************************************
--> 测试数据:TABLEA
if object_id('TABLEA') is not null
drop table TABLEA---->建表
create table TABLEA([ID] int,[NAME] varchar(8))
insert TABLEA
select 1,'zhangsan' union all
select 2,'lisi' union all
select 3,'wangwu'
--> 测试数据:TABLEB
if object_id('TABLEB') is not null
drop table TABLEB---->建表
create table TABLEB([A] int,[PID] int,[CONTENT] int)
insert TABLEB
select 1,1,1111 union all
select 2,1,2222 union all
select 3,1,3333 union all
select 4,2,1122 union all
select 5,2,2233 union all
select 6,3,5555
--> 查询结果
SELECT a.ID,a.NAME,
CONTENT =stuff((
select ','+convert(varchar(20),CONTENT) FROM (SELECT a.ID,a.NAME,b.CONTENT
FROM TABLEA a join TABLEB b
on a.ID=b.PID)b WHERE ID = a.ID for xml path(''))
,1,1,'')
FROM (SELECT a.ID,a.NAME,b.CONTENT
FROM TABLEA a join TABLEB b
on a.ID=b.PID) a
group by a.ID,a.NAME
--> 删除表格
--DROP TABLE TABLEA
--> 删除表格
--DROP TABLE TABLEB
GO
IF OBJECT_ID('TB') IS NOT NULL DROP TABLE TB
GO
CREATE TABLE TA
(
ID INT,
NAME NVARCHAR(50)
)
CREATE TABLE TB
(
ID INT,
PID INT,
CONTENT NVARCHAR(50)
)INSERT INTO TA
SELECT 1,'ZHANGSAN'UNION ALL
SELECT 2,'LIZI'UNION ALL
SELECT 3,'WANGWU'INSERT INTO TB
SELECT 1,1,'1111' UNION ALL
SELECT 2,1,'2222' UNION ALL
SELECT 3,1,'3333' UNION ALL
SELECT 4,2,'1122' UNION ALL
SELECT 5,2,'2233' UNION ALL
SELECT 6,3,'5555'SELECT TA.ID,TA.NAME
,STUFF((SELECT ','+ CONTENT FROM TB WHERE TA.ID=TB.PID FOR XML PATH('')),1,1,'')
FROM TA--------------------
ID NAME (无列名)
1 ZHANGSAN 1111,2222,3333
2 LIZI 1122,2233
3 WANGWU 5555
--自己写写吧
create table #a
(
id int,
name varchar(10)
)
create table #b
(
a int,
pid int,
[content] varchar(10)
)
insert into #a select 1, 'zhangsan'
insert into #a select 2, 'lisi'
insert into #a select 3, 'wangwu'
insert into #b select 1, 1, '1111'
insert into #b select 2, 1, '2222'
insert into #b select 3, 1, '3333'
insert into #b select 4, 2, '1122'
insert into #b select 5, 2, '2233'
insert into #b select 6, 3, '5555'--查询
SELECT
t.id,t.name,
[content] = STUFF(
(SELECT ',' + b.[content]
FROM #a a inner join #b b on a.id = b.pid
WHERE a.id = t.id
FOR XML PATH('')),
1,1,'')
FROM #a t
GROUP BY t.id,t.name/*
id name content
----------- ---------- -----------------
1 zhangsan 1111,2222,3333
2 lisi 1122,2233
3 wangwu 5555(3 行受影响)*/
if object_id('ta') is not null
drop table ta
go
create table ta(id int ,name varchar(10))
insert into ta
select 1, 'zhangsan' union all
select 2, 'lisi' union all
select 3, 'wangwu'if object_id('tb') is not null
drop table tb
go
create table tb( a int,pid int,content int)
insert into tb
select 1, 1, 1111 union all
select 2, 1, 2222 union all
select 3, 1, 3333 union all
select 4, 2, 1122 union all
select 5, 2, 2233 union all
select 6, 3, 5555select distinct a.id,a.name, content=stuff((select ','+ltrim(content)
from tb
where pid=b.pid for xml path('')),1,1,'')
from ta a join tb b on a.id=b.pid
if object_id('ta') is not null
drop table ta
go
create table ta(id int ,name varchar(10))
insert into ta
select 1, 'zhangsan' union all
select 2, 'lisi' union all
select 3, 'wangwu'if object_id('tb') is not null
drop table tb
go
create table tb( a int,pid int,content int)
insert into tb
select 1, 1, 1111 union all
select 2, 1, 2222 union all
select 3, 1, 3333 union all
select 4, 2, 1122 union all
select 5, 2, 2233 union all
select 6, 3, 5555create function dbo.f_str(@id varchar(10)) returns varchar(1000)
as
begin
declare @str varchar(1000)
select @str = isnull(@str + ',' , '') +content from Ta left join tb on ta.id=tb.id where ta.id = @id
return @str
end
go--调用函数
select id , Content= dbo.f_str(id) from ta group by id
create table b(a int,pid int,content varchar(10))
insert into a
select 1, 'zhangsan' union all
select 2, 'lisi'union all
select 3 ,'wangwu'
insert into b
select 1 ,1 ,'1111' union all
select 2, 1, '2222' union all
select 3, 1, '3333' union all
select 4, 2, '1122' union all
select 5, 2, '2233' union all
select 6 ,3, '5555'
goWITH tmp
as(
select pid,
content=stuff((select ',' + content from B t where t.pid=B.pid for xml path('')) ,1,1,'')
from B group by PID)
select a.id,a.name,tmp.content from a,tmp where a.id=tmp.pid
2 lisi 1122,2233
3 wangwu 5555