我是一个新的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 desci 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...
更多推荐
如何使用更新触发器或动态更新功能
发布评论