在SQL中存储排序顺序的最好方法是什么？ [英] What's the best way to store sort order in SQL?

问题描述

顶部的家伙希望排序顺序在我们的应用程序中可自定义。所以我有一个表，有效地定义数据类型。什么是存储我们的排序顺序的最好方法。如果我刚刚创建了一个名为Order的新列或某事，每次我更新一行的顺序，我想我必须更新每一行的顺序，以确保后代。有更好的方法吗？

The guys at the top want sort order to be customizable in our app. So I have a table that effectively defines the data type. What is the best way to store our sort order. If I just created a new column called 'Order' or something, every time I updated the order of one row I imagine I would have to update the order of every row to ensure posterity. Is there a better way to do it?

推荐答案

到目前为止，没有答案触及了自定义排序顺序的真正问题这是当两个不同的人想要相同的记录排序不同时会发生什么。

None of the answers so far have touched on the real problem with custom sort order and that is what happens when two different people want the same records sorted differently.

如果您需要自定义排序顺序，则需要相关的表来存储，而不是附加字段。该表将具有userid，数据的recordId和记录的排序顺序。这样乔的史密斯可以有一个订单和萨利琼斯另一个相同的数据。现在您有新记录添加到数据集的问题。您将它们放在排序顺序的开头或结束，或者您需要该人为他们设置顺序，然后才能将其添加到集合中。这实际上是一个非常复杂的标志，通常不值得花费的时间量来实现，因为几乎没有一个使用该系统一旦它在现场（我的意思是，我真的想通过一百个记录，并标记个别的顺序的每一个？）。现在，它获得了所有记录的顺序的复杂的干扰（当然，在下一次运行查询时需要改变，因为会有新的记录。）这是一个非常痛苦的有限无限的过程。

If you need a custom sort order, you need a related table to store it in, not an additional field. The table would have the userid, the recordId of the data and the sort order for the record. That way Joe Smith can have one order and Sally Jones another for the same data. Now you have the problem of new records being added to the data set. Do you put them at the beginning of the sort order or the end or do you require the person to set an order for them before they can be added to the set. This is in actuality a very complex porblem that is generally not worth the amount of time it takes to implement becasue almost no onne ever uses that system once it's inplace (I mean do I really want to go through a hundred records and mark the individual order of each one?). Now it gets complicated interms of saving the order of all the records (which will of course require changes the next time the query is run since there will be new records.) This is very painful process of limited untility.

我曾经在一个写作应用的建议中这样做过，因为我们需要能够按照我们认为对客户最令人印象深刻的顺序来排序propoasal上的部件和任务。即使那样，我们不得不建立一个默认订单，这样他们只需要移动两到三个他们真正想要显示的东西，而不是订购10,000个单独的零件。

I did this once in a proposal writing appliction because we needed to be able to sort the parts and tasks on the propoasal in the order we thought woudl be most impressive to the customer. Even then, we had to institute a default order, so that they only neede to move around teh two or three things they really wanted to show up first instead of ordering 10,000 individual parts.

更好的选择，如果你可以让他们买，它是允许他们按列（desc或asc）排序数据。通常，用户界面可以设计为，如果您单击列标题，它将使用该列的数据。这是相对简单的做和满足大多数需求的自定义排序。

A better choice if you can get them to buy off on it, is to allow them to sort the data by columns (desc or asc). Usually the user interface can be designed so that if you click on a column header, it will resort the data by that column. This is relatively straightforward to do and meets most needs for custom ordering.

您真的需要与管理层讨论这个要求，并获得他们希望如何工作的细节，我想要定制排序。这通常是人们认为他们想要的东西之一，但不真正使用。

You really need to discuss this requirement with management and get details of how they want it to work beyond, I want custom ordering. This is often one of those things people think they want, but don't really use.

这篇关于在SQL中存储排序顺序的最好方法是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！