求一SQL语句

smneo 2014-02-21 02:11:10
有点复杂,也不知道直接SQL语句能不能得出想要的结果.先谢过!如下:
表A 有如下字段

ID CODE VALUE TIME
1 3101 13 2014-02-21 10:08:06
2 3101 15 2014-02-21 10:08:41
3 3101 12 2014-02-21 10:08:48
4 3101 13 2014-02-21 10:09:08

5 3101 14 2014-02-21 10:20:55
6 3101 17 2014-02-21 10:21:22
7 3101 19 2014-02-21 10:21:33
8 3101 13 2014-02-21 10:21:58
9 3101 13 2014-02-21 10:22:08
10 3101 13 2014-02-21 10:22:22
11 3101 12 2014-02-21 10:23:01
12 3101 13 2014-02-21 10:23:12
13 3101 15 2014-02-21 10:23:22
14 3101 13 2014-02-21 10:23:45
15 3101 17 2014-02-21 10:24:08

16 3101 13 2014-02-21 11:08:06
17 3101 13 2014-02-21 11:08:41
18 3101 9 2014-02-21 11:08:44
19 3101 12 2014-02-21 11:08:49
20 3101 13 2014-02-21 11:09:08
21 3101 16 2014-02-21 11:10:01

需要的一个结果为
1.如果连续60秒VALUE值都是大于10的则记录为一条,超过120秒,则记录为两条记录120秒内只记录为一条记录;

2.如果连续60秒VALUE有大于10的但是也有小于10的,只要有小于10的就从下一条大于10的开始重新计算,小于10之前的就忽略;

以上记录查询的结果为

ID CODE MAX_VALUE BEGINTIME ENDTIME
1 3101 15 2014-02-21 10:08:06 2014-02-21 10:09:08
2 3101 19 2014-02-21 10:20:55 2014-02-21 10:22:22
3 3103 17 2014-02-21 10:23:01 2014-02-21 10:24:08

结果描述:
ID1为表中ID 1-4为一分钟以上没有到到120秒连续VALUE值大于10的,所以记录为一条记录
ID2和3为表中ID 5-15为连续60秒以上,且超过了120秒,且第二条也超过60秒,所以记录为两条
表中16-21因为不满足连续VALUE都大于10,所以不算入统计.
...全文
189 10 打赏 收藏 转发到动态 举报
写回复
用AI写文章
10 条回复
切换为时间正序
请发表友善的回复…
发表回复
smneo 2014-02-22
  • 打赏
  • 举报
 回复
引用 6 楼 t101lian 的回复:
写了一下午，楼主记得结贴。。
[/quote] 非常感谢,我还需要一个统计到这条结果记录的一个"开始"和"结束"时间,能不能把结束时间也给我计算在内呢? 新开一贴,劳烦t101lian再给看看. http://bbs.csdn.net/topics/390714220
t101lian 2014-02-22
  • 打赏
  • 举报
 回复
引用 9 楼 smneo 的回复:
引用 6 楼 t101lian 的回复:
写了一下午，楼主记得结贴。。
非常感谢,我还需要一个统计到这条结果记录的一个"开始"和"结束"时间,能不能把结束时间也给我计算在内呢? 新开一贴,劳烦t101lian再给看看. http://bbs.csdn.net/topics/390714220[/quote]
t101lian 2014-02-21
  • 打赏
  • 举报
 回复
简化一下 

--开始查询
;with
    cte as(
 select *,case when (select count(1) from #a where id=a.id-1 and
   datediff(s,[time],a.[time])>60 )=0 and a.[value]>10 then 10 else 11 end 
 as t from #a a   
)  , cc as
(
 select * from cte a where t=10 and
exists (select 1 from cte where t=11 and datediff(s,[time],a.[time]) <=120 and
datediff(s,[time],(select [time] from cte  where id=a.id+1))>120) 
or 
exists(select 1 from cte  where t=11 and a.id=id-1 and [value]>10) 
 or  a.id=(select max(id) from cte where t=10) 
)
select rank() over (order by id) as id,code,value,time from cc
fengxiaohan211 2014-02-21
  • 打赏
  • 举报
 回复
楼上辛苦···········
t101lian 2014-02-21
  • 打赏
  • 举报
 回复
写了一下午，楼主记得结贴。。
引用 3 楼 smneo 的回复:
结果集中还应该有一条 4 3101 16 2014-02-21 11:08:49 2014-02-21 11:10:01 表中ID16-18被丢弃,后面的重新计算得出一条
t101lian 2014-02-21
  • 打赏
  • 举报
 回复 
