34,590
社区成员
发帖
与我相关
我的任务
分享
create table #tb(NO int,startTime datetime,endTime datetime)
insert into #tb select 1,'2011-10-13 08:10:00','2011-10-13 08:15:00'
insert into #tb select 1,'2011-10-13 08:12:00','2011-10-13 08:19:00'
insert into #tb select 1,'2011-10-13 08:14:00','2011-10-13 08:20:00'
insert into #tb select 1,'2011-10-13 08:13:00','2011-10-13 08:14:00'
insert into #tb select 2,'2011-10-13 08:17:00','2011-10-13 08:21:00'
insert into #tb select 2,'2011-10-13 08:18:00','2011-10-13 08:20:00'
insert into #tb select 2,'2011-10-13 08:19:00','2011-10-13 08:22:00'
go
;with k as
(
--对每个组和时间进行排序
Select No,startTime, endtime, ROW_NUMBER() over(partition by No order by No, starttime) as FreshID
from #tb
)
,
k_end as
(
select No, MAX(endTime) as endTime
from k
group by NO
)
select k.NO, k.startTime, k_end.endTime
from k
JOIN k_end
ON K.NO=k_end.NO
where k.FreshID=1
drop table #tb
go
if object_id('tb','U') is not null
drop table tb
go
create table tb
(
NO int,
startTime datetime,
endTime datetime
)
go
insert into tb
select 1,'2011-10-13 08:10:00','2011-10-13 08:15:00' union all
select 1,'2011-10-13 08:12:00','2011-10-13 08:19:00' union all
select 1,'2011-10-13 08:14:00','2011-10-13 08:20:00' union all
select 1,'2011-10-13 08:13:00','2011-10-13 08:14:00' union all
select 2,'2011-10-13 08:17:00','2011-10-13 08:21:00' union all
select 2,'2011-10-13 08:18:00','2011-10-13 08:20:00' union all
select 2,'2011-10-13 08:19:00','2011-10-13 08:22:00'
go
select *,(select max(endTime) from tb where NO=a.NO) from tb a where not exists(select 1 from tb where NO=a.NO and startTime<a.startTime)
create table #tb(NO int,startTime datetime,endTime datetime)
insert into #tb select 1,'2011-10-13 08:10:00','2011-10-13 08:15:00'
insert into #tb select 1,'2011-10-13 08:12:00','2011-10-13 08:19:00'
insert into #tb select 1,'2011-10-13 08:14:00','2011-10-13 08:20:00'
insert into #tb select 1,'2011-10-13 08:13:00','2011-10-13 08:14:00'
insert into #tb select 2,'2011-10-13 08:17:00','2011-10-13 08:21:00'
insert into #tb select 2,'2011-10-13 08:18:00','2011-10-13 08:20:00'
insert into #tb select 2,'2011-10-13 08:19:00','2011-10-13 08:22:00'
--//
我需要查询出所有会议室下的开始创建时间(最早开始),主持人退出时间,会议室关闭时间(最晚结束)
结果应该是这样:
1 2011-10-13 08:10:00 2011-10-13 08:15:00 2011-10-13 08:20:00
2 2011-10-13 08:17:00 2011-10-13 08:21:00 2011-10-13 08:22:00
--//先对同一no进行排序,第一个也就是row=1的endtime就是主持人的退出时间,对大的结束时间就是会场的关闭时间
;with b as (
select *,row=row_number() over(partition by no order by starttime) from #tb
)
select b.no,min(b.starttime)as '开始创建时间(最早开始)',
max(case when row=1 then b.endtime end) as '主持人退出时间'
,max(b.endtime)as '会议室关闭时间(最晚结束'
from b
group by b.no
--//结果
/*
no 开始创建时间(最早开始) 主持人退出时间 会议室关闭时间(最晚结束
----------- ----------------------- ----------------------- -----------------------
1 2011-10-13 08:10:00.000 2011-10-13 08:15:00.000 2011-10-13 08:20:00.000
2 2011-10-13 08:17:00.000 2011-10-13 08:21:00.000 2011-10-13 08:22:00.000
警告: 聚合或其他 SET 操作消除了空值。
(2 行受影响)
*/
create table tb(NO int,startTime datetime,endTime datetime)
insert into tb select 1,'2011-10-13 08:10:00','2011-10-13 08:15:00'
insert into tb select 1,'2011-10-13 08:12:00','2011-10-13 08:19:00'
insert into tb select 1,'2011-10-13 08:14:00','2011-10-13 08:20:00'
insert into tb select 1,'2011-10-13 08:13:00','2011-10-13 08:14:00'
insert into tb select 2,'2011-10-13 08:17:00','2011-10-13 08:21:00'
insert into tb select 2,'2011-10-13 08:18:00','2011-10-13 08:20:00'
insert into tb select 2,'2011-10-13 08:19:00','2011-10-13 08:22:00'
go
select no,
Min(startTime) as 最早时间,
(select endTime from tb where starttime=(select min(starttime) from tb where no=a.no))as 主持人退出时间,
MAX(endTime) as 最晚时间
from tb a
group by no
/*
no 最早时间 主持人退出时间 最晚时间
----------- ----------------------- ----------------------- -----------------------
1 2011-10-13 08:10:00.000 2011-10-13 08:15:00.000 2011-10-13 08:20:00.000
2 2011-10-13 08:17:00.000 2011-10-13 08:21:00.000 2011-10-13 08:22:00.000
(2 行受影响)
*/
go
drop table tb
create table tb(NO int,startTime datetime,endTime datetime)
insert into tb select 1,'2011-10-13 08:10:00','2011-10-13 08:15:00'
insert into tb select 1,'2011-10-13 08:12:00','2011-10-13 08:19:00'
insert into tb select 1,'2011-10-13 08:14:00','2011-10-13 08:20:00'
insert into tb select 1,'2011-10-13 08:13:00','2011-10-13 08:14:00'
insert into tb select 2,'2011-10-13 08:17:00','2011-10-13 08:21:00'
insert into tb select 2,'2011-10-13 08:18:00','2011-10-13 08:20:00'
insert into tb select 2,'2011-10-13 08:19:00','2011-10-13 08:22:00'
go
select no,
Min(startTime) as 最早时间,
Min(case when rn=1 then endTime end) as 主持人退出时间,
MAX(endTime) as 最晚时间
from (select row_number()over(partition by no order by starttime)rn,* from tb)t
group by no
/*
no 最早时间 主持人退出时间 最晚时间
----------- ----------------------- ----------------------- -----------------------
1 2011-10-13 08:10:00.000 2011-10-13 08:15:00.000 2011-10-13 08:20:00.000
2 2011-10-13 08:17:00.000 2011-10-13 08:21:00.000 2011-10-13 08:22:00.000
警告: 聚合或其他 SET 操作消除了 Null 值。
(2 行受影响)
*/
go
drop table tb
create table #tb(NO int,startTime datetime,endTime datetime)
insert into #tb select 1,'2011-10-13 08:10:00','2011-10-13 08:15:00'
insert into #tb select 1,'2011-10-13 08:12:00','2011-10-13 08:19:00'
insert into #tb select 1,'2011-10-13 08:14:00','2011-10-13 08:20:00'
insert into #tb select 1,'2011-10-13 08:13:00','2011-10-13 08:14:00'
insert into #tb select 2,'2011-10-13 08:17:00','2011-10-13 08:21:00'
insert into #tb select 2,'2011-10-13 08:18:00','2011-10-13 08:20:00'
insert into #tb select 2,'2011-10-13 08:19:00','2011-10-13 08:22:00'
go
select no,
Min(startTime) as 最早时间,
(select endTime from #tb where startTime = (select min(startTime) from #tb where no = t.no) and no = t.no) as 主持人退出时间,
MAX(endTime) as 最晚时间
from #tb t
group by no
create table #tb(NO int,startTime datetime,endTime datetime)
insert into #tb select 1,'2011-10-13 08:10:00','2011-10-13 08:15:00'
insert into #tb select 1,'2011-10-13 08:12:00','2011-10-13 08:19:00'
insert into #tb select 1,'2011-10-13 08:14:00','2011-10-13 08:20:00'
insert into #tb select 1,'2011-10-13 08:13:00','2011-10-13 08:14:00'
insert into #tb select 2,'2011-10-13 08:17:00','2011-10-13 08:21:00'
insert into #tb select 2,'2011-10-13 08:18:00','2011-10-13 08:20:00'
insert into #tb select 2,'2011-10-13 08:19:00','2011-10-13 08:22:00'
;with t as
(
select row_number() over(partition by no order by startTime ) as type,
* from #tb
)
select
no,
(select min(startTime) from t where no = a.no and type = 1) as '开始创建时间',
(select max(endTime) from t where no = a.no and type = 1) as '主持人退出时间',
(select max(endTime) from t where no = a.no and type != 1) as '会议室关闭时间'
from t a
group by no
drop table #tb
----------------------------------------
no 开始创建时间 主持人退出时间 会议室关闭时间
----------- ----------------------- ----------------------- -----------------------
1 2011-10-13 08:10:00.000 2011-10-13 08:15:00.000 2011-10-13 08:20:00.000
2 2011-10-13 08:17:00.000 2011-10-13 08:21:00.000 2011-10-13 08:22:00.000
(2 行受影响)
select b.no,
Min(b.startTime) as 最早时间,
(select a.endTime from tb a group by a.no having a.no=b.no and a.startTime= 最早时间) as 主持人退出时间,
MAX(b.endTime) as 最晚时间
from tb b
group by b.no
create table tb(NO int,startTime datetime,endTime datetime,人员 nvarchar(5))
insert into tb select 1,'2011-10-13 08:10:00','2011-10-13 08:15:00','主持人'
insert into tb select 1,'2011-10-13 08:12:00','2011-10-13 08:19:00','参与者'
insert into tb select 1,'2011-10-13 08:14:00','2011-10-13 08:20:00','参与者'
insert into tb select 1,'2011-10-13 08:13:00','2011-10-13 08:14:00','参与者'
insert into tb select 2,'2011-10-13 08:17:00','2011-10-13 08:21:00','主持人'
insert into tb select 2,'2011-10-13 08:18:00','2011-10-13 08:20:00','参与者'
insert into tb select 2,'2011-10-13 08:19:00','2011-10-13 08:22:00','参与者'
go
select no,
Min(startTime) as 最早时间,
Max(case when 人员='主持人' then endTime end) as 主持人退出时间,
MAX(endTime) as 最晚时间
from tb
group by no
/*
no 最早时间 主持人退出时间 最晚时间
----------- ----------------------- ----------------------- -----------------------
1 2011-10-13 08:10:00.000 2011-10-13 08:15:00.000 2011-10-13 08:20:00.000
2 2011-10-13 08:17:00.000 2011-10-13 08:21:00.000 2011-10-13 08:22:00.000
警告: 聚合或其他 SET 操作消除了 Null 值。
(2 行受影响)
*/
go
drop table tb