我正在尝试使用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
=====================
这是该图的屏幕截图
我希望返回用户的
DisplayName
和UserTotalMarks
并对该结果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完全忽略了
TOP
和ORDER 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放在引用它们的查询中。 如果排序很昂贵,则可以考虑添加/更改索引以支持它。