select 学号,sum(成绩)/count(1) 平均成绩 from 成绩表 group by 学号
CREATE OR REPLACE PROCEDURE insert_t_tongji IS BEGIN FOR i IN SELECT 学号,AVG(成绩) 平均成绩 FROM 成绩表 GROUP BY 学号 LOOP INSERT INTO t_tongji VALUES(i.学号,i.平均成绩); END LOOP; END;
上面写错了 FOR i IN (SELECT 学号,AVG(成绩) 平均成绩 FROM 成绩表 GROUP BY 学号) LOOP加括号
create or replace procedure P as begin insert into t_tongji select 学号,avg(成绩) from 成绩表 group by 学号; end P;
create table grade( stuid varchar2(20), class varchar2(20), grade number(4,2) ); --创建统计表 create table t_tongji( stuid varchar2(20), grade_avg number(4,2) ); --插入测试数据 begin for varA in 1..10 loop for varB in 21..26 loop insert into grade values(varA,varB||'班',dbms_random.value*100); end loop; end loop; end; / --存储过程 create or replace procedure grade_tongji as begin for varA in (select stuid,avg(grade) grade_avg from grade group by stuid) loop insert into t_tongji values(varA.stuid,varA.grade_avg); end loop; end; / --以上步骤经过测试没问题,楼主可以试下
BEGIN
FOR i IN SELECT 学号,AVG(成绩) 平均成绩 FROM 成绩表 GROUP BY 学号 LOOP
INSERT INTO t_tongji VALUES(i.学号,i.平均成绩);
END LOOP;
END;
FOR i IN (SELECT 学号,AVG(成绩) 平均成绩 FROM 成绩表 GROUP BY 学号) LOOP加括号
as
begin
insert into t_tongji select 学号,avg(成绩) from 成绩表 group by 学号;
end P;
stuid varchar2(20),
class varchar2(20),
grade number(4,2)
);
--创建统计表
create table t_tongji(
stuid varchar2(20),
grade_avg number(4,2)
);
--插入测试数据
begin
for varA in 1..10
loop
for varB in 21..26
loop
insert into grade values(varA,varB||'班',dbms_random.value*100);
end loop;
end loop;
end;
/
--存储过程
create or replace procedure grade_tongji as
begin
for varA in (select stuid,avg(grade) grade_avg from grade group by stuid)
loop
insert into t_tongji values(varA.stuid,varA.grade_avg);
end loop;
end;
/
--以上步骤经过测试没问题,楼主可以试下