是SQL order by子句保证稳定（通过标准） [英] Is SQL order by clause guaranteed to be stable ( by Standards)

问题描述

我使用以下查询来查询在两列上有顺序的数据库。

I am using following query to query DB which have order by on two columns.

从表1中选择a，b，c由asc，b asc;

SELECT a,b,c from Table1 Order By a asc, b asc;

我的问题是，排序是否保证稳定（通过标准）虽然它没有什么意义，它不是稳定的，但我问这是因为我在网上阅读

My question is, Is the sorting guaranteed to be stable (by Standards) or not. Though it doesn't make any sense for it to be, not be stable, But i ask this because I read on net that

标准不会阻止使用稳定排序，但它也不需要它
。

The standard does not prevent the use of a stable sort, but it also does not require it.

推荐答案

排序不保证是稳定的。我认为SQL Server 文档有一个很好的解释，如何实现稳定排序：

The sort is not guaranteed to be stable. I think the SQL Server documentation has a good explanation of how to achieve a stable sort:

要在使用OFFSET和
FETCH的查询请求之间实现稳定的结果，必须满足以下条件：
查询使用的基础数据不能更改。也就是说，通过查询触摸的行
不被更新，或者来自查询
的所有页面请求在单个事务中使用快照或
可序列化事务隔离来执行。有关这些
事务隔离级别的更多信息，请参阅SET TRANSACTION ISOLATION LEVEL
（Transact-SQL）。 ORDER BY子句包含保证为唯一的列的列或组合
。

To achieve stable results between query requests using OFFSET and FETCH, the following conditions must be met: The underlying data that is used by the query must not change. That is, either the rows touched by the query are not updated or all requests for pages from the query are executed in a single transaction using either snapshot or serializable transaction isolation. For more information about these transaction isolation levels, see SET TRANSACTION ISOLATION LEVEL (Transact-SQL). The ORDER BY clause contains a column or combination of columns that are guaranteed to be unique.

最简单的方法一个排序是不是稳定的是回到表的定义。表在SQL中是固有无序的。因此，没有为了稳定性而退回的顺序。

The simplest way to understand that a sort is not stable is to go back to the definition of a table. Tables are inherently unordered in SQL. So, there is no ordering to fall back on for "stability".

作为第二个考虑，排序可以并行实现。在大多数并行排序中，公共密钥被放在一起，没有关于其原始顺序的信息（除非在排序键中显式或隐式地实现）。

As a second consideration, the sorting may be implemented in parallel. In most parallel sorts, common keys are brought together with no information about their original order (unless that is implemented in the sort key, either explicitly or implicitly).

这篇关于是SQL order by子句保证稳定（通过标准）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！