mysql求一个表中数据的平均值 最大值 最小值的 存储过程!注意是存储过程!表名叫score。
答案:2 悬赏:80 手机版
解决时间 2021-02-06 01:02
- 提问者网友:刺鸟
- 2021-02-05 14:57
表中数据有学生名 语数英的成绩
最佳答案
- 五星知识达人网友:woshuo
- 2021-02-05 15:48
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;
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;
全部回答
- 1楼网友:几近狂妄
- 2021-02-05 16:26
no
我要举报
如以上问答信息为低俗、色情、不良、暴力、侵权、涉及违法等信息,可以点下面链接进行举报!
大家都在看
推荐资讯