sql - 是否应将 SQL 排名功能视为 "use with caution"

Question

这个问题源于关于是否在 particular case 中使用 SQL 排名功能的讨论。

任何常见的 RDBMS 都包含一些排名功能，即其查询语言具有 TOP n ... ORDER BY key 、 ROW_NUMBER() OVER (ORDER BY key) 或 ORDER BY key LIMIT n ( overview ) 等元素。

如果您只想显示大量记录中的一小部分，它们在提高性能方面做得很好。但它们也引入了一个主要陷阱:如果 key 不是唯一的，结果是不确定的。考虑以下示例:

users

user_id name
1       John
2       Paul
3       George
4       Ringo

logins

login_id user_id login_date
1        4       2009-08-17
2        1       2009-08-18
3        2       2009-08-19
4        3       2009-08-20

查询应该返回最后登录的人:

SELECT TOP 1 users.*
FROM
  logins JOIN
  users ON logins.user_id = users.user_id
ORDER BY logins.login_date DESC

正如预期的那样返回 George 并且一切看起来都很好。但是随后将一条新记录插入到 logins 表中:

1        4       2009-08-17
2        1       2009-08-18
3        2       2009-08-19
4        3       2009-08-20
5        4       2009-08-20

上面的查询现在返回什么？ Ringo ? George ?你不能说。据我所知，例如MySQL 4.1 返回物理创建的符合条件的第一条记录，即结果将是 George 。但这可能因版本和 DBMS 的不同而异。什么应该被退回？人们可能会说 Ringo，因为他显然是最后登录的，但这是纯粹的解释。在我看来，两者都应该返回，因为您无法从可用数据中明确决定。

所以这个查询符合要求:

SELECT users.*
FROM
  logins JOIN
  users ON
    logins.user_id = users.user_id AND
    logins.login_date = (
      SELECT max(logins.login_date)
      FROM
        logins JOIN
        users ON logins.user_id = users.user_id)

作为替代，一些 DBMS 提供了特殊功能(例如，Microsoft SQL Server 2005 为此目的引入了 TOP n WITH TIES ... ORDER BY key(由 gbn 建议)、 RANK 和 DENSE_RANK)。

如果您搜索 SO 例如 ROW_NUMBER 你会发现许多建议使用排名功能的解决方案，并没有指出可能的问题。

问题:如果提出了包含排名功能的解决方案，应该给出什么建议？

Answer 1

rank 和 row_number 是很棒的函数，应该更自由地使用，IMO。人们只是不知道他们。

话虽如此，您需要确保您的排名依据是独一无二的。为重复项(尤其是日期)制定备份计划。您返回的数据与您输入的数据一样好。

我认为这里的陷阱在查询中完全相同:

select top 2 * from tblA order by date desc

您需要了解您订购的商品，并确保有某种方法可以让您始终有赢家。如果没有，您会得到一个(可能)随机两行的最大日期。

此外，对于记录，SQL Server 不会按行插入的物理顺序存储行。它将记录存储在 8k 页上，并根据表上的聚集索引以最有效的方式对这些页进行排序。因此，在 SQL Server 中绝对不能保证顺序。