日期:2014-05-18  浏览次数:20567 次

求解一个很难的问题,高分求解!!!!急!急!!!!!!
有数据表   sensor_fivemin_data   表的字段如下:

sensor_code(decimal)
fivemin_time(datetime)
fivemin_max(float)  
fivemin_min(float)
fivemin_ave(float)
其中:sensor_cide字段下有多个值         //从1到20
            fivemin_time字段下也有多个值   //是随着时间(5分钟一次)的增加而增加
想根据此表生成一个小时表,   用     视图     来实现。小时表的字段和sensor_fivemin_data   的字段基本一

样, sensor_code(decimal)        
hour_time(datetime) //时间只精确到小时,分钟、秒都为0
hour_max(float)   //是由fivemin_max提取出来的
hour_min(float) //是由fivemin_min提取出来的
hour_ave(float) //是由fivemin_ave提取出来的
我说的有点乱,希望高手能理解我说的意思
请教各位高手,这个该怎么做?

------解决方案--------------------

CREATE VIEW view_sensor_hour_data AS
select sensor_code,convert(char(10),sensor_fivemin_data,120) as hour_time,
max(fivemin_max) as hour_max,min(fivemin_min) as hour_min,
avg(fivemin_ave) as hour_ave
FROM sensor_fivemin_data
GROUP BY sensor_code,convert(char(10),sensor_fivemin_data,120)
------解决方案--------------------
create table sensor_fivemin_data(
sensor_code int,
fivemin_time datetime,
fivemin_max float,
fivemin_min float,
fivemin_ave float
)

insert sensor_fivemin_data select 1, '2007-1-1 00:35:00 ', 3.11, 1.31, 2.71
union all select 2, '2007-1-1 00:35:00 ', 3.12, 1.32, 2.72
union all select 3, '2007-1-1 00:35:00 ', 3.19, 1.39, 2.79

union all select 1, '2007-1-1 00:40:00 ', 3.31, 1.21, 2.41
union all select 2, '2007-1-1 00:40:00 ', 3.32, 1.22, 2.42
union all select 3, '2007-1-1 00:40:00 ', 3.39, 1.29, 2.49

union all select 1, '2007-1-1 01:25:00 ', 3.11, 1.31, 2.71
union all select 2, '2007-1-1 01:25:00 ', 3.12, 1.32, 2.72
union all select 3, '2007-1-1 01:25:00 ', 3.19, 1.39, 2.79

union all select 1, '2007-1-1 01:30:00 ', 3.31, 1.21, 2.41
union all select 2, '2007-1-1 01:30:00 ', 3.32, 1.22, 2.42
union all select 3, '2007-1-1 01:30:00 ', 3.39, 1.29, 2.49


select sensor_code, hour_time=convert(char(13), fivemin_time, 120),
hour_max=cast(max(fivemin_max) as decimal(10,2)),
hour_min=cast(max(fivemin_min)as decimal(10, 2)),
hour_ave=cast(max(fivemin_ave) as decimal(10, 2))
from sensor_fivemin_data
group by sensor_code, convert(char(13), fivemin_time, 120)

--result
sensor_code hour_time hour_max hour_min hour_ave
----------- ------------- ------------ ------------ ------------
1 2007-01-01 00 3.31 1.31 2.71
2 2007-01-01 00 3.32 1.32 2.72
3 2007-01-01 00 3.39 1.39 2.79
1 2007-01-01 01 3.31 1.31 2.71
2 2007-01-01 01 3.32 1.32 2.72
3 2007-01-01 01 3.39 1.39 2.79

(6 row(s) affected)