SQL 如何重新排序sql数据库中的行

How can I reorder rows in sql database

提问by lucius

Is it possible to reorder rows in SQL database? For example; how can I swap the order of 2nd row and 3rd row's values?

是否可以重新排序 SQL 数据库中的行？例如; 如何交换第 2 行和第 3 行值的顺序？

The order of the row is important to me since i need to display the value according to the order.

行的顺序对我很重要，因为我需要根据顺序显示值。

---

---

Thanks for all the answers. But 'Order by' won't work for me.

感谢所有的答案。但是“Order by”对我不起作用。

For example, I put a list of bookmarks in database. I want to display based on the result I get from query. (not in alphabet order). Just when they are inserted.

例如，我在数据库中放置了一个书签列表。我想根据我从查询中得到的结果进行显示。（不按字母顺序排列）。就在它们被插入时。

But user may re-arrange the position of the bookmark (in any way he/she wants). So I can't use 'order by'.

但是用户可以重新排列书签的位置（以他/她想要的任何方式）。所以我不能使用'order by'。

An example is how the bookmark display in the bookmark in firefox. User can switch position easily. How can I mention that in DB?

一个例子是书签如何在firefox 中的书签中显示。用户可以轻松切换位置。我怎么能在数据库中提到这一点？

Thank you.

谢谢你。

回答by Mike Lewis

It sounds like you need another column like "ListOrder". So your table might look like:

听起来您需要像“ListOrder”这样的另一列。所以你的表可能看起来像：

BookMark ListOrder
======== =========
  d        1
  g        2
  b        3
  f        4
  a        5

Then you can "order by" ListOrder.

然后您可以“按”ListOrder 排序。

Select * from MyTable Order By ListOrder

If the user can only move a bookmark one place at a time, you can use integers as the ListOrder, and swap them. For example, if the user wants to move "f" up one row:

如果用户一次只能移动一个书签，您可以使用整数作为 ListOrder，并交换它们。例如，如果用户想将“f”向上移动一行：

Update MyTable
    Set ListOrder=ListOrder+1
        Where ListOrder=(Select ListOrder-1 From MyTable where BookMark='f')

Update MyTable
    Set ListOrder=ListOrder-1
        Where BookMark='f'

If the user can move a bookmark up or down many rows at once, then you need to reorder a segment. For example, if the user wants to move "f" to the top of the list, you need to:

如果用户可以一次将书签向上或向下移动多行，则您需要对段重新排序。例如，如果用户想将“f”移动到列表顶部，则需要：

update MyTable
    Set ListOrder=ListOrder+1
        where ListOrder>=1 -- The New position
            and ListOrder <(Select ListOrder from MyTable where BookMark='f')

 update MyTable
     Set ListOrder=1 -- The New Position
         Where Bookmark='f'

回答by John M Gant

As others have mentioned, it's not a good idea to depend on the physical order of the database table. Relational tables are conceptually more like unordered sets than ordered lists. Assuming a certain physical order may lead to unpredictable results.

正如其他人所提到的，依赖数据库表的物理顺序不是一个好主意。关系表在概念上更像是无序集而不是有序列表。假设某种物理顺序可能会导致不可预测的结果。

Sounds like what you need is a separate column that stores the user's preferred sort order. But you'll still need to do something in your query to display the results in that order.

听起来您需要的是一个单独的列，用于存储用户的首选排序顺序。但是您仍然需要在查询中执行某些操作以按该顺序显示结果。

It is possible to specify the physical order of records in a database by creating a clustered index, but that is not something you'd want to do on an arbitrary user-specified basis. And it may still lead to unexpected results.

可以通过创建聚集索引来指定数据库中记录的物理顺序，但这不是您想要在任意用户指定的基础上执行的操作。它仍然可能导致意想不到的结果。

回答by John Millikin

Use ORDER BYin your SELECTquery. For example, to order by a user's last name, use:

ORDER BY在您的SELECT查询中使用。例如，要按用户的姓氏排序，请使用：

SELECT * FROM User ORDER BY LastName

回答by Ben S

The order of the rows on the actual database should not matter.

实际数据库上的行顺序应该无关紧要。

You should use the ORDER BYclause in your queries to order them as you need.

您应该ORDER BY在查询中使用该子句根据需要对它们进行排序。

回答by Graeme Perrow

Databases can store the data in any way they want. Using the "order by" clause is the onlyway to guarantee an ordering of the data. In your bookmark example, you could have an integer field that indicates the ordering, and then update that field as a user moves things around. Then ORDER BY that column to get things in the right order.

