求一条SQL汇总语句

[csdn_风中雪狼]

--有如下三张表（t_people 人员表、t_room 房间表 、t_people_room 入住信息 ）

--t_people 人员信息
p_id		p_name 
70304           张三
70716           李四
80184           王五
80983           杨七
10026           钱八
11005		工一
12056		黄九
14052		叶儿
13087		李二

--t_room 房间信息
roomid		roomname
1		201
2		202
3		203
4		205
5		206
6		214

--t_people_room 入住信息
id 	p_id	roomid
1	70716	1
2	70304	2
3	80184	2
4	80983	2
5	10026	6
6	11005   4
7	12056	4
8	14052	5

--现在统计要每个房间的入住人员信息,希望得如下统计结果
roomid	roomname	peoples
1	201		李四
2	202		张三、王五、杨七
3	203
4	205		工一、黄九	
5	206		叶儿
6	214		钱八

--希望用一条语句得到每个房间入住过人员信息
--数据库为 Ms sqL 2005 

[SQL77]

SQL字符串合并精华....搜索

[csdn_风中雪狼]

引用 1 楼 SQL77 的回复:

SQL字符串合并精华....搜索

搜索了一圈 没有结果，才来这里学习的，

[叶子]

--> 测试数据: @t_people
declare @t_people table (p_id int,p_name varchar(4))
insert into @t_people
select 70304,'张三' union all
select 70716,'李四' union all
select 80184,'王五' union all
select 80983,'杨七' union all
select 10026,'钱八' union all
select 11005,'工一' union all
select 12056,'黄九' union all
select 14052,'叶儿' union all
select 13087,'李二'

--> 测试数据: @t_room
declare @t_room table (roomid int,roomname int)
insert into @t_room
select 1,201 union all
select 2,202 union all
select 3,203 union all
select 4,205 union all
select 5,206 union all
select 6,214

--> 测试数据: @t_people_room
declare @t_people_room table (id int,p_id int,roomid int)
insert into @t_people_room
select 1,70716,1 union all
select 2,70304,2 union all
select 3,80184,2 union all
select 4,80983,2 union all
select 5,10026,6 union all
select 6,11005,4 union all
select 7,12056,4 union all
select 8,14052,5

;with maco as
(
select c.roomid,a.* from @t_people_room c left join @t_people a on c.p_id=a.p_id
)
select m.*,isnull(n.peoples,'') as peoples from @t_room m
left join (
select roomid, peoples=stuff((select ','+p_name from maco where roomid=t.roomid for xml path('')), 1, 1, '')
from maco t
group by roomid) n on m.roomid=n.roomid

/*
roomid      roomname    peoples
----------- ----------- ----------------------
1           201         李四
2           202         张三,王五,杨七
3           203         
4           205         工一,黄九
5           206         叶儿
6           214         钱八
*/

[jack15850798154]

CREATE TABLE t_people
(
   p_id INT,
   p_name VARCHAR(50)
)
INSERT INTO t_people
SELECT 70304,'张三'
UNION ALL SELECT 70716,'李四'
UNION ALL SELECT 80184,'王五'
UNION ALL SELECT 80983,'杨七'
UNION ALL SELECT 10026,'钱八'
UNION ALL SELECT 11005,'工一'
UNION ALL SELECT 12056,'黄九'
UNION ALL SELECT 14052,'叶儿'
UNION ALL SELECT 13087,'李二'

CREATE TABLE t_room
(
   roomid INT,
   roomname INT
)

INSERT INTO t_room
SELECT 1,201
UNION ALL SELECT 2,202
UNION ALL SELECT 3,203
UNION ALL SELECT 4,205
UNION ALL SELECT 5,206
UNION ALL SELECT 6,214


CREATE TABLE t_people_room
(
   id INT,
   p_id INT,
   roomid INT
)

INSERT INTO t_people_room
SELECT 1,70716,1
UNION ALL SELECT 2,70304,2
UNION ALL SELECT 3,80184,2
UNION ALL SELECT 4,80983,2
UNION ALL SELECT 5,10026,6
UNION ALL SELECT 6,11005,4
UNION ALL SELECT 7,12056,4
UNION ALL SELECT 8,14052,5


SELECT * FROM t_people a LEFT JOIN t_people_room b ON a.p_id=b.p_id
LEFT JOIN t_room c ON c.roomid=b.roomid

SELECT roomid,roomname,dbo.f_returnstring(roomid) AS peoples FROM t_room

CREATE FUNCTION f_returnstring(@roomid int)
RETURNS varchar(50)
AS
begin
   DECLARE @p_name VARCHAR(20)
   DECLARE @str VARCHAR(50)
   SET @str='';
	DECLARE  test_cursor Cursor FOR
	    SELECT b.p_name FROM T_PEOPLE_ROOM a INNER JOIN t_people b ON a.p_ID=b.p_ID
	     WHERE a.ROOMID=@ROOMID
	OPEN test_cursor
	FETCH NEXT FROM test_cursor INTO @p_name
	WHILE(@@FETCH_STATUS=0)
	BEGIN
	   IF(@str='')
	   begin
	      SET @str=@p_name;
	   END
	   ELSE begin
			SET @str=@str+','+@p_name;
	   END
	   FETCH NEXT FROM test_cursor INTO @p_name
	END
	CLOSE test_cursor;
	DEALLOCATE test_cursor;
	
	RETURN @str;
END

[偶麦嘎]

