declare @result int
select index from monitordata order by datetime如果我想把查询的index进行分析,如果后面比前面的值大一,则result+1;怎么在sql语句中实现呀?
可以指导下嘛?我只会在程序中解决,在sql中不会,各位大侠求救了呀!
select index from monitordata order by datetime如果我想把查询的index进行分析,如果后面比前面的值大一,则result+1;怎么在sql语句中实现呀?
可以指导下嘛?我只会在程序中解决,在sql中不会,各位大侠求救了呀!
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]
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
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
*/
[Quote=引用 5 楼 zy112429 的回复:]SQL codedeclare @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=(……不是我的意思是这样的呀?
select dataindex from monitordata order by datetime
查询出的的结果:
1
2
3
4
6
9
最后我分析这个查询的值:
例如2-1-1;3-2=1;4-3=1;
最后我需要的结果:3。我只要这个当前值减去前值为1的数据总数的结果!
select dataindex from monitordata order by datetime
查询出的的结果:
1
2
3
4
6
9
最后我分析这个查询的值:
例如2-1-1;3-2=1;4-3=1;
最后我需要的结果:3。我只要这个当前值减去前值为1的数据总数的结果!
create table t1
(
id int
)
insert into t1
select 1 union all
select 2 union all
select 3 union all
select 4 union all
select 6 union all
select 9select COUNT(*) from t1 as a inner join t1 as b on a.id=b.id-1