求教一个复杂sql的写法解决办法

求教一个复杂sql的写法
一个会议通知模块，涉及4个表
表1,会议表：t_Meeting
meeting_id subject begin_time end_time room_id
1 会议1 2013-12-10 10:00 2013-12-10 10:00 1
2 会议2 2013-12-10 14:00 2013-16-10 10:00 2

表2,会议室表：t_Room
room_id room_name
1 第一会议室
2 第二会议室

表3，参会部门表:t_MeetingDep
meeting_id dep_id
1 1
1 2
2 2
2 4

表4,部门表：
dep_id dep_name
1 财务部
2 信息部
3 综合部
4 市场部

希望得到如下结果
会议编号会议名称会议室参会部门开始时间结束时间
1 会议1 第一会议室财务部，信息部 2013-12-10 10:00 2013-12-10 10:00
2 会议2 第二会议室信息部，市场部 2013-12-10 14:00 2013-16-10 10:00

请问该如何写sql来实现，谢谢！
[解决办法]
FOR XML PATH('')
合并行！
应该就这一个技术点吧....
[解决办法]

select distinct
       m.meeting_id 会议编号,
       m.subject 会议名称,
       r.room_name 会议室,
       stuff(
              (select ','+md.dep_name
               from t_MeetingDep md 
               inner join 部门表 d
                       on d.dep_id = md.dep_id
               where r.meeting_id = md.meeting_id
               for xml path('')
              ),1,1,''
            ) as 参会部门,
            
       m.begin_time 开始时间,
       m.end_time 结束时间    
            
from t_Meeting m
inner join t_Room r
        on m.room_id = r.room_id

[解决办法]
----------------------------------------------------------------
-- Author :DBA_Huangzj()
-- Date :2013-12-11 14:57:21
-- 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: )
--
----------------------------------------------------------------
--> 测试数据：[t_Meeting]

if object_id('[t_Meeting]') is not null drop table [t_Meeting]
go
create table [t_Meeting]([meeting_id] int,[subject] varchar(5),[begin_time] datetime,[end_time] datetime,[room_id] int)
insert [t_Meeting]
select 1,'会议1','2013-12-10 10:00','2013-12-10 10:00',1 union all
select 2,'会议2','2013-12-10 14:00','2013-12-10 16:00',2
--> 测试数据：[t_Room]
if object_id('[t_Room]') is not null drop table [t_Room]
go
create table [t_Room]([room_id] int,[room_name] varchar(10))
insert [t_Room]
select 1,'第一会议室' union all
select 2,'第二会议室'
--> 测试数据：[t_MeetingDep]
if object_id('[t_MeetingDep]') is not null drop table [t_MeetingDep]
go
create table [t_MeetingDep]([meeting_id] int,[dep_id] int)
insert [t_MeetingDep]
select 1,1 union all
select 1,2 union all
select 2,2 union all
select 2,4
--> 测试数据：[t_dep]
if object_id('[t_dep]') is not null drop table [t_dep]
go
create table [t_dep]([dep_id] int,[dep_name] varchar(6))
insert [t_dep]
select 1,'财务部' union all
select 2,'信息部' union all
select 3,'综合部' union all
select 4,'市场部'
--------------开始查询--------------------------

;WITH ym AS (
select m.meeting_id,m.[SUBJECT],m.begin_time,m.end_time,r.room_name,d.dep_name
from [t_Meeting] m INNER JOIN [t_Room] r ON m.room_id=r.room_id
LEFT JOIN [t_MeetingDep] mp ON m.meeting_id=mp.meeting_id
INNER JOIN [t_dep] d ON mp.dep_id=d.dep_id)
select a.meeting_id,a.[SUBJECT],a.begin_time,a.end_time,a.room_name,
stuff((select ','+dep_name from ym b
where b.meeting_id=a.meeting_id and b.[SUBJECT]=a.[SUBJECT]
and b.begin_time=a.begin_time
and b.end_time=a.end_time
and b.room_name=a.room_name
for xml path('')),1,1,'') 'dep_name'
from ym a
group by a.meeting_id,a.[SUBJECT],a.begin_time,a.end_time,a.room_name
----------------结果----------------------------
/*
meeting_id SUBJECT begin_time end_time room_name dep_name
----------- ------- ----------------------- ----------------------- ---------- ----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 会议1 2013-12-10 10:00:00.000 2013-12-10 10:00:00.000 第一会议室财务部,信息部
2 会议2 2013-12-10 14:00:00.000 2013-12-10 16:00:00.000 第二会议室信息部,市场部
*/

[解决办法]
修改了一下：

create table t_Meeting(
meeting_id int,
subject varchar(20),
begin_time datetime,
end_time datetime,
room_id int
)

