ID | State | Date 1 1 2010-11-01 08:08:08
2 1 2010-11-01 08:09:08
3 1 2010-11-01 08:10:08
4 1 2010-11-01 08:11:08
5 1 2010-11-01 08:12:08
6 1 2010-11-01 08:13:08
7 1 2010-11-01 08:14:08
8 0 2010-11-01 08:15:08
9 0 2010-11-01 08:16:08
10 0 2010-11-01 08:17:08
11 0 2010-11-01 08:18:08
12 0 2010-11-01 08:19:08
13 1 2010-11-01 08:20:08
14 1 2010-11-01 08:21:08
15 1 2010-11-01 08:22:08
16 1 2010-11-01 08:23:08我要查出(注意：时间是从小到大,但是间隔不是固定的) State StartDate EndDate
1 2010-11-01 08:08:08 2010-11-01 08:14:08
0 2010-11-01 08:15:08 2010-11-01 08:19:08
1 2010-11-01 08:20:08 2010-11-01 08:23:08
(select a.*,Case state when (select top 1 state from tb where Date<a.Date order by Date desc) then 0 else 1 end as T from
tb a),
cte2 as
(select id,state,Date,(select SUM(T) from cte where Date<=a.Date) as Group_No from cte a)select Group_No,State,
(select MIN(Date) from cte2 where Group_No=a.Group_No) as StartDate,
(select Max(Date) from cte2 where Group_No=a.Group_No) as EndDate
from cte2 a group by Group_No,StateGroup_No State StartDate EndDate
----------- ----------- ----------------------- -----------------------
1 1 2010-11-01 08:08:08.000 2010-11-01 08:14:08.000
2 0 2010-11-01 08:15:08.000 2010-11-01 08:19:08.000
3 1 2010-11-01 08:20:08.000 2010-11-01 08:23:08.000(3 行受影响)
create table #tbA
(ID int, State int, Date datetime)
insert #tbA
select 1 ,1 ,'2010-11-01 08:08:08' union all
select 2 ,1 ,'2010-11-01 08:09:08' union all
select 3 ,1 ,'2010-11-01 08:10:08' union all
select 4 ,1 ,'2010-11-01 08:11:08' union all
select 5 ,1 ,'2010-11-01 08:12:08' union all
select 6 ,1 ,'2010-11-01 08:13:08' union all
select 7 ,1 ,'2010-11-01 08:14:08' union all
select 8 ,0 ,'2010-11-01 08:15:08' union all
select 9 ,0 ,'2010-11-01 08:16:08' union all
select 10 ,0 ,'2010-11-01 08:17:08' union all
select 11 ,0 ,'2010-11-01 08:18:08' union all
select 12 ,0 ,'2010-11-01 08:19:08' union all
select 13 ,1 ,'2010-11-01 08:20:08' union all
select 14 ,1 ,'2010-11-01 08:21:08' union all
select 15 ,1 ,'2010-11-01 08:22:08' union all
select 16 ,1 ,'2010-11-01 08:23:08'select 1 as gid,identity(int,1,1) as row ,* into #TT from #tbA order by Date
declare @i as int
declare @State as int
set @State=-1
set @i=1
update #TT set gid=(case when (@State<>-1 and @State<>State) then @i+1 else @i end),
@i=(case when (@State<>-1 and @State<>State) then @i+1 else @i end),@State=Stateselect State,Min(Date) as StartDate,Max(Date) as EndDate from #TT group by gid,State
虽然这么回贴可能会被删除。
谁他妈的删除了就是个王八蛋。
强烈抗议CSDN这种即当婊子又想立贞洁牌坊的恶劣行为！！！
有本事，就封杀我！！！理由在这里:
他妈的一帮管理员有神经病啊???
http://topic.csdn.net/u/20110422/10/1a381057-7a7b-41d9-8969-ad1f5b77f24a.html?38665
最好给出完整的表结构,测试数据,计算方法和正确结果.否则耽搁的是你宝贵的时间。
如果有多表,表之间如何关联?
如何更有效地在SQL Server论坛上提问
http://topic.csdn.net/u/20100716/19/6f132f16-20e4-418c-8dee-b99d5f86d320.html?75910
[code=SQL]