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

本文介绍了在SQL Server中使用存储过程提取值的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我需要您的帮助才能在SQL Server(v12.0.6024.0)中创建视图.我的一位客户有一张表格，其中某些时隙以这种格式保存:

I need your help to create a view in SQL Server (v12.0.6024.0). One of my customer has a table in which some time slot are saved in this format:

ID ID_EVENT 时间段

1000	24	08:30:00.0000
1000	24	09:00:00.0000
1000	24	09:30:00.0000

每个时隙持续30分钟，上面的示例表示ID为24(保存在另一个表中)的事件的持续时间为8:30至10:00(第三个时隙的开始时间为9:30，持续了30分钟，因此其结束时间为10:00).问题是，在某些情况下，时间值不是连续的，中间可能会有暂停，所以我会遇到类似这样的情况:

Every time slot lasts 30 minutes, the example above means that event with ID 24 (saved in another table) lasted form 8:30 to 10:00 (3rd slot started at 9:30, lasted 30 minutes so it finished at 10:00). The problem is that in some cases the time values are not consecutive and there may be a pause in the middle, so I would have something like this:

ID ID_EVENT 时间段

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

在这种情况下，ID为24的事件从8:30持续到10，停止，然后从11:30再次开始到13:00.我被要求为外部开发人员准备一个视图，在该视图中，我不仅要报告事件开始的时间(在我的示例中为8:30)，还应报告事件的永久停止时间(在我的示例中为13:00)，还有暂停开始的时间(在我的示例中为10:00)和暂停的时间(在我的示例中为11:30).

In this case event with ID 24 lasted from 8:30 to 10, stopped, then started again from 11:30 to 13:00. I have been asked to prepare a view for an external developer in which I have to report not only the time the event started (in my example, 8:30) and the time it stopped for good (in my example 13:00) but also the time the pause started (in my example 10:00) and the time the pause finished (in my example 11:30).

我对前两个值没有问题，但是我不知道如何提取其他两个值.我认为我们可以考虑在两个时隙不是连续的情况下发生暂停，同一事件的周期不能超过一个.我想我需要一个程序，但是发现很难编写它.我必须要说一个观点

I have no problem with the first 2 values but I don't know how to extract the other two. I think we can consider a pause happening when 2 time slots are not consecutive, there cannot be more than periods for the same event. I suppose I need a procedure but find it difficult to write it; I need to have a view that says

ID ID_EVENT Time1 Time2 Time3 Time4

1000	24	08:30:00.0000	10:00:00.0000	11:30:00.0000	13:00: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;