更改将一个表中的值与一个更新查询相乘

Lets say students can be in queue for multiple different courses. Each courses have its own queue with students.

让我们假设学生们可以排队上多个不同的课程。每门课程都有自己的学生队列。

The table got following: StudentID(int), CourseID(int), Accepted(bool) and QueueIndex(int). Every studentID got different QueueIndex's for each courseID.

表格如下:StudentID(int)、课程id (int)、accept (bool)和QueueIndex(int)。每个学生的课件都有不同的队列索引。

How do I update every QueueIndex for all queues once a student is e.g. removed where he belonged?

我如何更新所有队列的每个QueueIndex，例如一个学生被从他所属的位置删除?

E.g. the student A got QueueIndex 5 in Course A and QueueIndex 3 in Course B. I want every other student above that QueueIndex and CourseID get a decreased --value, so the next student in Course A who had QueueIndex 6 will have QueueIndex 5 instead etc.

例如，学生A在课程A中得到了QueueIndex 5，在课程b中得到了QueueIndex 3。我想让在这个QueueIndex和Course id之上的所有其他学生都得到一个-value值的下降，所以在课程A中有QueueIndex 6的下一个学生将会得到QueueIndex 5。

UPDATE [Register] 
SET QueueIndex = QueueIndex -1
WHERE QueueIndex > 0
AND (decrease QueueIndex for every CourseID where that specific StudentID left the queue);

Is this possible with a single query?

这对单个查询是否可行?

3 个解决方案

#1

Sounds like one student will be in there multiple times if they are in multiple courses? If they left one course then...

听起来像一个学生在不同的课程中会有很多次?如果他们离开了一道菜……

UPDATE table SET queue = queue - 1 WHERE queue > queuenumber /*the student that was removed queue number*/ AND course = courseid /*the course id of the course that the student was removed from */

I'm on my phone so sorry if there are typos. Just let me know.

如果有拼写错误，我很抱歉。只是让我知道。

#2

Why do you need to update the queueindex at all?

为什么需要更新queueindex呢?

SELECT StudentID, CourseID, 
    (SELECT Count(StudentID) FROM Register R 
    WHERE R.CourseID = Register.CourseID AND 
    R.QueueIndex <= Register.QueueIndex) AS QueueNr
FROM Register

This would give you the queueindex in order no matter who was removed.

这将为您提供queueindex，不管谁被删除。

#3

You can use trigger to do the update when a DELETE statement is fired. Below code may help you:

在触发删除语句时，可以使用触发器进行更新。下面的代码可以帮助您:

CREATE TRIGGER tr_Delete_Update_Register ON dbo.Register
    FOR DELETE
AS
    BEGIN
        UPDATE  Register
        SET     QueueIndex = QueueIndex - 1
        WHERE   QueueIndex > 0
                AND QueueIndex > ( SELECT   QueueIndex
                                   FROM     deleted
                                 )
    END

This trigger will update all the QueueIndex only for QueueIndex greater than the deleted QueueIndex. Otherwise, if you want to update all QueueIndex just comment out the AND condition from WHERE Clause.

此触发器将只更新所有QueueIndex，以便队列索引大于已删除的QueueIndex。否则，如果您想更新所有QueueIndex，只需注释掉WHERE子句的条件和条件。

#1