使用 ORDER BY 子句创建视图 [英] Create a view with ORDER BY clause

问题描述

我正在尝试使用 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:

消息 102，级别 15，状态 1，过程 TopUsers，第 11 行
不正确'OFFSET' 附近的语法.

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

创建视图的代码是

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

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

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

这是图表的屏幕截图

我希望返回用户的DisplayName 和UserTotalMarks 并将此结果降序排列，以便获得最大结果的用户排在最前面.

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 有什么作用?即使您do以合法的方式将ORDER BY放在视图中(例如通过添加TOP子句)，如果您只是从查看，例如SELECT * FROM dbo.TopUsersTest; 如果没有 ORDER BY 子句，SQL Server 可以以最有效的方式自由地返回行，这不一定与您的顺序匹配预计.这是因为 ORDER BY 被重载了，因为它试图服务于两个目的:对结果进行排序和指定要包含在 TOP 中的行.在这种情况下，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 没有意义，因此甚至不应包含在内.

The ORDER BY is meaningless so should not even be included.

为了说明，使用 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 运算符，也没有排序.SQL Server 已经完全优化了它们.

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.

这篇关于使用 ORDER BY 子句创建视图的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！