数据库可以以任何他们想要的方式存储数据。使用“order by”子句是保证数据排序的唯一方法。在您的书签示例中，您可以有一个指示排序的整数字段，然后在用户移动事物时更新该字段。然后按该列排序以按正确的顺序排列。

回答by Aaron Morefield

At times like this, I am reminded of a quote from the Matrix: "Do not try and order the database. That's impossible. Instead, only realize the truth... there is no order. Then you will see that it the table that orders itself, it is you who orders the table."

在这种时候，我想起了 Matrix 中的一句话：“不要试图对数据库进行排序。那是不可能的。相反，只有意识到真相......没有顺序。然后你会看到它是表自己点菜，桌子是你点的。”

When working with MySQL through a GUI, there is always a decision to make. If you run something like SELECT * FROM users, MySql will always make a decision to order this by some field. Normally, this will be the primary key.

通过 GUI 使用 MySQL 时，总是需要做出决定。如果您运行类似SELECT * FROM users，MySql 将始终决定按某个字段对其进行排序。通常，这将是主键。

+----------------
| id | name     |
-----------------
| 1  | Brian    |
| 2  | Carl     |
| 3  | Albert   |
-----------------

When you add an ORDER BYcommand to the query, it will make the decision to order by some other field.

当您ORDER BY向查询添加命令时，它将决定按其他字段排序。

For Example Select * From users ORDER BY namewould yield:

例如Select * From users ORDER BY name将产生：

+----------------
| id | name     |
-----------------
| 3  | Albert   |
| 1  | Brian    |
| 2  | Carl     |
-----------------

So to your question, you appear to want to change the default order by which your table displays this information. In order to do that, check to see what your Primary Key field is. For most practical purposes, having a unique identifying natural number tends to do the trick. MySQL has an AUTO_INCREMENTfunction for this. When creating the table, it would look something like field_name int NOT NULL AUTO_INCREMENT.

因此，对于您的问题，您似乎想要更改表格显示此信息的默认顺序。为此，请检查您的主键字段是什么。对于大多数实际目的，拥有唯一的识别自然数往往可以解决问题。MySQL 有一个AUTO_INCREMENT函数。创建表时，它看起来像field_name int NOT NULL AUTO_INCREMENT.

All of this is to say: if you would like to change "the row order", you would need to update this value. However, since the identifier is something that other tables would use to reference your field, this seems a little bit reckless.

所有这一切都是说：如果您想更改“行顺序”，则需要更新此值。但是，由于标识符是其他表用来引用您的字段的东西，这似乎有点鲁莽。

If you for example went: UPDATE table Set id = 1 where id = 2;, this would initially fail, since the id fields would end up being both an identical value and fail the PrimaryKey check (which insists on both uniqueness and having a value set). You could Juggle this by running three update statements in a row:

例如UPDATE table Set id = 1 where id = 2;，如果你去: ，这最初会失败，因为 id 字段最终将成为相同的值并且无法通过 PrimaryKey 检查（它坚持唯一性和设置值）。您可以通过连续运行三个更新语句来解决这个问题：

UPDATE users Set id = 100000000 where id = 1;
UPDATE users Set id = 1 where id = 2;
UPDATE users Set id = 2 where id = 100000000;

This would result in the rows for this table looking like:

这将导致该表的行看起来像：

+----------------
| id | name     |
-----------------
| 1  | Carl     |
| 2  | Brian    |
| 3  | Albert   |
----------------+

Which technically would work to reorder this table, but this is in a bubble. MySQL being a relational database means that any table which was depending on that data to be consistent will now be pointed to the wrong data. For example, I have a table which stores birthdays, referencing the initial user table. It's structure might look like this:

这在技术上可以重新排序这张表，但这是一个泡沫。MySQL 是一个关系数据库意味着任何依赖于该数据保持一致的表现在都将指向错误的数据。例如，我有一个存储生日的表，引用初始用户表。它的结构可能如下所示：

+----------------------------+
| id | user_id  | birthdate  |
+----------------------------+
| 1  | 1        | 1993-01-01 |
| 1  | 2        | 1980-02-03 |
| 1  | 3        | 1955-01-01 |
+----------------------------+

By switching the ID's on the user table, you MUST update the user_id value on the birthdays table. Of course MySQL comes prepared for this: enter "Foreign Key Constraints". As long as you configured all of your foreign key constraints to Cascade Updates, you wouldn't need to manually change the reference to every value you changed.