--开始查询
;with cte as(
 select *,case when (select count(1) from #a where id=a.id-1 and
   datediff(s,[time],a.[time])>60 )=0 and a.[value]>10 then 10 else 11 end 
 as t from #a a   
)  ,  cc as( select * from cte a where t=10 and
exists (select 1 from cte where t=11 and datediff(s,[time],a.[time]) <=120 and
datediff(s,[time],(select [time] from cte  where id=a.id+1))>120) 
 union all 
 select * from cte a where t=10  and
exists(select 1 from cte  where t=11 and a.id=id-1 and [value]>10) 
 union all 
select   * from cte a where  a.id=(select max(id) from cte where t=10)
and  datediff(s,(select max(Convert(varchar,[time],120)) from cte where t=11),a.[time])>60
)
select rank() over (order by id) as id,code,value,time from cc
--查询结果
/*
id                   code        value       time
-------------------- ----------- ----------- --------------------
1                    3101        13          2014-02-21 10:09:08
2                    3101        13          2014-02-21 10:22:22
3                    3101        17          2014-02-21 10:24:08
4                    3101        16          2014-02-21 11:10:01

(4 行受影响)

*/
t101lian 2014-02-21
  • 打赏
  • 举报
 回复 
if object_id('tempdb..#a') is not null drop table #a
go
create table #a
(
   id int,
   code int ,
   [value] int,
   [time]  varchar(20)
)
insert into #a 
select 1,3101,13,'2014-02-21 10:08:06' union all
select 2,3101,15,'2014-02-21 10:08:41' union all
select 3,3101,12,'2014-02-21 10:08:48' union all
select 4,3101,13,'2014-02-21 10:09:08' union all
select 5,3101,14,'2014-02-21 10:20:55' union all
select 6,3101,17,'2014-02-21 10:21:22' union all
select 7,3101,19,'2014-02-21 10:21:33' union all
select 8,3101,13,'2014-02-21 10:21:58' union all
select 9,3101,13,'2014-02-21 10:22:08' union all
select 10,3101,13,'2014-02-21 10:22:22' union all
select 11,3101,12,'2014-02-21 10:23:01' union all
select 12,3101,13,'2014-02-21 10:23:12' union all
select 13,3101,15,'2014-02-21 10:23:22' union all
select 14,3101,13,'2014-02-21 10:23:45' union all
select 15,3101,17,'2014-02-21 10:24:08' union all
select 16,3101,13,'2014-02-21 11:08:06' union all
select 17,3101,13,'2014-02-21 11:08:41' union all
select 18,3101,9,'2014-02-21 11:08:44' union all
select 19,3101,12,'2014-02-21 11:08:49' union all
select 20,3101,13,'2014-02-21 11:09:08' union all
select 21,3101,16,'2014-02-21 11:10:01' 
--开始查询
;with cte as(
 select *,case when (select count(1) from #a where id=a.id-1 and
   datediff(s,[time],a.[time])>60 )=0 and a.[value]>10 then 10 else 11 end 
 as t from #a a   
)   select * from cte a where t=10 and
exists (select 1 from cte where t=11 and datediff(s,[time],a.[time]) <=120 and
datediff(s,[time],(select [time] from cte  where id=a.id+1))>120) 
 union all 
 select * from cte a where t=10  and
exists(select 1 from cte  where t=11 and a.id=id-1 and [value]>10) 
 union all 
select   * from cte a where  a.id=(select max(id) from cte where t=10)
and  datediff(s,(select max(Convert(varchar,[time],120)) from cte where t=11),a.[time])>60
--查询结果
/*
id          code        value       time                 t
----------- ----------- ----------- -------------------- -----------
10          3101        13          2014-02-21 10:22:22  10
4           3101        13          2014-02-21 10:09:08  10
15          3101        17          2014-02-21 10:24:08  10
21          3101        16          2014-02-21 11:10:01  10

(4 行受影响)

*/
smneo 2014-02-21
  • 打赏
  • 举报
 回复
结果集中还应该有一条 4 3101 16 2014-02-21 11:08:49 2014-02-21 11:10:01 表中ID16-18被丢弃,后面的重新计算得出一条
smneo 2014-02-21
  • 打赏
  • 举报
 回复
哎呀呀,都是手工编辑的,我大意了,应该要计算在内的.
t101lian 2014-02-21
  • 打赏
  • 举报
 回复
