思路:在存储过程中,拼接sql语句。根据起始时间和结束时间,while循环每次加一段时间。
DROP PROCEDURE IF EXISTS `get_avg`;
DELIMITER ;;
CREATE DEFINER=`root`@`%` PROCEDURE `get_avg`(in iStartTime datetime, in iEndTime datetime)
BEGIN
declare vSql varchar(10240) default '';
declare vNextTime datetime;
while(iStartTime < iEndTime) do
-- 每次加一个小时
set vNextTime = date_add(iStartTime,interval 3600 second);
-- 单引号是特殊字符,要表示单引号,使用 '' 进行转义
set vSql = concat(vSql,'union select 100, avg(`value`) from t1 where time between ''',iStartTime,''' and ''', vNextTime,''' ');
set iStartTime = vNextTime;
end while;
set vSql = substring(vSql,7);
-- 看看拼接的字符串是否正确
-- select vSql;
set @vSql = vSql;
prepare stmt from @vSql;
execute stmt;
deallocate prepare stmt;
END
;;
DELIMITER ;我只列出SQL查询语句,具体在VB怎么用就你自己搞定了,因为你提供的信息有限,没法给你提供完整的VB代码:
select max([序号]) as 最大序号, avg([平均值]) as 总平均值 from 表名
要得到转动惯量 列中所有数据的平均值,你可以自己举一反三啊,一定要嚼烂了你才会吃啊:
select max([序号]) as 最大序号, avg([平均值]) as 总平均值, avg([转动惯量]) as 转动惯量平均值 from 表名
或者分别求三个值也行:
select max([序号]) as 最大序号 from 表名
select avg([平均值]) as 总平均值 from 表名
select avg([转动惯量]) as 转动惯量平均值 from 表名
Set rs1 = dbOpenRecordset("select avg(转动惯量) from 项目")
然后rs1(0)的值就是“项目”这个表中所有“转动惯量”的平均值了create or replace procedure (ave out number,themax out number,themin varchar2)is
Mathmax number;
Mathmin number;
chinesemax number;
chinesemin number;
englishmax number;
englishmin number;
totle number;
sum1 number;
sum2 number;
sum3 number;
count1 number;
begin
select Math into Mathmax from score where 1=1 order by desc;
select Math into Mathmin from score where 1=1 order by asc;
select chinese into chinesemin from score where 1=1 order by asc;
select chinese into chinesemax from score where 1=1 order by desc;
select english into englishmax from score where 1=1 order by desc;
select english into englishmin from score where 1=1 order by asc;
---the max
if Mathmax>chinesemax then
themax:=Mathmax;
else
themax:=chinesemax;
end if;
if themax > englishmax then
themax:=themax;
else
themax:=englishmax;
end if;
---the min
if Mathmin>chinesemin then
themin:=chinesemin;
else
themin:=Mathmin;
end if;
if themin > englishmin then
themin:=englishmin;
else
themin:=themin;
end if;
--the average
select count(1) into count1 from score;
select sum(math) into sum1 from score;
select sum(chinese) into sum2 from score;
select sum(english) into sum3 from score;
ave:=(sum1+sum2+sum3)/count1;
end;
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)