我正在尝试使用ORDER BY子句创建 View 。我已经在SQL Server 2012 SP1上成功创建了它,但是当我尝试在SQL Server 2008 R2上重新创建它时,出现此错误:



创建 View 的代码是

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

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

这是该图的屏幕截图

我希望返回用户的DisplayNameUserTotalMarks并对该结果desc进行排序,因此结果最大的用户位于顶部。

最佳答案

我不确定您认为ORDER BY完成了什么工作?即使您确实以合法方式将ORDER BY放置在 View 中(例如,通过添加TOP子句),即使您只是从 View 中进行选择,例如SELECT * FROM dbo.TopUsersTest;没有ORDER BY子句,SQL Server可以自由地以最有效的方式返回行,这不一定与您期望的顺序匹配。这是因为ORDER BY重载,因为它试图满足两个目的:对结果进行排序,并指定要包含在TOP中的行。在这种情况下,TOP始终会获胜(尽管取决于所选择的扫描数据的索引,您可能会注意到您的订单正在按预期方式工作-但这只是一个巧合)。

为了完成所需的操作,您需要将ORDER BY子句添加到从 View 中提取数据的查询中,而不是 View 本身的代码中。

因此,您的 View 代码应为:

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,下面是一个示例:
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

从执行计划中可以看到,SQL Server完全忽略了TOPORDER BY并对其进行了优化:

根本没有TOP运算符,也没有排序。 SQL Server完全优化了它们。

现在,如果您将 View 更改为ORDER BY SalesID,那么您将碰巧获得 View 所陈述的顺序,但只是(如前所述)巧合。

但是,如果您更改外部查询以执行所需的ORDER BY,则:
SELECT SalesOrderID, OrderDate, CustomerID, AccountNumber, TotalDue
FROM dbo.SillyView
ORDER BY CustomerID;

您可以按照想要的方式对结果进行排序:
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

并且该计划仍然优化了 View 中的TOP / ORDER BY,但是添加了一种排序方式(请注意,费用不菲),以呈现CustomerID排序的结果:

因此,就故事的寓意而言,不要在 View 中放入ORDER BY。将ORDER BY放在引用它们的查询中。 如果排序很昂贵,则可以考虑添加/更改索引以支持它。

10-07 19:49
查看更多