现要对历史数据库数据做整编(水位数据)历史数据库字段:时间time,水位 da 水位数据一般情况一天有两个最大值,两个最小值,也有可能是两个最大值 一个最小值、 用sql如何求出 这些最大值最小值???TM DA1
2005-12-17 08:00:00.000 -0.30
2005-12-17 08:15:00.000 -0.35
2005-12-17 08:30:00.000 -0.35
2005-12-17 08:45:00.000 -0.39
2005-12-17 09:00:00.000 -0.40
2005-12-17 09:15:00.000 -0.43
2005-12-17 09:30:00.000 -0.44
2005-12-17 09:45:00.000 -0.44
2005-12-17 10:00:00.000 -0.49
2005-12-17 10:15:00.000 -0.44
2005-12-17 10:30:00.000 -0.44
2005-12-17 10:45:00.000 -0.41
2005-12-17 11:00:00.000 -0.39
2005-12-17 11:15:00.000 -0.36
2005-12-17 11:30:00.000 -0.32
2005-12-17 11:45:00.000 -0.25
2005-12-17 12:00:00.000 -0.22
2005-12-17 12:15:00.000 -0.17
2005-12-17 12:30:00.000 -0.14
2005-12-17 12:45:00.000 -0.12
2005-12-17 13:00:00.000 -0.12
2005-12-17 13:15:00.000 -0.08
2005-12-17 13:30:00.000 -0.08
2005-12-17 13:45:00.000 -0.06
2005-12-17 14:00:00.000 -0.05
2005-12-17 14:15:00.000 -0.05
2005-12-17 14:30:00.000 -0.04
2005-12-17 14:45:00.000 -0.04
2005-12-17 15:00:00.000 -0.04
2005-12-17 15:15:00.000 -0.03
2005-12-17 15:30:00.000 -0.06
2005-12-17 15:45:00.000 -0.07
2005-12-17 16:00:00.000 -0.07
2005-12-17 16:15:00.000 -0.08
2005-12-17 16:30:00.000 -0.09
2005-12-17 16:45:00.000 -0.11
2005-12-17 17:00:00.000 -0.13
2005-12-17 17:15:00.000 -0.13
2005-12-17 17:30:00.000 -0.13
2005-12-17 17:45:00.000 -0.12
2005-12-17 18:00:00.000 -0.11
2005-12-17 18:15:00.000 -0.11
2005-12-17 18:30:00.000 -0.10
2005-12-17 18:45:00.000 -0.09
2005-12-17 19:00:00.000 -0.07
2005-12-17 19:15:00.000 -0.07
2005-12-17 19:30:00.000 -0.05
2005-12-17 19:45:00.000 -0.05
2005-12-17 20:00:00.000 0.02
2005-12-17 20:15:00.000 0.08
2005-12-17 20:30:00.000 0.12
2005-12-17 20:45:00.000 0.22
2005-12-17 21:00:00.000 0.28
2005-12-17 21:15:00.000 0.39
2005-12-17 21:30:00.000 0.45
2005-12-17 21:45:00.000 0.57
2005-12-17 22:00:00.000 0.60
2005-12-17 22:15:00.000 0.69
2005-12-17 22:30:00.000 0.72
2005-12-17 22:45:00.000 0.78
2005-12-17 23:00:00.000 0.81
2005-12-17 23:15:00.000 0.84
2005-12-17 23:30:00.000 0.88
2005-12-17 23:45:00.000 0.91
2005-12-17 08:00:00.000 -0.30
2005-12-17 08:15:00.000 -0.35
2005-12-17 08:30:00.000 -0.35
2005-12-17 08:45:00.000 -0.39
2005-12-17 09:00:00.000 -0.40
2005-12-17 09:15:00.000 -0.43
2005-12-17 09:30:00.000 -0.44
2005-12-17 09:45:00.000 -0.44
2005-12-17 10:00:00.000 -0.49
2005-12-17 10:15:00.000 -0.44
2005-12-17 10:30:00.000 -0.44
2005-12-17 10:45:00.000 -0.41
2005-12-17 11:00:00.000 -0.39
2005-12-17 11:15:00.000 -0.36
2005-12-17 11:30:00.000 -0.32
2005-12-17 11:45:00.000 -0.25
2005-12-17 12:00:00.000 -0.22
2005-12-17 12:15:00.000 -0.17
2005-12-17 12:30:00.000 -0.14
2005-12-17 12:45:00.000 -0.12
2005-12-17 13:00:00.000 -0.12
2005-12-17 13:15:00.000 -0.08
2005-12-17 13:30:00.000 -0.08
2005-12-17 13:45:00.000 -0.06
2005-12-17 14:00:00.000 -0.05
2005-12-17 14:15:00.000 -0.05
2005-12-17 14:30:00.000 -0.04
2005-12-17 14:45:00.000 -0.04
2005-12-17 15:00:00.000 -0.04
2005-12-17 15:15:00.000 -0.03
2005-12-17 15:30:00.000 -0.06
2005-12-17 15:45:00.000 -0.07
2005-12-17 16:00:00.000 -0.07
2005-12-17 16:15:00.000 -0.08
2005-12-17 16:30:00.000 -0.09
2005-12-17 16:45:00.000 -0.11
2005-12-17 17:00:00.000 -0.13
2005-12-17 17:15:00.000 -0.13
2005-12-17 17:30:00.000 -0.13
2005-12-17 17:45:00.000 -0.12
2005-12-17 18:00:00.000 -0.11
2005-12-17 18:15:00.000 -0.11
2005-12-17 18:30:00.000 -0.10
2005-12-17 18:45:00.000 -0.09
2005-12-17 19:00:00.000 -0.07
2005-12-17 19:15:00.000 -0.07
2005-12-17 19:30:00.000 -0.05
2005-12-17 19:45:00.000 -0.05
2005-12-17 20:00:00.000 0.02
2005-12-17 20:15:00.000 0.08
2005-12-17 20:30:00.000 0.12
2005-12-17 20:45:00.000 0.22
2005-12-17 21:00:00.000 0.28
2005-12-17 21:15:00.000 0.39
2005-12-17 21:30:00.000 0.45
2005-12-17 21:45:00.000 0.57
2005-12-17 22:00:00.000 0.60
2005-12-17 22:15:00.000 0.69
2005-12-17 22:30:00.000 0.72
2005-12-17 22:45:00.000 0.78
2005-12-17 23:00:00.000 0.81
2005-12-17 23:15:00.000 0.84
2005-12-17 23:30:00.000 0.88
2005-12-17 23:45:00.000 0.91
关键是现在数据库数据太大 几千万条数据 一个查询 几分钟都没反应 这有没什么好的解决方法?
where t1.id = t2.id + 1
and t2.id = t3.id + 1
and t2.da < t1.da
and t2.da < t3.da类似上面的写法,
不过这个语句执行非常慢
如果是,可以通过判断某条的da比上条和下条都大或都小来判断这个可以用sql能实现么? 后台我到知道怎么处理
而且比用代码做效率高
设计一个表,按时间分字段,
例如:
tmpTB
date time1 time2 time3 time4 ......//对应每15分钟一次的时间
2005-12-17 0.30 0.35 0.35 0.40 ......
2005-12-18 0.40 0.45 0.50 0.55 ......
... ...
select a.*,
(select max(da1) from your_table y1 where y1.time=a.time and y1.da1<a.max_da and y1.da1>a.min_da),
(select min(da1) from your_table y1 where y1.time=a.time and y1.da1<a.max_da and y1.da1>a.min_da)
from (select time,max(da1) max_da,min(da1) min_da from your_table group by time) a
最小两个:select distinct top 2 * from dbo.LotteryRecore where datediff(day,time,getdate())=0 order by da asc