我试图创建一个带有ORDER BY
子句的视图。我已经在SQL Server 2012 SP1上成功创建了它,但当我试图在SQL Server 2008 R2上重新创建它时,我得到了这个错误:
Msg 102, Level 15, State 1, Procedure TopUsers, Line 11
Incorrect 语法在'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',并对这个结果进行排序,所以结果最大的用户在上面。
我不知道你认为这个ORDER BY'有什么作用?即使你*以合法的方式在视图中放入
ORDER BY(例如添加
TOP子句),如果你只是从视图中选择,例如
SELECT * FROM dbo.TopUsersTest;而没有
ORDER BY子句,SQL Server可以自由地以最有效的方式返回行,这不一定与你期望的顺序一致。这是因为
ORDER BY是超载的,因为它试图达到两个目的:对结果进行排序,并决定哪些行要包括在
TOP中。在这种情况下,
TOP`总是胜出(尽管取决于选择的扫描数据的索引,你可能会观察到你的顺序是按照预期工作的 - 但这只是一个巧合)。
**为了达到你想要的目的,你需要将你的ORDER BY
子句添加到从视图中提取数据的查询中,而不是添加到视图本身的代码中。
所以你的视图代码应该只是:
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
你可以从执行计划中看到,"TOP "和 "ORDER BY "已经被SQL Server完全忽略并优化掉了:
根本就没有TOP
运算符,也没有排序。SQL Server已经把它们完全优化掉了。
现在,如果你把视图改为 "ORDER BY SalesID",你就会碰巧得到视图所说的排序,但只是--如前所述--巧合。
但是如果你改变你的外层查询来执行你想要的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
该计划仍然优化了视图中的TOP
/ORDER BY
,但增加了一个排序(注意,代价不小),以呈现按CustomerID
排序的结果:
所以,故事的寓意是,不要把ORDER BY放在视图中。把ORDER BY放在引用它们的查询中。如果排序很昂贵,你可以考虑添加/改变一个索引来支持它。
我已经成功地通过使用以下方法强迫视图排序
SELECT TOP 9999999 ... ORDER BY something
不幸的是,由于这里的问题,使用`SELECT TOP 100 PERCENT'无法工作。
错误是:FROM (SELECT empno,name FROM table1 where location = 'A' ORDER BY emp_no)
。
而解决方案是:FROM (SELECT empno,name FROM table1 where location = 'A') ORDER BY emp_no
。