引用 楼主 smneo 的回复:
有点复杂,也不知道直接SQL语句能不能得出想要的结果.先谢过!如下: 表A 有如下字段 ID CODE VALUE TIME 1 3101 13 2014-02-21 10:08:06 2 3101 15 2014-02-21 10:08:41 3 3101 12 2014-02-21 10:08:48 4 3101 13 2014-02-21 10:09:08 5 3101 14 2014-02-21 10:20:55 6 3101 17 2014-02-21 10:21:22 7 3101 19 2014-02-21 10:21:33 8 3101 13 2014-02-21 10:21:58 9 3101 13 2014-02-21 10:22:08 10 3101 13 2014-02-21 10:22:22 11 3101 12 2014-02-21 10:23:01 12 3101 13 2014-02-21 10:23:12 13 3101 15 2014-02-21 10:23:22 14 3101 13 2014-02-21 10:23:45 15 3101 17 2014-02-21 10:24:08 16 3101 13 2014-02-21 11:08:06 17 3101 13 2014-02-21 11:08:41 18 3101 9 2014-02-21 11:08:44 19 3101 12 2014-02-21 11:08:49 20 3101 13 2014-02-21 11:09:08 21 3101 16 2014-02-21 11:10:01 需要的一个结果为 1.如果连续60秒VALUE值都是大于10的则记录为一条,超过120秒,则记录为两条记录120秒内只记录为一条记录; 2.如果连续60秒VALUE有大于10的但是也有小于10的,只要有小于10的就从下一条大于10的开始重新计算,小于10之前的就忽略; 以上记录查询的结果为 ID CODE MAX_VALUE BEGINTIME ENDTIME 1 3101 15 2014-02-21 10:08:06 2014-02-21 10:09:08 2 3101 19 2014-02-21 10:20:55 2014-02-21 10:22:22 3 3103 17 2014-02-21 10:23:01 2014-02-21 10:24:08 结果描述: ID1为表中ID 1-4为一分钟以上没有到到120秒连续VALUE值大于10的,所以记录为一条记录 ID2和3为表中ID 5-15为连续60秒以上,且超过了120秒,且第二条也超过60秒,所以记录为两条 表中16-21因为不满足连续VALUE都大于10,所以不算入统计.
2.如果连续60秒VALUE有大于10的但是也有小于10的,只要有小于10的就从下一条大于10的开始重新计算,小于10之前的就忽略; ID为18的小于10，从ID 19开始重新计算，那ID为21的符合连续60秒VALUE值都是大于10的条件，怎么不在查询结果内？
SQL语句基础教程
SQL语句基础教程
SQL学习资料（包含所有常用的语句例子）
sql学习必备，内含sql一些常用语句的例子，有图有表，通过结果来理解sql语句的实现与结果。
oracle sql developer 执行sql语句,如何使用SQL Developer执行SQL语句（上）
本章摘自Murach的《Orcle SQL和PL/SQL》一书，描述了如何使用Oracle SQL Developer以及其它工具。在这一部分，我们将会学习如何使用SQL Developer来输入并执行SQL语句及脚本。如何使用摘录窗口，解决常见的语法错误等内容。在你回顾数据库设计的同时，SQL Developer还是一个很好的可输入并运行SQL语句的工具。在接下来的部分你将学习到。如何输入并执行...
PLSQL执行SQL语句
PLSQL执行SQL语句 1、执行中量SQL语句 当执行SQL语句较大时，除运行SQL语句外可以点击工具-------&gt;导入表---------&gt;（出现界面如下图所示） 点击导入就会出现执行语句的窗口如下图所示，把语句粘贴进去，自动执行，执行完毕，提交事务，导入成功！ 也可以直接打开命令行方式：点击新增SQL窗口直接执行。  ...
SQL语句一对多查询
效果如下 2个表 spr_course sys_user 表中我们可以发现一个科目会有多个老师教,如果我们直接进行表连接的话就会出现问题,这就牵扯到我们的一对多的sql语句,从而让我们写一条sql语句就带到一对多的效果 sql语句一对多(spr_course和sys_user表) SELECT spr_course.*,GROUP_CONCAT(sys_user.`username`) AS...
疑难问题

22,210

社区成员

121,726

社区内容

发帖
与我相关
我的任务
社区描述
MS-SQL Server 疑难问题
社区管理员
  • 疑难问题社区
  • 尘觉
加入社区
  • 近7日
  • 近30日
  • 至今

加载中

查看更多榜单
社区公告
暂无公告

试试用AI创作助手写篇文章吧

+ 用AI写文章