如何使用sql sum函数在不同的mysql表中使用相应的主键添加值？

我在mysql中创建了五个不同的表，其中包含学生记录。每个表基本上都有统一的列名。学生ID、科目1分、科目2分、科目3分、总分。这五个表分别是term1、term2、term3、term4和overall。
我想做的是，当我在term1、term2、term3和term4中插入值时，必须自动计算每列的得分之和、subject1\u得分、subject2\u得分、subject3\u得分、total\u得分，并将其放在overall表的相应列中。下面是我的代码。当我在term1表中插入分数时，我正在尝试更新总体表。它现在正在做的是得到total\ u score列的总和。我希望它取term1中的total \u score的值，并将其添加到total \u score的值中，然后将其作为更新后的当前值放入total中。下面是我的代码。请帮我实现我想做的事。

SELECT SUM( Total_Score ) 
FROM (

SELECT SUM( Total_Score ) AS Total_Score
FROM Term1
UNION ALL 
SELECT SUM( Total_Score ) AS Total_Score
FROM overall
) AS ALIAS;

你目前的设计不是标准化的，这意味着你需要付出很多努力才能达到你想要的。
对于每个term表，创建before和after insert触发器、before和after update触发器以及after delete触发器，其中解析所有表以计算overall.total。类似这样的东西（我只为term1创建了insert和update触发器，以减少答案大小）

drop trigger if exists trigger_after_term1_insert;
drop trigger if exists trigger_before_term1_insert;
drop trigger if exists trigger_after_term1_update;
drop trigger if exists trigger_before_term1_update;

delimiter $$
create trigger trigger_term1_before_insert before insert on term1
for each row
begin
  insert into debug_table(msg) values (concat('before insert:',new.subject1_score + new.subject2_score + new.subject3_score));
  set new.total = new.subject1_score + new.subject2_score + new.subject3_score; #you may need coalesce here
end $$

delimiter $$
create trigger trigger_term1_before_update before update on term1
for each row
begin
  insert into debug_table(msg) values (concat('before update:',new.subject1_score + new.subject2_score + new.subject3_score));
  set new.total = new.subject1_score + new.subject2_score + new.subject3_score; #you may need coalesce here
end $$

delimiter $$
create trigger trigger_term1_after_insert after insert on term1
for each row
begin
 insert into debug_table(msg) values ('after insert');
 if not exists (select 1 from overall o where o.studentid = new.studentid) then
         insert into debug_table(msg) values ('after insert Not exists');
        insert into overall(studentid,total)
                select studentid,sum(tot) 
                 from
                 (
                 select new.studentid,new.subject1_score + new.subject2_score + new.subject3_score tot
                 union all
                 select studentid,subject1_score + subject2_score + subject3_score from term2 #amend as required
                 union all
                 select studentid,subject1_score + subject2_score + subject3_score from term3 #amend as required
                 union all
                 select studentid,subject1_score + subject2_score + subject3_score from term4 #amend as required
                 ) b
                 group by studentid;
       end if;
end $$

delimiter $$
create trigger trigger_term1_after_update after update on term1
for each row
begin
 insert into debug_table(msg) values ('after update');
 if exists (select 1 from overall o where o.studentid = new.studentid) then
    insert into debug_table(msg) values ('after update exists');
    update overall 
           join (select studentid,sum(tot) tot
                 from
                 (
                 select new.studentid,new.subject1_score + new.subject2_score + new.subject3_score tot
                 union all
                 select studentid,subject1_score + subject2_score + subject3_score from term2 #amend as required
                 union all
                 select studentid,subject1_score + subject2_score + subject3_score from term3 #amend as required
                 union all
                 select studentid,subject1_score + subject2_score + subject3_score from term4 #amend as required
                 ) a
                 group by studentid
                 ) s
                 on s.studentid = overall.studentid
         set overall.total = s.tot;
      else
         insert into debug_table(msg) values ('after update Not exists');
        insert into overall(studentid,total)
                select studentid,sum(tot) 
                 from
                 (
                 select new.studentid,new.subject1_score + new.subject2_score + new.subject3_score tot
                 union all
                 select studentid,subject1_score + subject2_score + subject3_score from term2 #amend as required
                 union all
                 select studentid,subject1_score + subject2_score + subject3_score from term3 #amend as required
                 union all
                 select studentid,subject1_score + subject2_score + subject3_score from term4 #amend as required
                 ) b
                 group by studentid;
       end if;

end $$
delimiter ;

注1）debug\表用于协助调试2）studentid是所有表的主键
如果您的mysql版本支持生成的列，那么您可以省去before触发器，但是您确实不应该存储易于计算的数据。
如果你的数据库是正常的，那么所有这些代码消失。考虑一个表studentid，term，subject，subject\u分数

如何使用sql sum函数在不同的mysql表中使用相应的主键添加值？

1条答案

相关问题

热门标签

最新问答