时间戳

首页 > 解决方案 > 更新触发器中的平均分数？

sql-server - 更新触发器中的平均分数？

问题描述

我有学生表和分数表和科目表。学生表有“student_id”和“avg_score”。主题表有“subject_name”和“subject_id”。分数有 'subject_id'、'student_id' 和 'score' 作为十进制。我写了这个触发器来计算平均分数。如何获得每个学生的每个学科名称的准确分数并计算平均分数？

CREATE TRIGGER caculateAVG ON dbo.dbo_score
AFTER INSERT,UPDATE,DELETE AS
BEGIN
  DECLARE @Math DECIMAL;
  DECLARE @Eng DECIMAL;
  DECLARE @Liter DECIMAL;

  UPDATE x
  SET x.avgScore = (@Eng + @Liter + @Math)/3
  FROM dbo.dbo_student x
  INNER JOIN dbo.dbo_score c ON c.student_id = x.student_id
  INNER JOIN dbo.dbo_subject j ON j.subject_id = c.subject_id

  SET @Math = (SELECT s.score FROM dbo.dbo_score s INNER JOIN dbo.dbo_subject j ON 
  j.subject_id = s.subject_id WHERE UPPER(j.subject_name) LIKE 'math')

  SET @Eng = (SELECT s.score FROM dbo.dbo_score s INNER JOIN dbo.dbo_subject j ON 
  j.subject_id = s.subject_id WHERE UPPER(j.subject_name) LIKE 'eng')

  SET @Liter = (SELECT s.score FROM dbo.dbo_score s INNER JOIN dbo.dbo_subject j 
  ON j.subject_id = s.subject_id WHERE UPPER(j.subject_name) LIKE 'literature')
END

标签： sql-servertsqltriggers

解决方案

在使用 SQL Server（或任何相关数据库）时，您希望尝试使用基于集合的逻辑来解决所有问题。Inserted因此，我们可以执行您在单个查询中尝试的更新，如下所示，我希望您希望将更新限制为仅使用和Deleted伪表受更新影响的学生。

UPDATE x
  SET x.avgScore = (
    (SELECT sum(s.score) FROM dbo.dbo_score s INNER JOIN dbo.dbo_subject j ON j.subject_id = s.subject_id WHERE j.subject_name = 'eng')
    + (SELECT sum(s.score) FROM dbo.dbo_score s INNER JOIN dbo.dbo_subject j ON j.subject_id = s.subject_id WHERE j.subject_name = 'literature')
    + (SELECT sum(s.score) FROM dbo.dbo_score s INNER JOIN dbo.dbo_subject j ON j.subject_id = s.subject_id WHERE j.subject_name = 'math')
  )/3
FROM dbo.dbo_student x
where x.student_id in (select student_id from Inserted union all select student_id from Deleted)

like如果您在搜索词中未使用通配符，也无需使用。并且没有必要使用upper，因为 SQL Server 通常不区分大小写（并且您将其与小写单词进行比较，如果它区分大小写则会失败）。

推荐阅读