求一条SQL汇总语句
--有如下三张表（t_people 人员表、t_room 房间表 、t_people_room 入住信息 ）
--t_people 人员信息
p_idp_name
70304 张三
70716 李四
80184 王五
80983 杨七
10026 钱八
11005工一
12056黄九
14052叶儿
13087李二
--t_room 房间信息
roomidroomname
1201
2202
3203
4205
5206
6214
--t_people_room 入住信息
id p_idroomid
1707161
2703042
3801842
4809832
5100266
611005 4
7120564
8140525
--现在统计要每个房间的入住人员信息,希望得如下统计结果
roomidroomnamepeoples
1201李四
2202张三、王五、杨七
3203
4205工一、黄九
5206叶儿
6214钱八
--希望用一条语句得到每个房间入住过人员信息
--数据库为 Ms sqL 2005
--> 测试数据: @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 钱八
*/
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_idnvarchar(10)
,p_namenvarchar(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
(
roomidint
,roomnamevarchar(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
(
idint
,p_idvarchar(10)
,roomidint
)
insert into #t_people_room
select1 , '70716' , 1union all
select2 , '70304' , 2union all
select3 , '80184' , 2union all
select4 , '80983' , 2union all
select5 , '10026' , 6union all
select6 , '11005' ,4union all
select7 , '12056' , 4union all
select8 , '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
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
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
