ORDER BY 和 ROW_NUMBER() 是确定性的吗? [英] Is ORDER BY and ROW_NUMBER() deterministic?
问题描述
几年来，我不时在几个数据库引擎中使用 SQL，但对理论知识知之甚少，所以我的问题对你们中的一些人来说可能非常菜鸟".但现在这对我来说很重要，所以我不得不问.
I've used SQL in couple databases engines from time to time several years but have little theoretical knowledge so my question could be very "noobish" for some of you. But it become important to me now so I have to ask.
想象具有非唯一列status的表格网址.对于这个问题，假设我们有大量的行，并且每条记录中的状态值都相同.
Imagine table Urls with non unique column status. And for the question assume that we have large amount of rows and status has the same value in every record.
假设我们执行多次查询:
And imagine we execute many times query:
SELECT * FROM Urls ORDER BY status
我们是否每次都得到相同的行顺序?如果我们添加一些新行会发生什么?它会更改顺序还是将新记录附加到结果的末尾?如果我们没有得到相同的订单 - 在什么条件下取决于这个订单?
Do we get every time the same row order or not? If we do what will happen if we add some new rows? Does it change order or new records will be appended to end of the results? And if we don't get the same order - on what conditions depend this order?
ROW_NUMBER() OVER (ORDER BY status) 会返回与上面查询相同的顺序还是基于不同的排序机制?
Do ROW_NUMBER() OVER (ORDER BY status) will return the same order as query above or it is based on different ordering mechanism?
推荐答案
很简单.如果您想要一个可以依赖的排序，那么您需要在 ORDER BY 子句中包含足够多的列，以便所有这些列的组合对于每一行都是唯一的.没有其他保证.
It's very simple. If you want an ordering that you can rely upon, then you need to include enough columns in your ORDER BY clause such that the combination of all of those columns is unique for each row. Nothing else is guaranteed.
对于单个表，您通常可以通过列出有趣"的列进行排序，然后包括主键列来获得所需的内容.由于 PK 本身保证唯一性，因此整个组合也保证唯一地定义排序，例如如果 Urls 表有一个 {Site, Page, Ordinal} 的主键，那么下面会给你一个可靠的结果:
For a single table, you can usually get what you want by listing the columns that are "interesting" to sort by and then including the primary key column(s) afterwards. Since the PK, by itself, guarantees uniqueness, the whole combination is also guaranteed to uniquely define the ordering, e.g. If the Urls table has a primary key of {Site, Page, Ordinal} then the following would give you a dependable result:
SELECT * FROM Urls ORDER BY status, Site, Page, Ordinal
这篇关于ORDER BY 和 ROW_NUMBER() 是确定性的吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
