使用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:

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

Msg 102, Level 15, State 1, Procedure TopUsers, Line 11
Incorrect 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.

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