如何将一行的多列的总和存储在同一个表的同一行的另一列上? [英] How to store a row's multiple column's sum on that same table's same row's another column?

问题描述

CREATE TABLE `student`

(
  `student_id` int(5) NOT NULL AUTO_INCREMENT,
  `student_first_name` varchar(30) not null,
  `student_lase_name` varchar(30) not null,
  `student_roll_no` int(5) not null,
  `student_class` int(2) not null,
  PRIMARY KEY (`student_id`)
);

CREATE TABLE `result_sheet`
(
  `student_id` int(5),
  `student_first_name` varchar(30),
  `student_lase_name` varchar(30),
  `student_roll_no` int(5),
  `student_class` int(2),
  `mid_1_english` int(2),
  `mid_2_english` int(2),
  `mid_1_mathematics` int(2),
  `mid_2_mathematics` int(2),
  `mid_english` int(2),
  `mid_mathematics` int(2),
  `semester_final_english` int(2),
  `semester_final_mathematics` int(2),
  `total_english` int(2),
  `total_mathematics` int(2),
  `total` int(3),
   foreign key (student_id) references student(student_id) on delete cascade
);

delimiter $$
create trigger result_sheet_insert_trigger

after insert on student
for each row 
begin 
insert into result_sheet(student_id, student_first_name, student_lase_name, student_roll_no, student_class, mid_1_english, mid_2_english, mid_1_mathematics, mid_2_mathematics, mid_english, mid_mathematics, semester_final_english, semester_final_mathematics, total_english, total_mathematics, total)
values (new.student_id, new.student_first_name, new.student_lase_name, new.student_roll_no, new.student_class, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0);

end$$
delimiter ;

我想存储 mid_1_english & 的平均值mid_2_english 每行的中英文.我该怎么做?假设我有 12 个作为 mid_1_english 和 20 个作为 mid_2_english.然后我想在 result_sheet 表中存储 16 作为中英文.

I want to store the avg of mid_1_english & mid_2_english on mid english for each row. How am I supposed to do that? Suppose I have 12 as mid_1_english and 20 as mid_2_english. Then I want to store 16 as mid english on result_sheet table.

推荐答案

试试这个:

UPDATE result_sheet SET mid_english = (mid_1_english + mid_2_english) / 2

这篇关于如何将一行的多列的总和存储在同一个表的同一行的另一列上?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！