如何分析在sql语句中查询的值？

declare @result int
select index from monitordata order by datetime

如果我想把查询的index进行分析，如果后面比前面的值大一，则result+1；怎么在sql语句中实现呀？
可以指导下嘛？我只会在程序中解决，在sql中不会，各位大侠求救了呀！

作者: chp845 发布时间: 2011-06-16

给出数据，及你想要的结果

作者: maco_wang 发布时间: 2011-06-16

SQL code

declare @result int,@index int
select @result=0,@index=0
with cte as
(select [index] from monitordata order by [datetime])

update cte set @result=(case when @index<[index] then @result+1 else @result end),
       @index=[index]

作者: zy112429 发布时间: 2011-06-16

引用 1 楼 maco_wang 的回复:

给出数据，及你想要的结果

1
2
3
4
6
9
结果为3.

作者: chp845 发布时间: 2011-06-16

引用 2 楼 zy112429 的回复:

SQL code
declare @result int,@index int
select @result=0,@index=0
with cte as
(select [index] from monitordata order by [datetime])

update cte set @result=(case when @index<[index] then @result+1 e……

不行吧！！貌似

作者: chp845 发布时间: 2011-06-16

SQL code


declare @result int,@index int
select @result=1,@index=0

(select 1 [index] into cte union select 2 union select 3 union
 select 4 union select 6 union select 9 )

update cte set @result=(case when @index+1<[index] then @result+1 else @result end),
       @index=[index]
select  @result     
drop table cte
/*

-----------
3

作者: zy112429 发布时间: 2011-06-16

SQL code


declare @monitordata table ([index] int,datetime datetime)
insert into @monitordata
select 1,'2010-09-08' union all
select 2,'2010-09-10' union all
select 3,'2010-09-15' union all
select 4,'2010-09-22' union all
select 6,'2010-09-27' union all
select 9,'2010-10-01'

;with maco as( select row_number() over 
(order by datetime) as rid,* from @monitordata)

select count(*) from maco a left join maco b on 
a.rid=b.rid-1 where b.[index]-a.[index]=1

/*
3
*/

作者: maco_wang 发布时间: 2011-06-16

如何分析在sql语句中查询的值？

热门频道