如何查询记录中的临界点的记录

[毕业一年了]

表是个流水的记录，假设每5秒记录一次当前的温度 ，如下：

TimePoint TempValue PF
20131201080100 20.02 0.2
20131201080105 27.33 1.2
20131201080110 31.36 1.4
20131201080115 32.00 1.8
20131201080120 28.59 0.2
20131201080125 50.26 1.4
20131201080130 20.34 1.6
20131201080135 40.18 1.5
20131201080140 35.00 1.0
20131201080145 20.21 0.2
20131201080150 20.54 0.2
20131201080155 31.22 0.9

得到数据
20131201080100 20.02 关
20131201080110 31.36 开
20131201080120 28.59 关
20131201080135 40.18 开
20131201080145 20.21 关

中间隔多少条记录不一定，就是要看那个临界的记录，根据 后面两个列进行判断，
TempValue 在 30.00-50.00 之间 并且 PF 在 1.0-2.0之间

这种查询，因个人水平问题，除了循环判断，没有太好的想法，求大神指点。

附上测试代码

with tb(TimePoint,TempValue,PF)as(
select 20131201080100,20.02,0.2 union 
select 20131201080105,27.33,1.2 union
select 20131201080110,31.36,1.4 union
select 20131201080115,32.00,1.8 union
select 20131201080120,28.59,0.2 union
select 20131201080125,50.26,1.4 union
select 20131201080130,20.34,1.6 union
select 20131201080135,40.18,1.5 union
select 20131201080140,35.00,1.0 union
select 20131201080145,20.21,0.2 union
select 20131201080150,20.54,0.2 union
select 20131201080155,31.22,0.9 )
select * from tb

[xdashewan]

SELECT C.* FROM(
SELECT TOP 1 * FROM tb ORDER BY TimePoint
UNION ALL
SELECT A.* FROM tb A,
          (SELECT * FROM tb WHERE TempValue > 30 AND TempValue < 50 AND PF > 1.0 AND PF < 2.0) B
 WHERE (A.TempValue < 30 OR A.TempValue > 50 OR A.PF < 1.0 OR A.PF > 2.0)
AND A.TimePoint - 5 = B.TimePoint 
UNION ALL
SELECT A.* FROM tb A
         (SELECT * FROM tb WHERE TempValue < 30 OR TempValue > 50 OR PF < 1.0 OR PF > 2.0) B
 WHERE (A.TempValue > 30 AND A.TempValue < 50 AND A.PF > 1.0 AND A.PF < 2.0) AND A.TimePoint - 5 = B.TimePoint) C
ORDER BY TimePoint

注意：我在A.TimePoint - 5时候偷了个懒，其实应该转换成日期型扣除5秒，我只表达了扣除5秒的意思，还请自行转换

[毕业一年了]

引用 1 楼 xdashewan 的回复:

SELECT C.* FROM(
SELECT TOP 1 * FROM tb ORDER BY TimePoint
UNION ALL
SELECT A.* FROM tb A,
          (SELECT * FROM tb WHERE TempValue > 30 AND TempValue < 50 AND PF > 1.0 AND PF < 2.0) B
 WHERE (A.TempValue < 30 OR A.TempValue > 50 OR A.PF < 1.0 OR A.PF > 2.0)
AND A.TimePoint - 5 = B.TimePoint 
UNION ALL
SELECT A.* FROM tb A
         (SELECT * FROM tb WHERE TempValue < 30 OR TempValue > 50 OR PF < 1.0 OR PF > 2.0) B
 WHERE (A.TempValue > 30 AND A.TempValue < 50 AND A.PF > 1.0 AND A.PF < 2.0) AND A.TimePoint - 5 = B.TimePoint) C
ORDER BY TimePoint

注意：我在A.TimePoint - 5时候偷了个懒，其实应该转换成日期型扣除5秒，我只表达了扣除5秒的意思，还请自行转换

最后一个关没出来结果，另外实际应用中，不能用时间-5，这个虽然是主键，但是不一定都是5秒，应该用个行号-1之类的来判断。像这个应该怎么写呢，请再指点一下，谢谢。

[t101lian]

if object_id('tempdb..#a') is not null drop table #a

