使用ORDER BY子句创建视图

本文介绍了使用ORDER BY子句创建视图的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我尝试使用 ORDER BY 子句创建视图。我在SQL Server 2012 SP1上成功创建它，但是当我尝试在SQL Server 2008 R2上重新创建它，我得到这个错误：

I'm trying to create a view with an ORDER BY clause. I have create it successfully on SQL Server 2012 SP1, but when I try to re-create it on SQL Server 2008 R2, I get this error:

Msg 102，Level 15，State 1，Procedure TopUsers，Line 11 OFFSET附近的语法不正确。

Msg 102, Level 15, State 1, Procedure TopUsers, Line 11Incorrect syntax near 'OFFSET'.

$ b b

创建视图的代码是

The code to create the view is

CREATE View [dbo].[TopUsersTest] as select u.[DisplayName] , sum(a.AnswerMark) as Marks From Users_Questions us inner join [dbo].[Users] u on u.[UserID] = us.[UserID] inner join [dbo].[Answers] a on a.[AnswerID] = us.[AnswerID] group by [DisplayName] order by Marks desc OFFSET 0 ROWS

============ =========

=====================

这是图表的屏幕截图

This is a screen shot of the diagram

我想返回用户 DisplayName code> UserTotalMarks 并订购此结果desc，因此最大结果的用户位于顶部。

I wish to return users' DisplayName and the UserTotalMarks and order this result desc, so the user with the biggest result with be on the top.

推荐答案

我不知道你认为这个 ORDER BY 是否完成？即使您以合法方式在视图中放置 ORDER BY （例如添加顶部 clause），如果你只是从视图中选择，eg SELECT * FROM dbo.TopUsersTest; 没有 ORDER BY 子句，SQL Server可以自由返回最多有效的方式，这不一定匹配您期望的顺序。这是因为 ORDER BY 是重载的，因为它试图提供两个目的：对结果进行排序并指定要包含在中的行。在这种情况下， TOP 总是胜利（虽然根据选择的索引扫描数据，您可能会观察到您的顺序正常工作 - 但这只是一个巧合） 。

I'm not sure what you think this ORDER BY is accomplishing? Even if you do put ORDER BY in the view in a legal way (e.g. by adding a TOP clause), if you just select from the view, e.g. SELECT * FROM dbo.TopUsersTest; without an ORDER BY clause, SQL Server is free to return the rows in the most efficient way, which won't necessarily match the order you expect. This is because ORDER BY is overloaded, in that it tries to serve two purposes: to sort the results and to dictate which rows to include in TOP. In this case, TOP always wins (though depending on the index chosen to scan the data, you might observe that your order is working as expected - but this is just a coincidence).

为了完成你想要的，你需要将 ORDER BY

In order to accomplish what you want, you need to add your ORDER BY clause to the queries that pull data from the view, not to the code of the view itself.

因此，您的视图代码应该是：

So your view code should just be:

CREATE VIEW [dbo].[TopUsersTest] AS SELECT u.[DisplayName], SUM(a.AnswerMark) AS Marks FROM dbo.Users_Questions AS uq INNER JOIN [dbo].[Users] AS u ON u.[UserID] = us.[UserID] INNER JOIN [dbo].[Answers] AS a ON a.[AnswerID] = uq.[AnswerID] GROUP BY u.[DisplayName];

ORDER BY

为了说明，使用AdventureWorks2012，下面是一个例子：

To illustrate, using AdventureWorks2012, here is an example:

CREATE VIEW dbo.SillyView AS SELECT TOP 100 PERCENT SalesOrderID, OrderDate, CustomerID , AccountNumber, TotalDue FROM Sales.SalesOrderHeader ORDER BY CustomerID; GO SELECT SalesOrderID, OrderDate, CustomerID, AccountNumber, TotalDue FROM dbo.SillyView;

结果：

SalesOrderID OrderDate CustomerID AccountNumber TotalDue ------------ ---------- ---------- -------------- ---------- 43659 2005-07-01 29825 10-4020-000676 23153.2339 43660 2005-07-01 29672 10-4020-000117 1457.3288 43661 2005-07-01 29734 10-4020-000442 36865.8012 43662 2005-07-01 29994 10-4020-000227 32474.9324 43663 2005-07-01 29565 10-4020-000510 472.3108

从执行计划中可以看到 TOP 和 ORDER BY 已被SQL Server绝对忽略和优化：

And you can see from the execution plan that the TOP and ORDER BY have been absolutely ignored and optimized away by SQL Server:

没有 TOP 运算符，没有排序。

There is no TOP operator at all, and no sort. SQL Server has optimized them away completely.

现在，如果您将视图更改为 ORDER BY SalesID

Now, if you change the view to say ORDER BY SalesID, you will then just happen to get the ordering that the view states, but only - as mentioned before - by coincidence.

但是如果你改变你的外部查询来执行 ORDER BY 您需要：

But if you change your outer query to perform the ORDER BY you wanted:

SELECT SalesOrderID, OrderDate, CustomerID, AccountNumber, TotalDue FROM dbo.SillyView ORDER BY CustomerID;

您可以按照自己想要的方式订购结果：

You get the results ordered the way you want:

SalesOrderID OrderDate CustomerID AccountNumber TotalDue ------------ ---------- ---------- -------------- ---------- 43793 2005-07-22 11000 10-4030-011000 3756.989 51522 2007-07-22 11000 10-4030-011000 2587.8769 57418 2007-11-04 11000 10-4030-011000 2770.2682 51493 2007-07-20 11001 10-4030-011001 2674.0227 43767 2005-07-18 11001 10-4030-011001 3729.364

计划仍然优化了 TOP / ORDER BY 在视图中，但添加了一个排序结果按 CustomerID 排序：

And the plan still has optimized away the TOP/ORDER BY in the view, but a sort is added (at no small cost, mind you) to present the results ordered by CustomerID:

所以，故事的道德，不要在视图中放置ORDER BY。在引用它们的查询中加入ORDER BY。如果排序很昂贵，您可以考虑添加/更改索引以支持它。

So, moral of the story, do not put ORDER BY in views. Put ORDER BY in the queries that reference them. And if the sorting is expensive, you might consider adding/changing an index to support it.