mysql 平均值和总和

发布网友 发布时间：2022-04-23 17:34

我来回答

共1个回答

热心网友 时间：2022-04-07 16:32

用视图呗..用表得用触发器.或者存储...

create table student_class
(
id int(4) not null auto_increment,
name varchar(20) not null,
class varchar(20) not null,
years int(4) not null,
score double not null,
primary key (id)
)
engine = innodb default charset = utf8;
insert into student_class values (null,'张三','高三一班',2012,180.00);
insert into student_class values (null,'张三','高三一班',2013,88.00);
insert into student_class values (null,'张三','高三一班',2014,181.00);
insert into student_class values (null,'李四','高三一班',2012,102.00);
insert into student_class values (null,'李四','高三一班',2013,183.00);
insert into student_class values (null,'李四','高三一班',2014,184.00);
insert into student_class values (null,'王五','高三二班',2012,185.00);
insert into student_class values (null,'王五','高三二班',2013,186.00);
insert into student_class values (null,'王五','高三二班',2014,181.00);
insert into student_class values (null,'赵六','高三二班',2012,183.00);
insert into student_class values (null,'赵六','高三二班',2013,184.00);
insert into student_class values (null,'赵六','高三二班',2014,185.00);
create view student_years as select class,years,sum(score),avg(score) from student_class group by class,years;
mysql> select * from student_years;
+--------------+-------+------------+------------+
| class | years | sum(score) | avg(score) |
+--------------+-------+------------+------------+
| 高三一班 | 2012 | 282 | 141 |
| 高三一班 | 2013 | 271 | 135.5 |
| 高三一班 | 2014 | 365 | 182.5 |
| 高三二班 | 2012 | 368 | 184 |
| 高三二班 | 2013 | 370 | 185 |
| 高三二班 | 2014 | 366 | 183 |
+--------------+-------+------------+------------+