求一条SQL汇总语句

Question

--有如下三张表（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 sql 统计

Answer 1

--> 测试数据: @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         钱八
*/

Answer 2

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.roomidSELECT roomid,roomname,dbo.f_returnstring(roomid) AS peoples FROM t_roomCREATE 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

Answer 3

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.roomnamedrop table #t_people
drop table #t_room
drop table #t_people_room

Answer 4

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

Answer 5

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

Answer 6

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     ,   214if 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