通过切换用户表上的 ID，您必须更新生日表上的 user_id 值。当然 MySQL 已经为此做好了准备：输入“外键约束”。只要您将所有外键约束配置到级联更新，您就不需要手动更改对您更改的每个值的引用。

These queries would all be a lot of manual work and potentially weaken your data's integrity. If you have fields you would like to rank and reorder regularly, the answer posed by Mike Lewis on this questionwith the "table order" would be a more sensible answer (and if that is the case, then his is the best solution and just disregard this answer).

这些查询都是大量的手动工作，并且可能会削弱数据的完整性。如果您有想要定期排序和重新排序的字段，Mike Lewis用“表格顺序”对这个问题提出的答案将是一个更明智的答案（如果是这种情况，那么他是最好的解决方案，只是忽略这个答案）。

回答by garrow

Add a positioncolumn to your table and store as a simple integer.

position向表中添加一列并存储为一个简单的整数。

If you need to support multiple users or lists, your best bet is to create a bookmarks table, an users table and a table to link them.

如果您需要支持多个用户或列表，最好的办法是创建一个书签表、一个用户表和一个链接它们的表。

• bookmarks: id,url
• users: id,name
• users_bookmarks: user_id, bookmark_id, position, date_created

• 书签： id,url
• 用户： id,name
• 用户书签： user_id, bookmark_id, position, date_created

Assuming date_createdis populated when inserting rows you can get secondary list ordering based on date.

假设date_created在插入行时填充，您可以获得基于日期的二级列表排序。

select bookmark_id from users_bookmarks where user_id = 1 order by position, date_created;

回答by Magne

I have a solution for this that I have used a few times. I keep an extra field "sort_order" in the table, and update this when reordering. I've used this in cases when I have some sort of containers with items, and the order of the items should be editable inside the container. When reordering, I only update the sort_order for the items in the current container, which means not to many (usually in practice only a few) rows have to be updated.

我有一个解决方案，我已经使用了几次。我在表中保留了一个额外的字段“sort_order”，并在重新排序时更新它。当我有某种带有物品的容器时，我会使用它，并且物品的顺序应该在容器内可编辑。重新排序时，我只更新当前容器中项目的 sort_order，这意味着不需要更新很多（通常实际上只有少数）行。

In short, I do the following:

简而言之，我执行以下操作：

1. add a sort_order field to the items table
2. when inserting a new row, I set sort_order=id
3. when reordering (needs id of item to move, and id of item to insert after):
   • select id, sort_order from items where container = ID order by sort_order
   • split the id and sort_order from rows in two arrays
   • remove the id of the item to move from the id-list
   • insert the id of the item to move after the id of the item to insert after
   • merge the list of ids and the list of sort_order into a two dimensional array, as [[id, sort_order], [id2, sort_order], ...]
   • run update item set sort_order=SORT_ORDER where id=ID(executemany) with merged list

1. 将 sort_order 字段添加到 items 表
2. 插入新行时，我设置 sort_order=id
3. 重新排序时（需要移动项目的 id，然后插入项目的 id）：
   • select id, sort_order from items where container = ID order by sort_order
   • 从两个数组中的行拆分 id 和 sort_order
   • 从 id-list 中删除要移动的项目的 id
   • 在要插入的项目的 id 之后插入要移动的项目的 id
   • 将 id 列表和 sort_order 列表合并到一个二维数组中，如 [[id, sort_order], [id2, sort_order], ...]
   • update item set sort_order=SORT_ORDER where id=ID使用合并列表运行（executemany）

(If moving item to another container, after updating "container foreign key" move first or last depending on app.)

（如果将项目移动到另一个容器，在更新“容器外键”后根据应用程序首先或最后移动。）

(If the update involves a large number of items, I do not think this solution is a good approach.)

（如果更新涉及大量项目，我认为这个解决方案不是一个好方法。）

I have made an example using python and mysql on http://wannapy.blogspot.com/2010/11/reorder-rows-in-sql-database.html(copy and try it) along with some extra explanations.

我在http://wannapy.blogspot.com/2010/11/reorder-rows-in-sql-database.html（复制并尝试）上使用 python 和 mysql 做了一个例子以及一些额外的解释。

回答by slipset

I guess a simple order bywould be what you're looking for?

我想一个简单的order by就是你要找的？

select my_column from my_table order by my_order_column;

回答by schooner

As others have stated use an order by.
Never depend on the order data exists in a physical table, always base it of the data you are working with, be it one or more key fields.

正如其他人所说，使用 order by。
永远不要依赖物理表中存在的订单数据，始终以您正在使用的数据为基础，无论是一个还是多个关键字段。