请问一个sql语言查询的问题?
declare @result int,@index int
set @result=1
select index from monitordata order by datetime
我需要查询的数据保存到index:
比如select index from monitordata order by datetime查询的数据为:
1
2
3
5
6
9(这个只是举例,查询的数据未必是如此)
需要进行下面处理:当当前数据比前面一个数据大一的时候@result加上1,例如(例如(2-1=1时)这个怎么处理呀,有谁会吗?
declare @result int,@index int
set @result=1
select index from monitordata order by datetime
我需要查询的数据保存到index:
比如select index from monitordata order by datetime查询的数据为:
1
2
3
5
6
9(这个只是举例,查询的数据未必是如此)
需要进行下面处理:当当前数据比前面一个数据大一的时候@result加上1,例如(例如(2-1=1时)这个怎么处理呀,有谁会吗?
2>1 @result=@result+(2-1)=2
3>2 @result=@result+(3-2)=3
5>3 @result=@result+(5-3)=5
是不是上面这么算,那下一个是2呢,又怎么算
select count(distinct(index)) from monitordata
go
create table [monitordata]([index] int)
insert [monitordata]
select 1 union all
select 2 union all
select 3 union all
select 5 union all
select 6 union all
select 9
godeclare @result int
set @result=1
select @result=@result+count(b.[index])
from monitordata a
left join monitordata b on a.[index]=b.[index]+1select @result as result/**
警告: 聚合或其他 SET 操作消除了空值。
result
-----------
4(1 行受影响)
**/
set @result=1
select identity(int,1,1) as [ID], [index]
into #tmp
from monitordata order by datetime
select count(*)+@result from #tmp a,#tmp b
where a.id=b.id-1 and a.[index]<[b.index]drop table #tmp
select 9
上面的9比6大,不用考虑的?
create table monitordata([index] int,[datetime] datetime)
insert monitordata
select 1,'2011-06-20' union all
select 2,'2011-06-21' union all
select 3,'2011-06-22' union all
select 5,'2011-06-23' union all
select 6,'2011-06-24' union all
select 9,'2011-06-25'declare @result int
set @result=1
;with T as (select [index] from monitordata)
select @result=@result+1 from T where exists(select 1 from T as t1 where t1.[index]=T.[index]-1)
select @result
-----------
4(1 row(s) affected)这样?