MySQL-触发更新排名 [英] MySQL- Trigger updating ranking

问题描述

我正在为某些NFL球队创建一个带有 Team表的数据库，并且已经为他们分配了一个排名（在NFL中排名），该属性称为排名。

I'm creating a DB with "Team" table for some NFL teams, and I have assigned them all a ranking (standing in NFL), the attribute is called "Ranking".

我想创建一个触发器，以便如果排名得到更新，则所有其他触发器也会适当更新。

I want to create a trigger such that if the ranking is updated, all of the others are updated appropriately.

但是，我不知道找出一种遍历表格的方法。例如，假设排名5的团队升至排名3，我如何使排名3变为4，然后使4变为5？

However, I can't figure out a way to loop through the table. For example, assume team at rank 5 moves up to rank 3, how do I get the rank 3 to become 4, and then 4 to be 5?

更多信息，请随时询问，我会尽快提供。

If you need more info, feel free to ask, I'll provide it ASAP.

推荐答案

如果您知道要更新的团队的ID （将其命名为42）以及旧的和新的排名（旧的：5，新的：3），那么就不太难了：

If you know the id of the team to update (lets call it 42) and the old and new rank (old: 5, new: 3), then it's not too hard:

UPDATE Team SET rank=rank+1 WHERE rank BETWEEN 3 AND 5;
UPDATE Team SET rank=3 WHERE id=42;

这篇关于MySQL-触发更新排名的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！