sql时间比较查询当前时间的数据,前一条,后一条

版权声明:本文为博主原创文章,遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。
本文链接:https://blog.csdn.net/iteye_13003/article/details/82061905

select top 1 * from CheckBill where datediff(second,'2011-05-05 15:56:00',checkDate) > 0 order by CheckDate asc

select top 1 * from CheckBill where datediff(second,checkDate,'2011-05-05 15:56:00') > 0 order by CheckDate desc

select top 1 * from CheckBill where convert(varchar(19),checkDate,120) = '2011-05-05 15:56:00' order by CheckDate desc

select top 3 * from CheckBill order by CheckDate desc


[img]http://dl.iteye.com/upload/attachment/476081/5b76ab08-9d61-3933-be2a-2c649cdbe883.jpg[/img]
展开阅读全文

SQL一条记录减后一条记录

07-11

设备数据表D_DatarnID 日期时间 区域 类型 编号 用户 用电量 用水量 rn1 2009-07-01 08:15:21.357 1 2 1 1 1 1rn2 2009-07-01 08:16:03.483 1 2 1 2 1 2rn3 2009-07-01 08:16:59.420 1 2 1 1 2 1rn4 2009-07-01 08:17:53.623 1 2 1 1 3 2rn5 2009-07-01 08:17:58.247 1 2 1 1 4 2rn6 2009-07-01 08:17:59.450 1 2 1 1 5 3rn7 2009-07-01 08:18:01.357 1 2 1 1 1 4rn8 2009-07-01 08:18:02.780 1 2 2 2 3 6rn9 2009-07-01 10:44:50.840 1 2 2 3 1 0rn10 2009-07-01 10:45:03.950 1 2 1 1 6 4rn11 2009-07-01 10:45:17.293 1 2 3 5 4 0rn12 2009-07-01 10:45:30.577 1 2 1 5 5 0rn13 2009-07-01 10:45:43.827 1 2 1 4 6 0rn14 2009-07-01 10:45:57.030 1 2 2 7 7 0rn15 2009-07-01 10:46:10.250 1 2 1 4 8 0rn16 2009-07-01 10:46:23.873 1 2 3 9 9 0rn17 2009-07-01 10:46:36.653 1 2 2 1 6 4rn18 2009-07-01 10:46:53.500 1 2 3 2 4 8rnrn设备状态表D_StatusrnID 日期时间 区域 类型 编号 用户 状态(1表示设备开启,2表示设备关闭)rn1 2009-07-01 08:17:06.577 1 2 1 1 1rn2 2009-07-01 08:17:54.153 1 2 1 1 1rn3 2009-07-01 08:17:58.450 1 2 1 1 1rn4 2009-07-01 08:17:59.653 1 2 1 1 1rn5 2009-07-01 08:18:01.577 1 2 1 1 1rn6 2009-07-01 08:18:03.530 1 2 1 1 1rn7 2009-07-01 10:44:44.437 1 2 2 2 2rn8 2009-07-01 10:44:57.700 1 2 2 2 2rn9 2009-07-01 10:45:10.937 1 2 2 1 2rn10 2009-07-01 10:45:24.233 1 2 2 1 2rn11 2009-07-01 10:45:37.437 1 2 2 3 2rn12 2009-07-01 10:45:50.623 1 2 2 4 2rn13 2009-07-01 10:46:03.937 1 2 2 2 2rn14 2009-07-01 10:46:17.263 1 2 2 2 2rn15 2009-07-01 10:46:30.437 1 2 2 2 2rn16 2009-07-01 10:46:43.577 1 2 2 4 2rnrn首先根据状态表中设备状态为1时的第一条记录为开始时间,设备状态为2时的第一条记录为结束时间(对每一个编号、用户和区域来说)查询数据表中数据信息。rnrnrn[1]首先查询开始时间和结束时间:rn 比如:区域为1,编号为1,用户为1的第一条记录rn 设备启动时间:2009-07-01 08:17:06.577,设备停止时间:2009-07-01 10:45:10.937rnrn[2]然后根据开始时间和结束时间查询对应的数据rn 比如:对应数据为:rn 2009-07-01 08:18:01.357 1 2 1 1 1 4rn 2009-07-01 10:45:03.950 1 2 1 1 6 4rnrnrn这个SQL语句怎么写,还请大家多多帮忙啊?小弟是初来乍道的,多多指教,谢谢了!rnrnrnrn 论坛

没有更多推荐了,返回首页