34,593
社区成员
发帖
与我相关
我的任务
分享
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([主持人] varchar(4),[会议类型] varchar(8),[日期] datetime,[开始时间] datetime,[结束时间] sql_variant)
insert [tb]
select '王二','人事会议','2011-12-01',' 08:00','12:00' union all
select '王二','人事会议','2011-12-02',' 08:00','12:00' union all
select '王二','人事会议','2011-12-03',' 14:00','18:00' union all
select '王二','人事会议','2011-12-04 ','08:00','12:00' union all
select '王二','财务会议','2011-12-06',' 08:00','12:00' union all
select '王二','财务会议','2011-12-07',' 08:00','12:00' union all
select '王二','财务会议','2011-12-08',' 14:00','18:00' union all
select '王二','财务会议','2011-12-10',' 08:00','12:00' union all
select '王二','人事会议','2011-12-11',' 08:00','12:00' union all
select '王二','人事会议','2011-12-12',' 08:00','12:00' union all
select '王二','人事会议','2011-12-18',' 14:00','18:00' union all
select '王二','人事会议','2011-12-19',' 08:00','12:00'
select a.[主持人],a.[会议类型],[开始时间]=a.[日期]+1,
[结束时间]=(
select min([日期]) from tb aa
where [主持人]=a.[主持人] and [会议类型] =a.[会议类型] and [日期]>a.[日期]
and not exists(
select * from tb where [主持人]=aa.[主持人] and [会议类型] =aa.[会议类型]and [日期]=aa.[日期]-1))
-1
from(
select [主持人],[会议类型],[日期] from tb
union all --为每组编号补充查询起始编号是否缺号的辅助记录
select [主持人],[会议类型],min([日期]) from tb group by [主持人],[会议类型]
)a,(select [主持人],[会议类型],[日期]=max([日期]) from tb group by [主持人],[会议类型])b
where a.[主持人]=b.[主持人] and a.[会议类型]=b.[会议类型] and a.[日期]<b.[日期] --过滤掉每组数据中,编号最大的记录
and not exists(
select * from tb where [主持人]=a.[主持人] and [会议类型]=a.[会议类型] and [日期]=a.[日期]+1)
order by a.[主持人],[开始时间]
/*
主持人 会议类型 开始时间 结束时间
---- -------- ----------------------- -----------------------
王二 人事会议 2011-12-05 00:00:00.000 2011-12-10 00:00:00.000
王二 财务会议 2011-12-09 00:00:00.000 2011-12-09 00:00:00.000
王二 人事会议 2011-12-13 00:00:00.000 2011-12-17 00:00:00.000
(3 行受影响)
*/
----------------------------------------------------------------
-- Author :fredrickhu(小F,向高手学习)
-- Date :2011-12-09 09:45:17
-- Version:
-- Microsoft SQL Server 2008 R2 (RTM) - 10.50.1617.0 (Intel X86)
-- Apr 22 2011 11:57:00
-- Copyright (c) Microsoft Corporation
-- Enterprise Evaluation Edition on Windows NT 6.1 <X64> (Build 7600: ) (WOW64)
--
----------------------------------------------------------------
--> 测试数据:[tb]
if object_id('[tb]') is not null drop table [tb]
go
create table [tb]([主持人] varchar(4),[会议类型] varchar(8),[日期] datetime,[开始时间]varchar(10),[结束时间] varchar(10))
insert [tb]
select '王二','人事会议','2011-12-01' ,'08:00','12:00' union all
select '王二','人事会议','2011-12-02' ,'08:00','12:00' union all
select '王二','人事会议','2011-12-03' ,'14:00','18:00' union all
select '王二','人事会议','2011-12-04' ,'08:00','12:00' union all
select '王二','财务会议','2011-12-06' ,'08:00','12:00' union all
select '王二','财务会议','2011-12-07' ,'08:00','12:00' union all
select '王二','财务会议','2011-12-08' ,'14:00','18:00' union all
select '王二','财务会议','2011-12-10' ,'08:00','12:00' union all
select '王二','人事会议','2011-12-11' ,'08:00','12:00' union all
select '王二','人事会议','2011-12-12' ,'08:00','12:00' union all
select '王二','人事会议','2011-12-18' ,'14:00','18:00' union all
select '王二','人事会议','2011-12-19' ,'08:00','12:00'
--------------开始查询--------------------------
select
a.主持人,a.会议类型,
b.日期 as 开始日期,
case when a.日期=b.日期 then b.日期 else a.日期 end as 结束日期
from
( select px=ROW_NUMBER()over(order by GETDATE()),* from tb t where not exists(select 1 from tb where DATEDIFF(dd,t.日期,日期)=1 and 主持人=t.主持人 and 会议类型=t.会议类型))a
join
(select px=ROW_NUMBER()over(order by GETDATE()),* from tb t where not exists(select 1 from tb where DATEDIFF(dd,日期,t.日期)=1 and 主持人=t.主持人 and 会议类型=t.会议类型))b
on
a.px=b.px
----------------结果----------------------------
/* 主持人 会议类型 开始日期 结束日期
---- -------- ----------------------- -----------------------
王二 人事会议 2011-12-01 00:00:00.000 2011-12-04 00:00:00.000
王二 财务会议 2011-12-06 00:00:00.000 2011-12-08 00:00:00.000
王二 财务会议 2011-12-10 00:00:00.000 2011-12-10 00:00:00.000
王二 人事会议 2011-12-11 00:00:00.000 2011-12-12 00:00:00.000
王二 人事会议 2011-12-18 00:00:00.000 2011-12-19 00:00:00.000
(5 行受影响)
*/