SQL Server 视图中是否支持 ORDER BY？
在这个答案中使用 SqlServer 视图有哪些缺点？，hyprsleepy 建议 SQL Server 中的视图不支持 ORDER BY 子句。
谁能解释为什么会出现这种情况？
编辑：感谢您的回答，但我不确定这就是问题所在。我知道你不能将 Order By 子句添加到视图中，我对此没有问题，你只需在调用视图时将其添加到 SELECT 语句中，但我对另一个问题的印象是在视图的 SELECT 语句中使用 Order By 子句不会每次都给出正确的结果。
In this answer to What are the downsides of using SqlServer Views?, hyprsleepy suggests the ORDER BY clause is not honoured in views in SQL Server.
Could anyone explain why this is the case?
Edit: Thanks for the answers, but I'm not sure thats the issue. I know you cant add an Order By Clause to a view and I dont have a problem with that, you just add it to the SELECT statement when calling the view, but my impression from the other question was that using an Order By Clause in a SELECT statement on a view will not give the correct results every time.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（5）
SQL Server开发人员假设任何集合操作都可能更改记录的顺序，因此在中间集合定义中使用ORDER BY没有意义，它仅在以下情况下有意义：最后陈述。这些视图可用于连接或其他使其订单无效的操作。
由于您无法单独使用视图，即您不编写
vMyView，因此您宁愿编写SELECT * FROM vMyView，尽管该视图是一个SELECT本身，如果您需要订单，您也可以（并且应该）将ORDER BY子句附加到SELECT语句中。这是一个相当合理的假设，事实上它使代码更加清晰。
SQL Serverdevelopers assume that any set operation may change the order of the records so there is no point to useORDER BYin the intermediate set definitions and it only makes sense in the final statements.The views may be used in joins or other operations which invalidates their orders.
Since you cannot use the view by itself, i. e. you don't write
vMyView, you rather writeSELECT * FROM vMyView, despite the fact that the view is aSELECTper se, you can (and should) append theORDER BYclause to theSELECTstatement as well if you need an order.This is quite a sane assumption and in fact it makes the code more clear.
看看 在 SQL Server 2005 和 SQL Server 2008 中创建排序视图
有一种方法可以做到这一点，但不支持，只需在从视图中选择时发出命令即可
Take a look at Create a sorted view in SQL Server 2005 and SQL Server 2008
There is a way to do it but it is not supported, just issue an order by when selecting from the view
在您的视图定义中包括 TOP 100 PERCENT 作为解决方法。然后您可以使用 ORDER BY。
编辑或如 SQLMenace 指出的 TOP 99.99 PERCENT。
In your view definition include TOP 100 PERCENT as a workaround. You can then use ORDER BY.
EDIT or as SQLMenace pointed out TOP 99.99 PERCENT.
来自维基百科：
From wikipedia:
您不能在视图中放置 ORDER BY 子句。确切的错误是：
“ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效，除非还指定了 TOP 或 FOR XML。”
You can't put an ORDER BY clause in your views. The exact error is:
"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."