Sql Server 2008 视图中的 ORDER BY
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1306359/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
ORDER BY in a Sql Server 2008 view
提问by eidylon
we have a view in our database which has an ORDER BY in it. Now, I realize views generally don't order, because different people may use it for different things, and want it differently ordered. This view however is used for a VERY SPECIFICuse-case which demands a certain order. (It is team standings for a soccer league.)
我们的数据库中有一个视图，其中有一个 ORDER BY。现在，我意识到视图通常不排序，因为不同的人可能将它用于不同的事情，并希望它以不同的方式排序。但是，此视图用于需要特定顺序的非常特定的用例。（这是足球联赛的球队积分榜。）
The database is Sql Server 2008 Express, v.10.0.1763.0 on a Windows Server 2003 R2 box.
数据库是 Windows Server 2003 R2 机器上的 Sql Server 2008 Express, v.10.0.1763.0。
The view is defined as such:
视图定义如下：
CREATE VIEW season.CurrentStandingsOrdered
AS
SELECT TOP 100 PERCENT *, season.GetRanking(TEAMID) RANKING
FROM season.CurrentStandings
ORDER BY
GENDER, TEAMYEAR, CODE, POINTS DESC,
FORFEITS, GOALS_AGAINST, GOALS_FOR DESC,
DIFFERENTIAL, RANKING
It returns:
它返回：
GENDER, TEAMYEAR, CODE, TEAMID, CLUB, NAME,
WINS, LOSSES, TIES, GOALS_FOR, GOALS_AGAINST,
DIFFERENTIAL, POINTS, FORFEITS, RANKING
Now, when I run a SELECTagainst the view, it orders the results by GENDER, TEAMYEAR, CODE, TEAMID. Notice that it is ordering by TEAMIDinstead of POINTSas the order by clause specifies.
现在，当我对视图运行SELECT 时，它按GENDER, TEAMYEAR, CODE, TEAMID对结果进行排序。请注意，它是按TEAMID而不是POINTS排序，正如 order by 子句指定的那样。
However, if I copy the SQL statement and run it exactly as is in a new query window, it orders correctly as specified by the ORDER BYclause.
但是，如果我复制 SQL 语句并在新查询窗口中完全按照原样运行它，它会按照ORDER BY子句的指定正确排序。
回答by Ed Harper
The order of rows returned by a view with an ORDER BYclause is never guaranteed. If you need a specific row order, you must specify where you select from the view.
带有ORDER BY子句的视图返回的行顺序永远无法保证。如果需要特定的行顺序，则必须指定从视图中选择的位置。
See this the note at the top of this Book On-Lineentry.
请参阅此在线图书条目顶部的注释。
回答by gbn
SQL Server 2005 ignores TOP 100 PERCENT by design.
SQL Server 2005 在设计上忽略了 TOP 100 PERCENT。
Try TOP 2000000000 instead.
改为尝试 TOP 2000000000。
Now, I'll try and find a reference... I was at a seminar presented by Itzak Ben-Gan who mentioned it
现在，我会尝试找到一个参考...我参加了 Itzak Ben-Gan 主持的研讨会，他提到了它
Found some...
发现了一些...
"TOP 100 Percent ORDER BY Considered Harmful"
In this particular case, the optimizer recognizes that TOP 100 PERCENT qualifies all rows and does not need to be computed at all.
在这种特殊情况下，优化器识别出 TOP 100 PERCENT 限定所有行并且根本不需要计算。
回答by Mahboubeh_J
Just use :
只需使用：
"Top (99) Percent"
“最高 (99)%”
or
或者
"Top (a number 1000s times more than your data rows like 24682468123)" it works! just try it.
“顶部（比您的数据行多 1000 倍，如 24682468123）”它有效！就试一试吧。
回答by Mike
In SQL server 2008, ORDER BY is ignored in views that use TOP 100 PERCENT. In prior versions of SQL server, ORDER BY was only allowed if TOP 100 PERCENT was used, but a perfect order was never guaranteed. However, many assumed a perfect order was guaranteed. I infer that Microsoft does not want to mislead programmers and DBAs into believing there is a guaranteed order using this technique.
在 SQL Server 2008 中，ORDER BY 在使用 TOP 100 PERCENT 的视图中被忽略。在 SQL Server 的早期版本中，仅当使用 TOP 100 PERCENT 时才允许 ORDER BY，但无法保证完美的顺序。然而，许多人认为完美的秩序是有保证的。我推断 Microsoft 不想误导程序员和 DBA 相信使用这种技术有保证的顺序。
An excellent comparative demonstration of this inaccuracy, can be found here...
可以在此处找到这种不准确性的极好比较演示...
Oops, I just noticed that this was already answered. But checking out the comparative demonstration is worth a look anyway.
哎呀，我刚刚注意到这已经得到了回答。但是无论如何，查看比较演示还是值得一看的。
回答by mikemurf22
Microsoft has fixed this. You have patch your sql server
微软已经解决了这个问题。你已经修补了你的 sql server
回答by Ryan Montgomery
I found an alternative solution.
我找到了一个替代解决方案。
My initial plan was to create a 'sort_order' column that would prevent users from having to perform a complex sort.
我最初的计划是创建一个“sort_order”列，以防止用户执行复杂的排序。
I used a windowed function ROW_NUMBER. In the ORDER BY clause, I specified the default sort order that I needed (just as it would have been in the ORDER BY of a SELECT statement).
我使用了一个窗口函数 ROW_NUMBER。在 ORDER BY 子句中，我指定了我需要的默认排序顺序（就像在 SELECT 语句的 ORDER BY 中一样）。
I get several positive outcomes:
我得到了几个积极的结果：
By default, the data is getting returned in the default sort order I originally intended (this is probably due to the windowed function having to sort the data prior to assigning the sort_order value)
Other users can sort the data in alternative ways if they choose to
The sort_order column is there for a very specific sort need, making it easier for users to sort the data should whatever tool they use rearranges the rowset.
默认情况下，数据以我最初打算的默认排序顺序返回（这可能是由于窗口函数必须在分配 sort_order 值之前对数据进行排序）
其他用户可以选择以其他方式对数据进行排序
sort_order 列用于满足非常特定的排序需求，如果用户使用的任何工具重新排列行集，则用户可以更轻松地对数据进行排序。
Note: In my specific application, users are accessing the view via Excel 2010, and by default the data is presented to the user as I had hoped without further sorting needed.
注意：在我的特定应用程序中，用户通过 Excel 2010 访问视图，默认情况下，数据将按我希望的方式呈现给用户，无需进一步排序。
Hope this helps those with a similar problem.
希望这可以帮助那些有类似问题的人。
Cheers, Ryan
干杯，瑞安
回答by Joshua
run a profiler trace on your database and see the query that's actuallybeing run when you query your view.
在您的数据库上运行分析器跟踪，并查看查询视图时实际运行的查询。
You also might want to consider using a stored procedure to return the data from your view, ordered correctly for your specific use case.
您可能还需要考虑使用存储过程从您的视图中返回数据，并为您的特定用例正确排序。
