--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]([编号] int,[温度] int) insert [test] select 1,4 union all select 1,11 union all select 1,12 union all select 1,20 union all select 2,4 union all select 2,10;with t as( select ROW_NUMBER()over(partition by [编号] order by getdate()) as row_num, * from test ) select * from( select [编号],[温度], case when [温度b] is null then 0 else [温度b]-[温度] end as [温度差] from( select a.*,b.[温度] as [温度b] from t a left join t b on a.row_num=b.row_num-1 and a.编号=b.编号)a)b where [温度差]>=3/* 编号 温度 温度差 1 4 7 1 12 8 2 4 6 */
;with t as( select ROW_NUMBER()over(order by getdate()) as row_num, * from test ) select * from( select [编号],[温度], case when [温度b] is null then 0 else [温度b]-[温度] end as [温度差] from( select a.*,b.[温度] as [温度b] from t a left join t b on a.row_num=b.row_num-1 )a)b where [温度差]>=3/* 编号 温度 温度差 1 4 7 1 12 8 2 4 6 */ --如果不考虑不同编号的话就是这个方法就好了
Create table tempe([编号] int, [温度] int) go insert into tempe([编号], [温度]) select 1, 4 union all select 1, 11 union all select 1, 12 union all select 1, 20 union all select 2, 4 union all select 2, 10;with T as ( select rn = row_number() over(order by [编号]) ,[编号], [温度] from tempe ) select a.[编号], a.[温度] from T a join T b on a.[编号] = b.[编号] and a.rn = b.rn - 1 and abs(a.[温度] - b.[温度]) > 3 drop table tempe; /* 编号 温度 ----------- ----------- 1 4 1 12 2 4 */
--> 测试数据:[test]
if object_id('[test]') is not null drop table [test]
create table [test]([编号] int,[温度] int)
insert [test]
select 1,4 union all
select 1,11 union all
select 1,12 union all
select 1,20 union all
select 2,4 union all
select 2,10;with t
as(
select ROW_NUMBER()over(partition by [编号] order by getdate()) as row_num,
* from test
)
select * from(
select [编号],[温度],
case when [温度b] is null then 0 else [温度b]-[温度] end as [温度差]
from(
select a.*,b.[温度] as [温度b] from t a
left join t b on a.row_num=b.row_num-1
and a.编号=b.编号)a)b where [温度差]>=3/*
编号 温度 温度差
1 4 7
1 12 8
2 4 6
*/
;with t
as(
select ROW_NUMBER()over(order by getdate()) as row_num,
* from test
)
select * from(
select [编号],[温度],
case when [温度b] is null then 0 else [温度b]-[温度] end as [温度差]
from(
select a.*,b.[温度] as [温度b] from t a
left join t b on a.row_num=b.row_num-1
)a)b where [温度差]>=3/*
编号 温度 温度差
1 4 7
1 12 8
2 4 6
*/
--如果不考虑不同编号的话就是这个方法就好了
Create table tempe([编号] int, [温度] int)
go
insert into tempe([编号], [温度])
select 1, 4 union all
select 1, 11 union all
select 1, 12 union all
select 1, 20 union all
select 2, 4 union all
select 2, 10;with T as
(
select rn = row_number() over(order by [编号])
,[编号], [温度]
from tempe
)
select a.[编号], a.[温度]
from T a
join T b
on a.[编号] = b.[编号]
and a.rn = b.rn - 1
and abs(a.[温度] - b.[温度]) > 3
drop table tempe;
/*
编号 温度
----------- -----------
1 4
1 12
2 4
*/