如何按升序排列表格的行并同时保存表格? [英] How to arrange rows of a table in ascending order and to save the table simultaneously?

问题描述

我想通过从现有表中按升序排列行来创建一个新表.我尝试使用的代码是:

I want to create a new table by arranging the rows in ascending order from the existing table. The code I am trying to use is:

SELECT * 
   INTO new_table
FROM existing_table
ORDER BY col1, col2

然而，新表没有显示任何行的升序排列.谁能告诉我这段代码有什么问题?

However new table does not show any ascending arrangement of rows. Can anybody tell me what is wrong with this code ?

推荐答案

表中的行是无序的，所以谈论行是有序的没有意义.而且，查询的结果集是无序的，除非您使用 order by 子句.

Rows in a table are unordered, so it doesn't make sense to talk about rows being in order. And, the result set from a query is unordered, unless you use an order by clause.

也就是说，您可以以有序的方式将行放入表中，从而获得相同的效果.这是一个方法.

That said, you can have the same effect of placing rows into a table in an ordered fashion. Here is a method.

select top 0 *
into new_table
from existing_table;

alter table new_table add new_table_id int identity(1, 1);

insert into new_table(<list of columns without new_table_id>)
    SELECT <list of columns without new_table_id>
    INTO new_table
    FROM existing_table
    ORDER BY col1, col2;

id 列保证按正确顺序排列.在实践中，行似乎是按顺序插入的.严格来说，虽然id的值排序正确，但不能保证插入顺序.

The id column is guaranteed to be in the correct order. In practice, it seems that the rows will be inserted in order. Strictly speaking, the insert order is not guaranteed although the values of the id are ordered correctly.

正如评论中提到的，你也可以这样做:

As mentioned in the comment, you can also do:

alter table new_table add new_table_id int identity(1, 1) not null primary key;

您可以这样做，因为表是空的.作为主键，数据应该按顺序插入.

You can do this because the table is empty. As a primary key, the data should be inserted in order.

不过，请注意.查询:

select *
from new_table;

不保证结果的顺序.插入到表中的顺序是什么没有区别.您不能仅仅因为行的排序方式就依赖于特定顺序的结果.例如，在多线程环境中，结果通常在理论上或实践中都不是有序的.

does not guarantee the ordering of the results. It makes no difference what the insert order is into the table. You cannot depend on the results being in a particular order just because the rows were ordered that way. For instance, in a multi-threaded environment, the results will generally not be in order, either in theory or in practice.

这篇关于如何按升序排列表格的行并同时保存表格?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！