create table #a (TimePoint varchar(50),TempValue float,PF float)
insert into #a
select 20131201080100,20.02,0.2 union 
select 20131201080105,27.33,1.2 union
select 20131201080110,31.36,1.4 union
select 20131201080115,32.00,1.8 union
select 20131201080120,28.59,0.2 union
select 20131201080125,50.26,1.4 union
select 20131201080130,20.34,1.6 union
select 20131201080135,40.18,1.5 union
select 20131201080140,35.00,1.0 union
select 20131201080145,20.21,0.2 union
select 20131201080150,20.54,0.2 union
select 20131201080155,31.22,0.9 
--   select * from #a
---开始查询
;with cte as(
select  * , row_number() over (order by getdate()) num,
ison=(case when TempValue between 30.00 and 50.00 and PF between 1.0 and 2.0  then 2 else 1 end)
 from #a) 
 select TimePoint,TempValue,ison=case when ison=1 then '关' else '开' end  from cte a where 
not exists(select 1 from cte where num=a.num-1 and ison=a.ison)

----查询结果
TimePoint                                          TempValue              ison
-------------------------------------------------- ---------------------- ----
20131201080100                                     20.02                  关
20131201080110                                     31.36                  开
20131201080120                                     28.59                  关
20131201080135                                     40.18                  开
20131201080145                                     20.21                  关

(5 行受影响)



 

[t101lian]

引用 楼主 fishsoftdotnet 的回复:

表是个流水的记录，假设每5秒记录一次当前的温度 ，如下： TimePoint TempValue PF 20131201080100 20.02 0.2 20131201080105 27.33 1.2 20131201080110 31.36 1.4 20131201080115 32.00 1.8 20131201080120 28.59 0.2 20131201080125 50.26 1.4 20131201080130 20.34 1.6 20131201080135 40.18 1.5 20131201080140 35.00 1.0 20131201080145 20.21 0.2 20131201080150 20.54 0.2 20131201080155 31.22 0.9 得到数据 20131201080100 20.02 关 20131201080110 31.36 开 20131201080120 28.59 关 20131201080135 40.18 开 20131201080145 20.21 关 中间隔多少条记录不一定，就是要看那个临界的记录，根据 后面两个列进行判断， TempValue 在 30.00-50.00 之间 并且 PF 在 1.0-2.0之间 这种查询，因个人水平问题，除了循环判断，没有太好的想法，求大神指点。 附上测试代码

with tb(TimePoint,TempValue,PF)as(
select 20131201080100,20.02,0.2 union 
select 20131201080105,27.33,1.2 union
select 20131201080110,31.36,1.4 union
select 20131201080115,32.00,1.8 union
select 20131201080120,28.59,0.2 union
select 20131201080125,50.26,1.4 union
select 20131201080130,20.34,1.6 union
select 20131201080135,40.18,1.5 union
select 20131201080140,35.00,1.0 union
select 20131201080145,20.21,0.2 union
select 20131201080150,20.54,0.2 union
select 20131201080155,31.22,0.9 )
select * from tb

让楼主久等了

[毕业一年了]

引用 4 楼 t101lian 的回复:

[quote=引用 楼主 fishsoftdotnet 的回复:] 表是个流水的记录，假设每5秒记录一次当前的温度 ，如下： TimePoint TempValue PF 20131201080100 20.02 0.2 20131201080105 27.33 1.2 20131201080110 31.36 1.4 20131201080115 32.00 1.8 20131201080120 28.59 0.2 20131201080125 50.26 1.4 20131201080130 20.34 1.6 20131201080135 40.18 1.5 20131201080140 35.00 1.0 20131201080145 20.21 0.2 20131201080150 20.54 0.2 20131201080155 31.22 0.9 得到数据 20131201080100 20.02 关 20131201080110 31.36 开 20131201080120 28.59 关 20131201080135 40.18 开 20131201080145 20.21 关 中间隔多少条记录不一定，就是要看那个临界的记录，根据 后面两个列进行判断， TempValue 在 30.00-50.00 之间 并且 PF 在 1.0-2.0之间 这种查询，因个人水平问题，除了循环判断，没有太好的想法，求大神指点。 附上测试代码

with tb(TimePoint,TempValue,PF)as(
select 20131201080100,20.02,0.2 union 
select 20131201080105,27.33,1.2 union
select 20131201080110,31.36,1.4 union
select 20131201080115,32.00,1.8 union
select 20131201080120,28.59,0.2 union
select 20131201080125,50.26,1.4 union
select 20131201080130,20.34,1.6 union
select 20131201080135,40.18,1.5 union
select 20131201080140,35.00,1.0 union
select 20131201080145,20.21,0.2 union
select 20131201080150,20.54,0.2 union
select 20131201080155,31.22,0.9 )
select * from tb

让楼主久等了[/quote] 服了，真简单，哎，有空还是要继续学习啊！！！

[yoan2014]

DROP TABLE #t
GO
with tb(TimePoint,TempValue,PF) AS (
select 20131201080100,20.02,0.2 union 
select 20131201080105,27.33,1.2 union
select 20131201080110,31.36,1.4 union
select 20131201080115,32.00,1.8 union
select 20131201080120,28.59,0.2 union
select 20131201080125,50.26,1.4 union
select 20131201080130,20.34,1.6 union
select 20131201080135,40.18,1.5 union
select 20131201080140,35.00,1.0 union
select 20131201080145,20.21,0.2 union
select 20131201080150,20.54,0.2 union
select 20131201080155,31.22,0.9 )
--产生中间数据
SELECT timepoint , CASE WHEN TempValue BETWEEN 30 AND 50 AND PF BETWEEN 1 AND 2 THEN  N'开' ELSE N'关' END AS kg, tempvalue INTO #t FROM tb

with b as(SELECT * , lag(kg , 1) OVER(ORDER BY timepoint) AS kg1   FROM #t) 
--执行查询
SELECT * FROM b  
WHERE kg1 IS NULL OR kg <> kg1
--结果
timepoint                               kg   tempvalue                               kg1
--------------------------------------- ---- --------------------------------------- ----
20131201080100                          关    20.02                                   NULL
20131201080110                          开    31.36                                   关
20131201080120                          关    28.59                                   开
20131201080135                          开    40.18                                   关
20131201080145                          关    20.21                                   开

(5 row(s) affected)