求年统计最高效率解决办法问题如下：数据库中数据存储记录编号 日期 水位 经过时间（分钟） 流速（立方米/秒）
1 2009-1-1 0：00 15.1 0 25.1
2 2009-1-1 0：30 15.6 30 26.3
3 2009-1-1 1：00 15.3 30 25.4
。统计结果要求为：
月 1 12 合计
最大水位 最小水位 平均流量 日水量 最大水位 最小水位 平均流量 日水量
日 1
31
合计说明： 要有横向与纵向合计，月份1到12，日1到31，该月31日可显示空
一日从 0：00：00到第二日的 0：00：00
日水量 = （本日本时段流量 + 本日上一时段流量） / 2 * 时段 * 60 + 上一流量累计
流速 = 日水量 / 86400
1 2009-1-1 0：00 15.1 0 25.1
2 2009-1-1 0：30 15.6 30 26.3
3 2009-1-1 1：00 15.3 30 25.4
。统计结果要求为：
月 1 12 合计
最大水位 最小水位 平均流量 日水量 最大水位 最小水位 平均流量 日水量
日 1
31
合计说明： 要有横向与纵向合计，月份1到12，日1到31，该月31日可显示空
一日从 0：00：00到第二日的 0：00：00
日水量 = （本日本时段流量 + 本日上一时段流量） / 2 * 时段 * 60 + 上一流量累计
流速 = 日水量 / 86400
解决方案 »
- sql server 2000中JDBC的驱动类在哪里
- 求TXT动态文件名导入过程,求大侠帮忙！
- 求十进制转为十六进制及十六进制转为十进制的函数?
- 求助~~~~not in 语句的问题
- 请问如何将oracle的的数据导到SQL Server 2000呢
- 新人求知!!!谢谢大家进来帮个忙!
- 求教各位字符串截取的问题
- 关于sqlserver链接数据库的问题，在线急等
- 怎樣將一個mdb文件用SQL語句導入到SQL Server資料庫中(詳情見內)。。。在線等待
- SQL SERVER数据库为何出现自溢？
- sql恢复备份之前，如何获取该bak文件的逻辑文件名？？
- 如何在前台截获存储过程里的错误，如：截断字符串或二进制数据的错误
-------乱
有没有方法使操作两条数据的效率提高呢
from 表 a join 表 b on a.当前记录=b.下一记录(记录+1)
Max_2 numeric(18,2),Min_2 numeric(18,2),Avg_2 numeric(18,2),Water_2 numeric(18,2),
Max_3 numeric(18,2),Min_3 numeric(18,2),Avg_3 numeric(18,2),Water_3 numeric(18,2),
Max_4 numeric(18,2),Min_4 numeric(18,2),Avg_4 numeric(18,2),Water_4 numeric(18,2),
Max_5 numeric(18,2),Min_5 numeric(18,2),Avg_5 numeric(18,2),Water_5 numeric(18,2),
Max_6 numeric(18,2),Min_6 numeric(18,2),Avg_6 numeric(18,2),Water_6 numeric(18,2),
Max_7 numeric(18,2),Min_7 numeric(18,2),Avg_7 numeric(18,2),Water_7 numeric(18,2),
Max_8 numeric(18,2),Min_8 numeric(18,2),Avg_8 numeric(18,2),Water_8 numeric(18,2),
Max_9 numeric(18,2),Min_9 numeric(18,2),Avg_9 numeric(18,2),Water_9 numeric(18,2),
Max_10 numeric(18,2),Min_10 numeric(18,2),Avg_10 numeric(18,2),Water_10 numeric(18,2),
Max_11 numeric(18,2),Min_11 numeric(18,2),Avg_11 numeric(18,2),Water_11 numeric(18,2),
Max_12 numeric(18,2),Min_12 numeric(18,2),Avg_12 numeric(18,2),Water_12 numeric(18,2),
Max_13 numeric(18,2),Min_13 numeric(18,2),Avg_13 numeric(18,2),Water_13 numeric(18,2))
Declare @MonitorFlow_temp table(id int,Period numeric(18,2),MonitorFlow numeric(18,2),AvgFlow numeric(18,2),SumFlow numeric(18,2)) --每日临时表
Declare @month int, --月
@day int, --日
@ishave int, --是否有数据
@max numeric(18,2), --合计最大值
@min numeric(18,2), --合计最小值
@avg numeric(18,2), --合计平均值
@dayWate numeric(18,2), --日水量
@dateStr varchar(10), --日期字符串
@IsDate int, --日期字符串是否合法
@n int, --循环计数器
@count int, --循环次数
@flow_1 numeric(18,2), --上一时段流量（立方米/秒）
@flow_2 numeric(18,2), --当前时段流量（立方米/秒）
@time numeric(18,2) --经过的时段（分钟）
Set @month = 1
Set @day = 1--插入空数据备用
While @month < 32
Begin
Insert into @VelocimeterMonitorTotal values (@month,null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,
null,null,null,null,null,null,null,null,
null,null,null,null)
Set @month = @month + 1
End --根据现有数据生成统计数据
Set @month = 1
While @month < 14
Begin
While @day < 32
Begin
--验证日期字符串是否合法
If @month <> 13
Begin
Set @dateStr = '2009' + '-' + Cast(@month as varchar) + '-' + Cast(@day as varchar)
Set @IsDate = Isdate(@dateStr)
End
Else
Set @IsDate = 1 If @IsDate > 0
Begin
--验证表中该日期是否有数据
If @month <> 13
Select @ishave = Count(*) from VelocimeterMonitorData where Datediff(day,MonitorTime,@dateStr) = 0
Else
Set @ishave = 1 If @ishave > 0
Begin
set @max = 0
set @min = 0
set @avg = 0
set @dayWate = 0 --计算一到十二月的数据
If @month <> 13
begin
select @max = Max(WaterLevel),@min = Min(WaterLevel) from VelocimeterMonitorData where MonitorTime between @dateStr and Dateadd(day,1,Cast(@dateStr as datetime))
Insert into @MonitorFlow_temp select rank() over(order by MonitorTime) as id,Period,Flow,null,null from VelocimeterMonitorData
where MonitorTime between @dateStr and Dateadd(day,1,Cast(@dateStr as datetime)) order by MonitorTime
set @n = 1
select @count = Count(*) from @MonitorFlow_temp
while @n <= @count and @n + 1 <= @count
begin
select @flow_1 = MonitorFlow from @MonitorFlow_temp where id = @n
select @flow_2 = MonitorFlow,@time = Period from @MonitorFlow_temp where id = @n + 1
set @dayWate = @dayWate + (@flow_1 + @flow_2) / 2 * @time * 60
set @n = @n + 1
end set @avg = @dayWate / 86400
delete from @MonitorFlow_temp
end If @month = 1
Update @VelocimeterMonitorTotal set Max_1 = @max,Min_1 = @min,Avg_1 = @avg,Water_1 = @dayWate where [day] = @day
Else If @month = 2
Update @VelocimeterMonitorTotal set Max_2 = @max,Min_2 = @min,Avg_2 = @avg,Water_2 = @dayWate where [day] = @day
Else If @month = 3
Update @VelocimeterMonitorTotal set Max_3 = @max,Min_3 = @min,Avg_3 = @avg,Water_3 = @dayWate where [day] = @day
Else If @month = 4
Update @VelocimeterMonitorTotal set Max_4 = @max,Min_4 = @min,Avg_4 = @avg,Water_4 = @dayWate where [day] = @day
Else If @month = 5
Update @VelocimeterMonitorTotal set Max_5 = @max,Min_5 = @min,Avg_5 = @avg,Water_5 = @dayWate where [day] = @day
Else If @month = 6
Update @VelocimeterMonitorTotal set Max_6 = @max,Min_6 = @min,Avg_6 = @avg,Water_6 = @dayWate where [day] = @day
Else If @month = 7
Update @VelocimeterMonitorTotal set Max_7 = @max,Min_7 = @min,Avg_7 = @avg,Water_7 = @dayWate where [day] = @day
Else If @month = 8
Update @VelocimeterMonitorTotal set Max_8 = @max,Min_8 = @min,Avg_8 = @avg,Water_8 = @dayWate where [day] = @day
Else If @month = 9
Update @VelocimeterMonitorTotal set Max_9 = @max,Min_9 = @min,Avg_9 = @avg,Water_9 = @dayWate where [day] = @day
Else If @month = 10
Update @VelocimeterMonitorTotal set Max_10 = @max,Min_10 = @min,Avg_10 = @avg,Water_10 = @dayWate where [day] = @day
Else If @month = 11
Update @VelocimeterMonitorTotal set Max_11 = @max,Min_11 = @min,Avg_11 = @avg,Water_11 = @dayWate where [day] = @day
Else If @month = 12
Update @VelocimeterMonitorTotal set Max_12 = @max,Min_12 = @min,Avg_12 = @avg,Water_12 = @dayWate where [day] = @day
Else
Begin
Insert into @MonitorFlow_temp select null,Max_1,Min_1,Avg_1,Water_1 from @VelocimeterMonitorTotal where [day] = @day
Insert into @MonitorFlow_temp select null,Max_2,Min_2,Avg_2,Water_2 from @VelocimeterMonitorTotal where [day] = @day
Insert into @MonitorFlow_temp select null,Max_3,Min_3,Avg_3,Water_3 from @VelocimeterMonitorTotal where [day] = @day
Insert into @MonitorFlow_temp select null,Max_4,Min_4,Avg_4,Water_4 from @VelocimeterMonitorTotal where [day] = @day
Insert into @MonitorFlow_temp select null,Max_5,Min_5,Avg_5,Water_5 from @VelocimeterMonitorTotal where [day] = @day
Insert into @MonitorFlow_temp select null,Max_6,Min_6,Avg_6,Water_6 from @VelocimeterMonitorTotal where [day] = @day
Insert into @MonitorFlow_temp select null,Max_7,Min_7,Avg_7,Water_7 from @VelocimeterMonitorTotal where [day] = @day
Insert into @MonitorFlow_temp select null,Max_8,Min_8,Avg_8,Water_8 from @VelocimeterMonitorTotal where [day] = @day
Insert into @MonitorFlow_temp select null,Max_9,Min_9,Avg_9,Water_9 from @VelocimeterMonitorTotal where [day] = @day
Insert into @MonitorFlow_temp select null,Max_10,Min_10,Avg_10,Water_10 from @VelocimeterMonitorTotal where [day] = @day
Insert into @MonitorFlow_temp select null,Max_11,Min_11,Avg_11,Water_11 from @VelocimeterMonitorTotal where [day] = @day
Insert into @MonitorFlow_temp select null,Max_12,Min_12,Avg_12,Water_12 from @VelocimeterMonitorTotal where [day] = @day select @max = Max(Period),@min = Min(MonitorFlow),@avg = Avg(AvgFlow),@dayWate = Sum(SumFlow) from @MonitorFlow_temp
Update @VelocimeterMonitorTotal set Max_13 = @max,Min_13 = @min,Avg_13 = @avg,Water_13 = @dayWate where [day] = @day
delete from @MonitorFlow_temp
End
End
End
Set @day = @day + 1
End
Set @day = 1
Set @month = @month + 1
EndInsert into @VelocimeterMonitorTotal select '合计',Max(Max_1),Min(Min_1),Avg(Avg_1),sum(Water_1),
Max(Max_2),Min(Min_2),Avg(Avg_2),sum(Water_2),
Max(Max_3),Min(Min_3),Avg(Avg_3),sum(Water_3),
Max(Max_4),Min(Min_4),Avg(Avg_4),sum(Water_4),
Max(Max_5),Min(Min_5),Avg(Avg_5),sum(Water_5),
Max(Max_6),Min(Min_6),Avg(Avg_6),sum(Water_6),
Max(Max_7),Min(Min_7),Avg(Avg_7),sum(Water_7),
Max(Max_8),Min(Min_8),Avg(Avg_8),sum(Water_8),
Max(Max_9),Min(Min_9),Avg(Avg_9),sum(Water_9),
Max(Max_10),Min(Min_10),Avg(Avg_10),sum(Water_10),
Max(Max_11),Min(Min_11),Avg(Avg_11),sum(Water_11),
Max(Max_12),Min(Min_12),Avg(Avg_12),sum(Water_12),
Max(Max_13),Min(Min_13),Avg(Avg_13),sum(Water_13) from @VelocimeterMonitorTotalSelect * from @VelocimeterMonitorTotal
<table>
<tr>
<td>月</td>
<td colspan="4">1月</td>
<td colspan="4">2月</td>
<td colspan="4">3月</td>
<td colspan="4">4月</td>
<td colspan="4">5月</td>
<td colspan="4">6月</td>
<td colspan="4">7月</td>
<td colspan="4">8月</td>
<td colspan="4">9月</td>
<td colspan="4">10月</td>
<td colspan="4">11月</td>
<td colspan="4">12月</td>
<td colspan="4">合计</td>
</tr>
<tr>
<td>日</td>
<td>最大值</td>
<td>最小值</td>
<td>平均流量</td>
<td>水量</td>
<td>最大值</td>
<td>最小值</td>
<td>平均流量</td>
<td>水量</td>
<td>最大值</td>
<td>最小值</td>
<td>平均流量</td>
<td>水量</td>
<td>最大值</td>
<td>最小值</td>
<td>平均流量</td>
<td>水量</td>
<td>最大值</td>
<td>最小值</td>
<td>平均流量</td>
<td>水量</td>
<td>最大值</td>
<td>最小值</td>
<td>平均流量</td>
<td>水量</td>
<td>最大值</td>
<td>最小值</td>
<td>平均流量</td>
<td>水量</td>
<td>最大值</td>
<td>最小值</td>
<td>平均流量</td>
<td>水量</td>
<td>最大值</td>
<td>最小值</td>
<td>平均流量</td>
<td>水量</td>
<td>最大值</td>
<td>最小值</td>
<td>平均流量</td>
<td>水量</td>
<td>最大值</td>
<td>最小值</td>
<td>平均流量</td>
<td>水量</td>
<td>最大值</td>
<td>最小值</td>
<td>平均流量</td>
<td>水量</td>
<td>最大值</td>
<td>最小值</td>
<td>平均流量</td>
<td>水量</td>
</tr>
<tr>
<td>
1</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>
2</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>
</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>
31</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
<tr>
<td>
合计</td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
<td></td>
</tr>
</table>每日求和要全年的横向及纵向统计