Sql Server 2008 视图中的 ORDER BY [英] ORDER BY in a Sql Server 2008 view

问题描述

我们的数据库中有一个视图，其中有一个 ORDER BY.现在，我意识到视图通常不排序，因为不同的人可能将它用于不同的事情，并希望它以不同的方式排序.但是，此视图用于需要特定顺序的非常特殊 用例.(这是足球联赛的球队积分榜.)

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 SPECIFIC use-case which demands a certain order. (It is team standings for a soccer league.)

数据库是 Windows Server 2003 R2 机器上的 Sql Server 2008 Express, v.10.0.1763.0.

The database is Sql Server 2008 Express, v.10.0.1763.0 on a Windows Server 2003 R2 box.

视图定义如下:

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

它返回:

GENDER, TEAMYEAR, CODE, TEAMID, CLUB, NAME,  
WINS, LOSSES, TIES, GOALS_FOR, GOALS_AGAINST,  
DIFFERENTIAL, POINTS, FORFEITS, RANKING

现在，当我对视图运行 SELECT 时，它会按 GENDER、TEAMYEAR、CODE、TEAMID 对结果进行排序.请注意，它按 TEAMID 排序，而不是按 order by 子句指定的 POINTS 排序.

Now, when I run a SELECT against the view, it orders the results by GENDER, TEAMYEAR, CODE, TEAMID. Notice that it is ordering by TEAMID instead of POINTS as the order by clause specifies.

但是，如果我复制 SQL 语句并在新查询窗口中完全按照原样运行它，它会按照 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 BY clause.

推荐答案

从不保证带有 ORDER BY 子句的视图返回的行顺序.如果您需要特定的行顺序，则必须指定从视图中选择的位置.

The order of rows returned by a view with an ORDER BY clause is never guaranteed. If you need a specific row order, you must specify where you select from the view.

请参阅 此在线图书条目.

这篇关于Sql Server 2008 视图中的 ORDER BY的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！