求教一个复杂sql的写法

woshizhaoxuhui 2013-12-11 02:54:33

一个会议通知模块，涉及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来实现，谢谢！

...全文

206 9 打赏收藏转发到动态举报

写回复

用AI写文章

9 条回复

切换为时间正序

请发表友善的回复…

发表回复

jiajiaren 2013-12-11

打赏
举报

--> 测试数据：[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,'市场部'
--------------开始查询--------------------------

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 行受影响)

*/

KeepSayingNo 2013-12-11

打赏
举报

你看下我下面帮你层层剖析你就明白这个for xml的用法了，首先建立表



create table t_MeetingDep(meeting_id int,  dep_id int, dep_name varchar(30))

 

insert into t_MeetingDep

select 1,             1, '财务部' union all

select 1,             2,'信息部' union all

select 2,             2,'信息部' union all

select 2,             4,'市场部'

第二步



select a.meeting_id,(select ','+b.dep_name from t_MeetingDep b 

where a.meeting_id= b.meeting_id for xml path('')) from 

t_MeetingDep a

group by a.meeting_id

第二步结果

第三步SQL



select a.meeting_id,STUFF((select ','+b.dep_name from t_MeetingDep b 

where a.meeting_id= b.meeting_id for xml path('')),1,1,'') from 

t_MeetingDep a

group by a.meeting_id

第三步结果

發糞塗牆 2013-12-11

打赏
举报

引用 4 楼 woshizhaoxuhui 的回复:

[quote=引用 1 楼 u010192842 的回复:] FOR XML PATH('') 合并行！应该就这一个技术点吧....

对，就这里总是理解不了[/quote]这种合并是2005及以上版本才支持，如果是2000就要换别的方式了

阳泉酒家小当家 2013-12-11

打赏
举报

引用 4 楼 woshizhaoxuhui 的回复:

[quote=引用 1 楼 u010192842 的回复:] FOR XML PATH('') 合并行！应该就这一个技术点吧....

对，就这里总是理解不了[/quote] 其实就是一个 xml的合并操作，有点像个循环，依次把值加上去

阳泉酒家小当家 2013-12-11

打赏
举报

修改了一下：


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.000	2013-12-10 10:00:00.000
2	会议2	第二会议室	信息部,市场部	2013-12-10 14:00:00.000	2013-12-10 16:00:00.000
*/

woshizhaoxuhui 2013-12-11

打赏
举报

引用 1 楼 u010192842 的回复:

FOR XML PATH('') 合并行！应该就这一个技术点吧....

对，就这里总是理解不了

發糞塗牆 2013-12-11

打赏
举报

---------------------------------------------------------------- -- 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 第二会议室信息部,市场部 */

阳泉酒家小当家 2013-12-11

打赏
举报

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