SQL查询服务器在SELECT查询中按TOP 100 PERCENT排序

这是我的查询:

select * from   
(select name,empID,salary,[deducted salary] = salary-7000 from tblEmpDetails   
order by Joined_Date) TmpTbl where [decucted salary] > 50000

这是错误的:

The ORDER BY clause is invalid in views, inline functions, derived tables,   
subqueries, and common table expressions, unless TOP or FOR XML is also specified.

然后我将查询更改为:

select * from   
(select TOP 100 PERCENT name,empID,salary,[deducted salary] = salary-7000   
from tblEmpDetails order by Joined_Date) TmpTbl where [decucted salary] > 50000 

现在它工作正常.
我的查询是使用TOP 100是正确的方法或任何其他工作有这个？

1> RichardTheKi..：

---

您可能希望在外部查询中使用ORDER BY,例如

select name,empID,salary,[deducted salary] from   
(select name,empID,salary,[deducted salary] = salary-7000, Joined_Date
   from tblEmpDetails   
) TmpTbl where [decucted salary] > 50000
order by Joined_Date

编辑 - 是的,您需要在内部查询中包含Joined_Date,以便在外部查询中对其进行排序,以及仅显示所需的4列而不是*.

但您也可以在一个级别编写查询

  select name,empID,salary,[deducted salary] = salary-7000
    from tblEmpDetails
   where salary-7000 > 50000
order by Joined_Date

请注意,salary-7000虽然查询中重复只是由SQL Server 评估一次,因为它足够聪明,可以使用它两次.

---

ORDER BY在带有TOP的子查询中有效,因为您可能希望根据特定顺序查看几个或多个通常只有一行,例如`select u.name,(从Log中选择前1个Log.IPAddress,其中Log.UserID = u .UserID order by Log.time desc)LastKnownIP来自用户u`.它与TOP一起工作以实现有意义的事情.但是没有TOP的ORDER BY没有实现任何功能,因为演示顺序不受子查询控制,只能在最外层的查询中控制.使其语法无效有助于使其更清晰.