使用SQL Server中的存储过程提取值

Question

我需要您的帮助才能在SQL Server(v12.0.6024.0(中创建视图。我的一位客户有一个表，其中一些时隙以以下格式保存：

ID_EVENT
1000		1000	24	09:00:00.0000
1000	24	09:30:00.0000

declare @t table(ID int, ID_EVENT int, TimeSlot time)
insert into @t
values
(1000,  24, '08:30:00.0000'),
(1000,  24, '09:00:00.0000'),
(1000,  24, '09:30:00.0000'),
--
(1000,  24, '11:30:00.0000'),
(1000,  24, '12:00:00.0000'),
(1000,  24, '12:30:00.0000'),
--
(1000,  24, '15:00:00.0000'),
(1000,  24, '15:30:00.0000'),
(1000,  24, '16:00:00.0000'),
--
(1000,  25, '15:30:00.0000'),
(1000,  25, '16:30:00.0000');

select Id, ID_EVENT, 
min(TimeSlot) as StartTimeSlot, 
dateadd(minute, 30, max(TimeSlot)) as EndTimeSlot
from 
(
select *,
datediff(minute, '00:00:00', Timeslot)/30 - row_number() over(partition by Id, ID_EVENT order by TimeSlot) as grpid
from @t
) as t
group by Id, ID_EVENT, grpid;

--first two groups per event&id row
select Id, ID_EVENT,
--1
min(case when grpordinal = 1 then TimeSlot end) as StartSlot1,
dateadd(minute, 30, max(case when grpordinal = 1 then TimeSlot end)) as EndSlot1,
--2
min(case when grpordinal = 2 then TimeSlot end) as StartSlot2,
dateadd(minute, 30, max(case when grpordinal = 2 then TimeSlot end)) as EndSlot2
from 
(
select Id, ID_EVENT, TimeSlot,
dense_rank() over(partition by Id, ID_EVENT order by grpid) as grpordinal
from 
(
select *,
datediff(minute, '00:00:00', Timeslot)/30 - row_number() over(partition by Id, ID_EVENT order by TimeSlot) as grpid
from @t
) as t
) as src
--where grpordinal <= 2 --not really needed
group by Id, ID_EVENT;

--!!!!only when there are max two groups/periods
--if there could be more than 2 periods this will not work
select Id, ID_EVENT,
--1
min(case when grpid = 0 then TimeSlot end) as StartSlot1,
dateadd(minute, 30, max(case when grpid = 0 then TimeSlot end)) as EndSlot1,
--2
min(case when grpid <> 0 then TimeSlot end) as StartSlot2,
dateadd(minute, 30, max(case when grpid <> 0 then TimeSlot end)) as EndSlot2
from
(
select *,
/*
1
+ datediff(minute, '00:00:00', Timeslot)/30 - row_number() over(partition by Id, ID_EVENT order by TimeSlot) 
- datediff(minute, '00:00:00', min(Timeslot) over(partition by Id, ID_EVENT)) /30
*/
1
+ datediff(minute, min(Timeslot) over(partition by Id, ID_EVENT), TimeSlot)/30  
- row_number() over(partition by Id, ID_EVENT order by TimeSlot)
as grpid --1st groupid is always 0
from @t
) as t
group by Id, ID_EVENT;

这读起来像是一个缺口和孤岛问题，在这里您需要识别并分组"相邻的"；时隙。

我建议将范围放在行中，而不是列中。为此，您可以使用这样的窗口函数：

select id, id_event, 
min(timeslot) as timeslot_start, max(timeslot) as timeslot_end
from (
select t.*, 
row_number() over(partition by id, id_event order by timeslot) rn
from mytable t
) t
group by id, id_event, datediff(minute, - rn * 30, timeslot)

如果只想查看每个事件的前两个范围(都在结果集中的同一行(，那么我们可以在该查询的顶部使用条件聚合：

select id, id_event,
max(case when rn = 1 then timeslot_start end) as timeslot_start_1,
max(case when rn = 1 then timeslot_end   end) as timeslot_end_1,
max(case when rn = 2 then timeslot_start end) as timeslot_start_2,
max(case when rn = 2 then timeslot_end   end) as timeslot_end_2
from (
select id, id_event, 
min(timeslot) as timeslot_start, max(timeslot) as timeslot_end,
row_number() over(partition by id, id_event order by min(timeslot)) rn
from (
select t.*, 
row_number() over(partition by id, id_event order by timeslot) rn
from mytable t
) t
group by id, id_event, datediff(minute, - rn * 30, timeslot)
) t
where rn <= 2
group by id, id_event