insert into t_Meeting
select 1 ,'会议1', '2013-12-10 10:00', '2013-12-10 10:00',1 union all
select 2 ,'会议2', '2013-12-10 14:00', '2013-12-10 16:00',2

create table t_Room(room_id int, room_name varchar(20))

insert into t_Room
select 1 , '第一会议室' union all
select 2 , '第二会议室'

create table t_MeetingDep(meeting_id int, dep_id int)

insert into t_MeetingDep
select 1, 1 union all
select 1, 2 union all
select 2, 2 union all
select 2, 4

create table 部门表(dep_id int, dep_name varchar(30))

insert into 部门表
select 1 , '财务部' union all
select 2 , '信息部' union all
select 3 , '综合部' union all
select 4 , '市场部'

go

select distinct
m.meeting_id 会议编号,
m.subject 会议名称,
r.room_name 会议室,
stuff(
(select ','+d.dep_name
from t_MeetingDep md
inner join 部门表 d
on d.dep_id = md.dep_id
where m.meeting_id = md.meeting_id
for xml path('')
),1,1,''
) as 参会部门,

m.begin_time 开始时间,
m.end_time 结束时间

from t_Meeting m
inner join t_Room r
on m.room_id = r.room_id
/*
会议编号会议名称会议室参会部门开始时间结束时间
1会议1第一会议室财务部,信息部2013-12-10 10:00:00.0002013-12-10 10:00:00.000
2会议2第二会议室信息部,市场部2013-12-10 14:00:00.0002013-12-10 16:00:00.000
*/

[解决办法]

第三步SQL


[解决办法]
--&gt; 测试数据：[t_Meeting]
if object_id('[t_Meeting]') is not null drop table [t_Meeting]
go 
create table [t_Meeting]([meeting_id] int,[subject] varchar(5),[begin_time] datetime,[end_time] datetime,[room_id] int)
insert [t_Meeting]
select 1,'会议1','2013-12-10 10:00','2013-12-10 10:00',1 union all
select 2,'会议2','2013-12-10 14:00','2013-12-10 16:00',2
--&gt; 测试数据：[t_Room]
if object_id('[t_Room]') is not null drop table [t_Room]
go 
create table [t_Room]([room_id] int,[room_name] varchar(10))
insert [t_Room]
select 1,'第一会议室' union all
select 2,'第二会议室'
--&gt; 测试数据：[t_MeetingDep]
if object_id('[t_MeetingDep]') is not null drop table [t_MeetingDep]
go 
create table [t_MeetingDep]([meeting_id] int,[dep_id] int)
insert [t_MeetingDep]
select 1,1 union all
select 1,2 union all
select 2,2 union all
select 2,4
--&gt; 测试数据：[t_dep]
if object_id('[t_dep]') is not null drop table [t_dep]
go 
create table [t_dep]([dep_id] int,[dep_name] varchar(6))
insert [t_dep]
select 1,'财务部' union all
select 2,'信息部' union all 
 
select 3,'综合部' union all
select 4,'市场部'
--------------开始查询--------------------------

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[F_ReturnString]') and xtype in (N'FN', N'IF', N'TF'))
DROP function [dbo].[F_ReturnString] 
GO
CREATE FUNCTION  F_ReturnString(@id int)
RETURNS  NVARCHAR(MAX)
AS
BEGIN
DECLARE @s  NVARCHAR(MAX)
SET @s=''
SELECT @s=@s+','+b.dep_Name FROM   t_MeetingDep  a INNER JOIN   t_dep b
ON a.dep_id=b.dep_id WHERE a.meeting_id=@id
RETURN  RIGHT(@s,LEN(@s)-1)
END

GO
 
SELECT c.meeting_id AS  会议编号,c.SUBJECT AS 会议名称,a.room_name AS 会议室,b.dep_id AS 参会部门,c.begin_time AS  开始时间,c.end_time AS  结束时间 FROM t_Room a INNER JOIN 

t_Meeting c  ON a.room_id=c.room_id INNER JOIN
(
 SELECT  meeting_id,dbo.F_ReturnString(meeting_id) dep_id     FROM t_MeetingDep  a   GROUP BY meeting_id
)b
 ON c.meeting_id=b.meeting_id  
 
/*
会议编号        会议名称  会议室        参会部门           开始时间                    结束时间
----------- ----- ---------- ----------------------------  ----------------------- -----------------------
1           会议1   第一会议室      财务部,信息部        2013-12-10 10:00:00.000 2013-12-10 10:00:00.000
2           会议2   第二会议室      信息部,市场部        2013-12-10 14:00:00.000 2013-12-10 16:00:00.000

(2 行受影响)

*/

求教一个 复杂sql的写法解决办法

热点推荐

求教一个复杂sql的写法解决办法