Create table #t_people
(
	p_id	nvarchar(10)
	,p_name	nvarchar(10)
)
insert into #t_people
select '70304','张三' union all
select '70716', '李四'union all
select '80184', '王五'union all
select '80983', '杨七'union all
select '10026', '钱八'union all
select '11005', '工一'union all
select '12056', '黄九'union all
select '14052', '叶儿'union all
select '13087', '李二'
 
 
--t_room 房间信息
create table #t_room
 (
	roomid	int
	,roomname	varchar(10)
 )
 insert into #t_room
select 1,'201'union all
select 2,'202'union all
select 3,'203'union all
select 4,'205'union all
select 5,'206'union all
select 6,'214'
--t_people_room 入住信息
 create table #t_people_room
 (
 id		int
 ,p_id	varchar(10)
 ,roomid	int
 )
 insert into #t_people_room 
select	1 , '70716' ,   1	union all
select	2 , '70304' ,   2	union all
select	3 , '80184' ,   2	union all
select	4 , '80983' ,   2	union all
select	5 , '10026' ,   6	union all
select	6 , '11005' ,	4	union all
select	7 , '12056' ,   4	union all
select	8 , '14052' ,   5	

			
select tr.roomid,tr.roomname
		,peoples=stuff((select ','+tp.p_name 
			from #t_people tp 
				 join #t_people_room tpm on tp.p_id=tpm.p_id
			where tpm.roomid=tr.roomid 
			for XML path('')),1,1,'')
from #t_room tr
	join #t_people_room t on tr.roomid = t.roomid
group by tr.roomid,tr.roomname

drop table #t_people
drop table #t_room
drop table #t_people_room

[jack15850798154]

SELECT roomid,roomname,LEFT(Peoples,LEN(Peoples)-1) AS peoples FROM (
SELECT aa.roomid,aa.roomname,
(
SELECT b.p_name+',' FROM T_PEOPLE_ROOM a INNER JOIN t_people b ON a.p_ID=b.p_ID
WHERE a.ROOMID=aa.roomid FOR XML PATH('')
) AS Peoples  FROM t_room aa
) t

[hackervip1988]

if OBJECT_ID('t_people', 'u') is not null
drop table t_people
create table t_people
(
p_id int not null,
p_name varchar(128) not null
)
Insert into t_people
Select 70304 ,  '张三'
Union  Select 70716 ,  '李四'
Union Select  80184 ,          '王五'
Union Select  80983 ,          '杨七'
Union Select  10026  , '钱八'
union Select  11005  ,'工一'
union Select  12056  ,'黄九'
union Select  14052  ,'叶儿'
union Select  13087  ,'李二'

if OBJECT_ID('t_room', 'u') is not null
drop table t_room
Create table t_room
(
roomid  int not null ,
roomname varchar(128)
)
insert into t_room
Select 1  , '201'
union Select  2     ,   202 
Union Select  3     ,   203
Union Select  4     ,   205
Union Select  5     ,   206
Union Select  6     ,   214

if OBJECT_ID('t_people_room', 'u') is not null
drop table t_people_room
create table t_people_room
(
 id int not null,
 p_id int not null,
 roomid int not null
)
Insert into t_people_room
Select  1  ,  70716  ,  1
union Select  2  ,  70304  ,  2
union Select  3  ,  80184 ,   2
union Select  4   , 80983  ,  2
union Select  5 ,   10026 ,   6
union Select  6  ,  11005 ,  4
union Select  7  ,  12056 ,   4
union Select  8  ,  14052  ,  5
;
with mm as
(
Select a.roomid, a.roomname , c.p_id, c.p_name
From t_room a  left Outer Join t_people_room b On a.roomid = b.roomid
left outer Join t_people  c on b.p_id = c.p_id
)
Select roomid, roomname, case when p_names <> '' then left(p_names,len(p_names) -1 ) else p_names end
From
(
Select roomid, roomname, 
(Select p_name + ',' From mm a where a.roomid = mm.roomid for XML path('')) p_names
 From mm  
group by roomid, roomname
) bb

[custom1234]

declare @t_people table (p_id int,p_name varchar(4))
insert into @t_people
select 70304,'张三' union all
select 70716,'李四' union all
select 80184,'王五' union all
select 80983,'杨七' union all
select 10026,'钱八' union all
select 11005,'工一' union all
select 12056,'黄九' union all
select 14052,'叶儿' union all
select 13087,'李二'
 
--> 测试数据: @t_room
declare @t_room table (roomid int,roomname int)
insert into @t_room
select 1,201 union all
select 2,202 union all
select 3,203 union all
select 4,205 union all
select 5,206 union all
select 6,214
 
--> 测试数据: @t_people_room
declare @t_people_room table (id int,p_id int,roomid int)
insert into @t_people_room
select 1,70716,1 union all
select 2,70304,2 union all
select 3,80184,2 union all
select 4,80983,2 union all
select 5,10026,6 union all
select 6,11005,4 union all
select 7,12056,4 union all
select 8,14052,5

;with t as(
select 
a.roomid,
a.roomname,
c.p_name 
from t_room a 
inner join t_people_room b on a.roomid=b.roomid 
inner join t_people c	   on b.p_id=c.p_id
)

select 
m.roomid,
m.roomname,
STUFF(m.p_name,1,1,'') as p_name 
from
(
select roomid,roomname,
(select ','+p_name  from t a where a.roomname=b.roomname for xml path('')) as p_name from t b
)m
group by 
m.roomid,
m.roomname,
m.p_name
order by 
m.roomid