求解一个很难的有关问题,高分求解!
求解一个很难的问题,高分求解!!!!急!急!!!!!!
有数据表 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)
有数据表 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)