create table #1(id int,portid int,tstamp datetime,immFlux int,totalFlux int,currAmount int)insert #1 values(3348, 11, '2004-1-12 13:07:00', 105.75, 1020888, 1495)
insert #1 values(3349 ,11, '2004-1-12 13:30:00', 83.32 ,1021026 ,1633)
insert #1 values(3350 ,11, '2004-1-12 13:54:00', 61.26 ,1021122 ,1729)
insert #1 values(3351 ,11, '2004-1-12 14:11:00', 63.59 ,1021191 ,1798)
insert #1 values(3352 ,11, '2004-1-12 14:17:00', 57.84 ,1021214 ,1821)
insert #1 values(3353 ,11, '2004-1-12 14:35:00', 61.26 ,1021276 ,1883)
insert #1 values(3354 ,11, '2004-1-12 14:41:00', 60.69 ,1021299 ,1906)
insert #1 values(3355 ,11, '2004-1-12 15:05:00', 56.72 ,1021381 ,1988)
insert #1 values(3356 ,11, '2004-1-12 15:29:00', 57.84 ,1021462 ,2069)
insert #1 values(3357 ,11, '2004-1-12 15:52:00', 65.35 ,1021550 ,2157)
insert #1 values(3358 ,11, '2004-1-12 16:10:00', 66.54 ,1021621 ,2228)
insert #1 values(3359 ,11, '2004-1-12 16:16:00', 66.54 ,1021644 ,2251)
insert #1 values(3360 ,11, '2004-1-12 16:40:00', 66.54 ,1021738 ,2345)
insert #1 values(3361 ,11, '2004-1-12 17:03:00', 66.54 ,1021832 ,2439)
insert #1 values(3362 ,11, '2004-1-12 17:27:00', 66.54 ,1021927 ,2534)
insert #1 values(3363 ,11, '2004-1-12 17:51:00', 93.93 ,1022046 ,2653)
insert #1 values(3364 ,11, '2004-1-12 18:11:00', 93.26 ,1022158 ,2765)
insert #1 values(3365 ,11, '2004-1-12 18:14:00', 101.51, 1022179 ,2786)
insert #1 values(3366 ,11, '2004-1-12 18:38:00', 98.73, 1022327 ,2934)
insert #1 values(3367 ,11, '2004-1-12 19:02:00', 79.46, 1022451 ,3058)
insert #1 values(3368 ,11, '2004-1-12 19:25:00', 60.12, 1022552 ,3159)
insert #1 values(3369 ,11, '2004-1-12 19:49:00', 67.73, 1022647 ,3254)
insert #1 values(3370 ,11, '2004-1-12 20:13:00', 78.2 ,1022754 ,3361)
insert #1 values(3371 ,11, '2004-1-12 20:37:00', 75.06, 1022866 ,3473)
select a.*
from #1 a
join
(
select tstamp=min(tstamp)
from #1
group by convert(char(13),tstamp,120)
) b
on a.tstamp=b.tstampid portid tstamp immFlux totalFlux currAmount
----------- ----------- ----------------------- ----------- ----------- -----------
3348 11 2004-01-12 13:07:00.000 105 1020888 1495
3351 11 2004-01-12 14:11:00.000 63 1021191 1798
3355 11 2004-01-12 15:05:00.000 56 1021381 1988
3358 11 2004-01-12 16:10:00.000 66 1021621 2228
3361 11 2004-01-12 17:03:00.000 66 1021832 2439
3364 11 2004-01-12 18:11:00.000 93 1022158 2765
3367 11 2004-01-12 19:02:00.000 79 1022451 3058
3370 11 2004-01-12 20:13:00.000 78 1022754 3361(8 row(s) affected)这是在SQL中过滤得到的 整点数据
现在是 从数据库中 得到 是某一天的全部数据的DataView,如何过滤DataView得到想要的接近整点(去整点后面的上面的不考虑)的一条数据?
求解:
insert #1 values(3349 ,11, '2004-1-12 13:30:00', 83.32 ,1021026 ,1633)
insert #1 values(3350 ,11, '2004-1-12 13:54:00', 61.26 ,1021122 ,1729)
insert #1 values(3351 ,11, '2004-1-12 14:11:00', 63.59 ,1021191 ,1798)
insert #1 values(3352 ,11, '2004-1-12 14:17:00', 57.84 ,1021214 ,1821)
insert #1 values(3353 ,11, '2004-1-12 14:35:00', 61.26 ,1021276 ,1883)
insert #1 values(3354 ,11, '2004-1-12 14:41:00', 60.69 ,1021299 ,1906)
insert #1 values(3355 ,11, '2004-1-12 15:05:00', 56.72 ,1021381 ,1988)
insert #1 values(3356 ,11, '2004-1-12 15:29:00', 57.84 ,1021462 ,2069)
insert #1 values(3357 ,11, '2004-1-12 15:52:00', 65.35 ,1021550 ,2157)
insert #1 values(3358 ,11, '2004-1-12 16:10:00', 66.54 ,1021621 ,2228)
insert #1 values(3359 ,11, '2004-1-12 16:16:00', 66.54 ,1021644 ,2251)
insert #1 values(3360 ,11, '2004-1-12 16:40:00', 66.54 ,1021738 ,2345)
insert #1 values(3361 ,11, '2004-1-12 17:03:00', 66.54 ,1021832 ,2439)
insert #1 values(3362 ,11, '2004-1-12 17:27:00', 66.54 ,1021927 ,2534)
insert #1 values(3363 ,11, '2004-1-12 17:51:00', 93.93 ,1022046 ,2653)
insert #1 values(3364 ,11, '2004-1-12 18:11:00', 93.26 ,1022158 ,2765)
insert #1 values(3365 ,11, '2004-1-12 18:14:00', 101.51, 1022179 ,2786)
insert #1 values(3366 ,11, '2004-1-12 18:38:00', 98.73, 1022327 ,2934)
insert #1 values(3367 ,11, '2004-1-12 19:02:00', 79.46, 1022451 ,3058)
insert #1 values(3368 ,11, '2004-1-12 19:25:00', 60.12, 1022552 ,3159)
insert #1 values(3369 ,11, '2004-1-12 19:49:00', 67.73, 1022647 ,3254)
insert #1 values(3370 ,11, '2004-1-12 20:13:00', 78.2 ,1022754 ,3361)
insert #1 values(3371 ,11, '2004-1-12 20:37:00', 75.06, 1022866 ,3473)
select a.*
from #1 a
join
(
select tstamp=min(tstamp)
from #1
group by convert(char(13),tstamp,120)
) b
on a.tstamp=b.tstampid portid tstamp immFlux totalFlux currAmount
----------- ----------- ----------------------- ----------- ----------- -----------
3348 11 2004-01-12 13:07:00.000 105 1020888 1495
3351 11 2004-01-12 14:11:00.000 63 1021191 1798
3355 11 2004-01-12 15:05:00.000 56 1021381 1988
3358 11 2004-01-12 16:10:00.000 66 1021621 2228
3361 11 2004-01-12 17:03:00.000 66 1021832 2439
3364 11 2004-01-12 18:11:00.000 93 1022158 2765
3367 11 2004-01-12 19:02:00.000 79 1022451 3058
3370 11 2004-01-12 20:13:00.000 78 1022754 3361(8 row(s) affected)这是在SQL中过滤得到的 整点数据
现在是 从数据库中 得到 是某一天的全部数据的DataView,如何过滤DataView得到想要的接近整点(去整点后面的上面的不考虑)的一条数据?
求解:
如Pho_Time在数据库时是:2009-02-27 09:17:
<%# Eval("Pho_Time", "{0:d}")%> 显示的数据就是 2009-02-27
或者: <%# ((DateTime) Eval("Pho_Time")).ToShortDateString()%>结果也是:2009-02-27不知道你要的是不是这样,还是我理解错了你的意思
insert #1 values(3348, 11, '2004-1-12 13:07:00', 105.75, 1020888, 1495)
insert #1 values(3349 ,11, '2004-1-12 13:30:00', 83.32 ,1021026 ,1633)
insert #1 values(3350 ,11, '2004-1-12 13:54:00', 61.26 ,1021122 ,1729)
insert #1 values(3348, 11, '2004-1-12 13:07:00', 105.75, 1020888, 1495)
insert #1 values(3352 ,11, '2004-1-12 14:17:00', 57.84 ,1021214 ,1821)
insert #1 values(3353 ,11, '2004-1-12 14:35:00', 61.26 ,1021276 ,1883)
insert #1 values(3354 ,11, '2004-1-12 14:41:00', 60.69 ,1021299 ,1906)
例如上面 的数据 13点的数据有3条 14点的数据有4条现在要取接近整点的 数据 即最靠近13点 14点的数据 分别为
insert #1 values(3348, 11, '2004-1-12 13:07:00', 105.75, 1020888, 1495)
insert #1 values(3348, 11, '2004-1-12 13:07:00', 105.75, 1020888, 1495)
这2条的数据 取出来 ,其他数据过滤掉
在线等、、、
这种方法属于后期的处理,查询时不作特殊要求另一种方法是写SQL函数,直接查询出最接近某整点时间的记录