如何在两行之间插入行并在数据库中给予优先级？

问题描述

我在数据库表中有一堆消息。
i想要按照优先级发送这些消息，所以我将优先级列添加到消息表。

但是如果我想插入cram在两条消息之间发送消息，并将此优先级提供给新消息？

我应该在此消息下更新所有消息优先级。

所以请给我完整的数据库表设计，以支持优先级更新。

解决方案

使用 float 列为优先级而不是 int 。

然后，在另外两个，将两个消息的优先级值的平均值分配为新消息的优先级。 （例如，在优先级为2和3之间的消息之间插入一封邮件，将其分配给优先级为2.5）。

通过这样做，您不必更新任何其他消息的优先级，您可以继续在这些之间进行平均/插入等，直到达到浮点数的十进制精度极限（这将需要一段时间，特别是如果原始优先级值往往较小）。

或者，在ORDER BY中的Priority之后添加另一列。在最简单的情况下，使用名为ShowAfter的位列，其默认值为0.当插入cram消息时，给予与要查看的消息相同的优先级，但[ShowAfter]值为1。

i have a stack of messages in database table.i want to send these messages by their priority so i added "Priority" column to "messages" table.

but what if i want to insert "cram" message between two messages and give the previous priority to this new message?.

Should i update all messages priority under this message.

So please give me the perfect design for my database table to support priority update.

解决方案

Use a float column for Priority rather than an int.

Then, to insert a message between two others, assign the average of the two messages' Priority values as the new message's Priority. (E.g., to insert a cram message between a messages with Priority 2 and 3, assign it a Priority of 2.5).

By doing this, you don't have to update any other messages' priority, and you can continue to average/insert between those, etc. until you bump up against the decimal accuracy limits of a float (which will take awhile, especially if the raw Priority values tend to be small).

Or, add another column after Priority in the ORDER BY. In the simplest case, use bit column called "ShowAfter" with a default value of 0. When you insert a cram message, give it the same Priority as the message you want to see it after, but a [ShowAfter] value of 1.

这篇关于如何在两行之间插入行并在数据库中给予优先级？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！