如何使用更新触发器或动态更新功能 [英] How to use update trigger or dynamic update function

问题描述

我是一个新的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...

这篇关于如何使用更新触发器或动态更新功能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！