在SQL服务器中，TOP返回意外的行。 [英] In SQL server, TOP is returning unexpected rows.

问题描述

我有一张员工表。它有EmployeeName（varchar（100））和Salary字段（deciaml）。所有记录都按排序顺序排列，我试图将记录从第11个员工提取到20个员工。我知道有多种简单的方法来获取这些数据，我也用3-4种不同的方式完成了这些工作。但是，我试过的方法之一是不工作，它重新打击意外的数据。以下是我正在使用的脚本：



数据脚本：

Hi, I'm having a table of Employees. It has EmployeeName (varchar(100)) and Salary field (deciaml). All the records are in sorted order, and I was trying to fetch records from 11th Employee to 20 Employee. I know there are multiple and simple ways to get this data and I've also done it with 3-4 different ways. But, one of the way that I tried is not working and it retruns unexpected data. Below are the scripts that I'm using:

Data script:

declare @counter int = 1
while(@counter <= 50)
begin
	insert into Employee (name,salary) 	select CONCAT('name - ',@counter),100*@counter;
	set @counter += 1;
end

Select * from Employee

查询以获取记录：

Query to fetch records:

select top(10) * from (
Select Top(20)* from Employee
) as t order by t.id desc

说明：首先，我使用TOP（20）获得前20名员工的记录。然后，我试图按降序排序20员工的数据，然后获得TOP（10）员工。它应该从11-20（以任何顺序取消或取消）返回我的员工。但是，它从50-41返回员工。为什么？



我的尝试：



我试过了在Google上，所有人都在谈论视图和TOP，但在我的情况下没有View。

Explanation: First, I'm getting records of first 20 employees, using TOP(20). Then, I'm trying to order the data of 20 Employee in descending order and then get TOP(10) employees. It supposed ot return me Employee from 11-20 (in any order descinduing or ascinding). But, it returns employees from 50-41. Why?

What I have tried:

I've tried on google, all are talking about Views and TOP but in my case there is no View.

推荐答案

可能是因为你没有指定ORDER BY条件idder set：所以SQL可以自由地以任何合适的顺序返回行。然后取出前20个，按ID排序，然后选择结果的前10个。

如果要使用TOP提取特定行，则始终需要指定ORDER BY或你无法真正控制返回的行。

Probably, because you don't specify an ORDER BY condition on the idder set: so SQL is at liberty to return rows in any order it finds suitable. You then take the top 20 of those, order them by ID, and select the top 10 of that result.
If you want to extract specific rows with TOP, you always need to specify an ORDER BY or you have no real control over what rows are returned.

select top(10) e.*
from (Select Top(20) e.*
      from Employee e
      order by e.id 
     ) e
order by e.id desc;

这是返回预期结果的最终查询。感谢@OriginalGriff指出查询中的问题。

This is the final query that returns expected result. Thanks to @OriginalGriff for pointing out the problem in query.

这篇关于在SQL服务器中，TOP返回意外的行。的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！