SQL 服务器 2008 : Unexpected results using SELECT TOP

Question

谁能解释我看到的 SQL Server 2008 行为？

给定一个简单的表定义:

Column          Type          Nullability
id (PK)         int           not null
author_id       int           null
title           varchar(255)  not null
body            varchar(MAX)  not null
type            varchar(255)  null
comments_count  int           null

“SELECT * FROM posts ORDER BY comments_count DESC”返回:

id  author_id  title                               comments_count
--- ---------- ----------------------------------- --------------
1   1          Welcome to the weblog               2             
2   1          So I was thinking                   1             
3   0          I don't have any comments           0             
4   1          sti comments                        0             
5   1          sti me                              0             
6   1          habtm sti test                      0             
7   2          eager loading with OR'd conditions  0             

但是“SELECT TOP 3 * FROM posts ORDER BY comments_count DESC”返回:

id  author_id  title                               comments_count
--- ---------- ----------------------------------- --------------
1   1          Welcome to the weblog               2             
2   1          So I was thinking                   1             
4   1          sti comments                        0             

而不是像我期望的那样返回行 ID 1、2 和 3。

谢谢尼克

Answer 1

当按 comments_count 列排序时，id 为 3 和 4 的行并列。标准 SQL 表示，如果出现平局或者您未指定任何 ORDER BY 子句，则由供应商实现来确定排序顺序。

如果你想要某个顺序，我建议你指定顺序:

SELECT TOP 3 * FROM posts ORDER BY comments_count DESC, id ASC