求助：一个sql的编写

[風中凌乱]

Id IsPresent CreationTime ResourceId
1 1 2016-09-04 04:00:41.923 1
3 0 2016-09-04 04:32:41.923 1
4 1 2016-09-04 05:03:41.923 1
5 0 2016-09-04 05:27:41.923 1
7 1 2016-09-04 05:35:41.923 1
8 1 2016-09-04 05:45:41.923 1
9 0 2016-09-04 06:10:41.923 1
10 1 2016-09-04 06:44:41.923 1
11 0 2016-09-04 06:55:41.923 1
12 0 2016-09-04 07:10:41.923 1


表结构如上，ResourceId 为房间号，IsPresent 1为有人，0为没人，现在需要统计每个房间以最1开始，以0为结束的时间，就是有连续的1或者连续的0的时候1取第一个1创建时间为开始时间，0取最后一个0的创建时间作为结束时间。

想一上午，没有头绪。。。

[shoppo0505]

没太明白描述。 这组测试数据最后应该得出什么结果？

[風中凌乱]

引用 1 楼 shoppo0505 的回复:

没太明白描述。 这组测试数据最后应该得出什么结果？

ResourceId startTime endTime 1 2016-09-04 04:00:41.923 2016-09-04 04:32:41.923 1 2016-09-04 05:03:41.923 2016-09-04 05:27:41.923 1 2016-09-04 05:35:41.923 2016-09-04 06:10:41.923 1 2016-09-04 06:44:41.923 2016-09-04 07:10:41.923 类似于这种格式。

[满庭花醉三千客]

直接查询不出来，用循环？ 分组ResourceId 排序Id 循环 定义 赋值 @前一个IsPresent @当前IsPresent 进行判断 插入临时表

[shoppo0505]

你这个太废脑子了。 感觉数据本身有问题。连续的1，0数据怎么会入录呢？ 写了一个，但是不符合你的要求，凑合看吧 with tb (Id, IsPresent, CreationTime, ResourceId) as ( select 1, 1, ' 2016-09-04 04:00:41.923', 1 union all select 3, 0, ' 2016-09-04 04:32:41.923', 1 union all select 4, 1 , '2016-09-04 05:03:41.923', 1 union all select 5, 0 , '2016-09-04 05:27:41.923', 1 union all select 7, 1 , '2016-09-04 05:35:41.923', 1 union all select 8, 1 , '2016-09-04 05:45:41.923', 1 union all select 9, 0 , '2016-09-04 06:10:41.923', 1 union all select 10, 1 , '2016-09-04 06:44:41.923', 1 union all select 11, 0 , '2016-09-04 06:55:41.923', 1 union all select 12, 0, '2016-09-04 07:10:41.923', 1 ) , temp_tb as ( select * , _index = ROW_NUMBER()over( order by CreationTime) from tb --where IsPresent = 1 ) select temp_tb.ResourceId , temp_tb.CreationTime as starttime , (select MIN(CreationTime) from temp_tb t where t._index > temp_tb._index and t.IsPresent = 0)as endtime from temp_tb where IsPresent = 1

[fleetingevent]

我是这样想的： select top 1 * from 表名 //选择第一条数据 where ResourceId=1 and IsPresent=1 //选择一号房间有人的情况下 order by Time asc //正序排序，这样就是当天最早的时候的有人的情况了 同样 select top 1 * from 表名 //选择第一条数据 where ResourceId=1 and IsPresent=0 //选择一号房间没有人的情况下 order by Time desc //倒排序，这样就是当天最晚的时候的没有人的情况了

[ijunxiong]

1，选择一个房间号，为临时表a 2，找 是IsPresent=1的 下一个IsPresent=0的最小的CreationTime，为临时表a的新字段N1CreationTime 这个sql不难 即让IsPresent 从1 到 0可以进行分组，可以统计出最大，和最小，CreationTime，相减就得到结果了

[ijunxiong]

declare @date1 datetime set @date1=getdate()-- select *,@date1 'N1CreationTime' into tabA from 原始表 where ResourceId=1 --过滤房间号 update tabA set N1CreationTime=CreationTime where IsPresent=0 update t1 set N1CreationTime=(select min(CreationTime) from tabA where IsPresent=0 and CreationTime>t1.CreationTime) from tabA t1 where IsPresent=1 select max(CreationTime)-min(CreationTime) from tabA group by N1CreationTime 应该就可以了

[Nick_Ngai]

select ResourceId,case when IsPresent = 1 then min(CreationTime) end as StartTime, case when IsPresent = 0 then max(CreationTime) end as EndTime from room
group by ResourceId,IsPresent
order by ResourceId,IsPresent desc
结果如下：

[Nick_Ngai]

select t.ResourceId, max(StartTime) StartTime, max(EndTime) EndTime from
(
select ResourceId,case when IsPresent = 1 then min(CreationTime) end as StartTime, case when IsPresent = 0 then max(CreationTime) end as EndTime
from room
group by ResourceId,IsPresent
) t
group by t.ResourceId

这个应该是你想要的效果吧

[changuncle]

楼主的问题挺有意思，先记录一下。

[卧_槽]

用游标即可。

[changuncle]

楼主问题解决了：http://blog.csdn.net/xiaouncle/article/details/54668182 请给分吧...............

[風中凌乱]

今天上来居然看到有好多人又回复了，当时这个问题最后放到程序里面后台解决了，sql当时真搞不出来。。。

[風中凌乱]

另外 问题我描述也有点不够详细及错误。。。当时手抖了吧，问题补充修正:统计每个房间的利用率，就是统计每个房间有人的时间，从1开始有人，0没有人(在0的数据之后五分钟之内再没有1的数据才说明是真没人了)，前面发问题的时候写错了，是以1开始，第一个0(这之后五分钟内没有1的数据)的时间为有人的时间，好了 就这样结贴吧！