如何使用更新触发器或动态更新功能

本文介绍了如何使用更新触发器或动态更新功能的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我是一个新的sql aspirant并且被赋予了一个任务并被它击中了 i有一个如下表格 --------- -------------------------------------------------- ---- 没有| studentID |主题|标记 1 001数学90 2 001历史88 3 002数学99 4 002历史88 5 002电脑97 我想要这样的输出 ------------------ -------------------- 没有|学生姓名|总计|排名 1 james 178 2 2 john 227 1 无论科目数量多少，根据寄存器编号总计应动态添加，并且应该生成等级 什么我试过了：

i am a new sql aspirant and was given with a assignment and struck with it i have a table like the following --------------------------------------------------------------- no | studentID | subject | marks 1 001 maths 90 2 001 history 88 3 002 maths 99 4 002 history 88 5 002 computer 97 and i want the output like this -------------------------------------- no | student name | total | rank 1 james 178 2 2 john 227 1 irrespective of the number of subjects , based on the register number the total should be added dynamically and rank should be produced What I have tried:

SELECT m.StudentID as ID, s.SubjectName as subjects, m.MarkRate as marks FROM Mark m INNER JOIN Subject s on m.SubjectID = m.SubjectID ORDER BY m.StudentID, s.SubjectName ---------------------------------- SELECT X.StudentID, X.StudentName, ROWNUMBER() OVER ( ORDER BY X.TotalMark desc) as Rank FROM ( SELECT m.StudentID, s.StudentName, sum(m.MarkRate) TotalMark FROM Mark m INNER JOIN Student s on s.StudentID = m.StudentID GROUP BY m.StudentID, s.StudentName ) X ORDER BY X.TotalMark desc

i treid这个但无法动态添加值 我也需要使用UPDATE关键字也可以使用它作为触发器。

i treid this but unable to add values dynamically also i need to use the UPDATE keyword can use it as trigger too.

推荐答案

这似乎是一个功课所以我赢了不要提供全部细节。但是为了让你开始，你应该同时使用 - SUM（Transact-SQL） - SQL Server | Microsoft Docs [ ^ ] - RANK（Transact-SQL） - SQL Server Microsoft Docs [ ^ ] 聚合函数。看一下文档中的示例。它们与你的需求非常相似。 关于更新和触发器的问题是什么，我不太明白那部分...... This seems like a homework so I won't be giving full details. But to get you started, you should use both - SUM (Transact-SQL) - SQL Server | Microsoft Docs[^] - RANK (Transact-SQL) - SQL Server | Microsoft Docs[^] aggregation functions. Have a look at the examples in the documentation. They are quite similar to your needs. What comes to the question about update and a trigger, I didn't quite understand that part...