顶层条款，按条款排序 [英] Top Clause, order by clause

问题描述

--Table Creation Statement
create table tbl_Employee
(
EmployeeID int primary key,
Name Varchar(3),
salary int 
);

--Insert Statements

insert into tbl_Employee values(1001,'ABC',10000);
insert into tbl_Employee values(1002,'EFG',10000);
insert into tbl_Employee values(1003,'MGM',20000);
insert into tbl_Employee values(1004,'MSM',30000);
insert into tbl_Employee values(1005,'XYZ',30000);
insert into tbl_Employee values(1006,'RST',40000);

--Query
select top(4) * from tbl_Employee order by salary

--Result Set
EmployeeID	Name	salary
1002	        EFG	10000
1001	        ABC	10000
1003	        MGM	20000
1005	        XYZ	30000

关于此处生成的结果集，我有两个问题:-
i)为什么名称''EFG''在''ABC''
之前 ii)为什么在生成的结果集中Name ''XYZ''优先于Name ''MSM''，尽管两者的工资相同.

我正在使用SQL Server 2008 R2.
在此先感谢

I have two questions regarding the result set produced here:-
i)Why is the Name ''EFG'' coming before ''ABC''
ii)Why is the Name ''XYZ'' is given preference over the Name ''MSM'' in the result set produced although both are having the same salary.

I am using SQL Server 2008 R2.
Thanks in advance

推荐答案

之所以会发生，是因为您未按名称"进行订购.您不能依赖订单，除非您按必填列专门进行订购.使用

It''s happening because you are not ordering by ''Name''. You cannot depend on the order unless you specifically order by the required column. Use

select top(4) * from tbl_Employee order by salary, Name

逗号按列分隔顺序.这将按薪金顺序对结果进行排序，&然后(对于相同的薪水)按名称

Comma separate the order by columns. This will order the results by Salary first, & then (for identical salaries) by Name

> 1)因为您仅在ORDER BY子句中指定了salary:如果未指定其他字段，则SQL可以自由返回它们以它希望的任何顺序.要更改此设置，请按重要性顺序指定该字段:

1) Because you only specified salary in your ORDER BY clause: if you do not specify other fields, then SQL is at liberty to return them in any order it wishes. To change this, specify the field in the order of importance:

SELECT TOP(4) * FROM tbl_Employee ORDER BY salary, EmployeeID

2)参见1).

2) See 1).

这取决于为该查询创建的执行计划，并且由于您没有为Name 列指定任何顺序，因此，告诉查询优化器您不介意Name 列，它可以用它做任何想要的事情.

正如我在这种情况下考虑的那样，您的查询是:

This is something that is depend on the execution plan that was created for this query and since you didn''t specified any order for the Name column you told to the query optimizer that you don''t mind about Name column and it can do whatever it wants with it.

And as I considered in this case your query :

select top(4) * from tbl_Employee order by salary

将产生与这一结果相同的结果:

will produce the same result as this one :

select top(4) * from tbl_Employee order by salary , name desc

但是我们不能得出结论，它在查询的末尾添加了 name desc.也许这一次它决定这样做，但是由于我们没有提及Name 列顺序的任何内容，因此它可以做任何想要的事情.

要查看结果而不会给您带来任何问题，请运行以下命令:

But we can not conclude that it adds name desc to the end of the query. Maybe this time it decided to do that but since we didn''t mention anything about Name column order it can do whatever it wants.

To see the results without raising any question for you, run this one :

select top(4) * from tbl_Employee order by salary , name asc

希望对您有所帮助.

Hope it helps.

这篇关于顶层条款，按条款排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！