ORDER BY 子句在视图中无效 [英] The ORDER BY clause is invalid in views

问题描述

我收到此错误:
ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效，除非还指定了 TOP 或 FOR XML.
在下面的代码中，尝试从视图中订购时.我该如何解决这个问题?

I get this error:
The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP or FOR XML is also specified.
on the code below, when trying to order from view. How do I fix this?

USE [MYDB]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER VIEW [dbo].[vw_FCoord]
AS
SELECT     FeatId, ISNULL(MLNumber, '') AS MLNumber,
                          (SELECT     Text
                            FROM          dbo.tblFType AS ft
                            WHERE      (FTypeId = dbo.tblFeat.FTypeId)) AS FType, Height, Width, Depth, ISNULL(Description, '') AS Description, 
                                 Latitude, Longitude * - 1 AS Longitude, IsSubsidence, ISNULL(ProjectName, '') 
                      AS ProjectName, ISNULL
                          ((SELECT     Text
                              FROM         dbo.tblFStatusType AS fst
                              WHERE     (FStatusTypeId = dbo.tblFeat.FStatusTypeId)), 'Not Set') AS FStatus, ISNULL
                          ((SELECT     Text
                              FROM         dbo.tblSGType AS st
                              WHERE     (SGTypeId = dbo.tblFeat.SGTypeId)), 'Not Set') AS SGType, SGIsBackfillMaterial, SGDate, 
                      ISNULL(SGDetails, '') AS SGDetails, SGCost, SGIsBatCompatible,
                      (Select COUNT(*) from tblProjectFeat pf where pf.FeatId=dbo.tblFeat.FeatId) NumProjects 
FROM         dbo.tblFeat
WHERE     (Latitude > 0) AND (Longitude > 0)
order by NumProjects asc
GO

推荐答案

您不能在视图中指定 ORDER BY 子句.创建没有 ORDER BY 的视图，并在从视图中选择时指定 ORDER BY 子句:

You can't specify the ORDER BY clause in a view. Create the view without the ORDER BY and specify the ORDER BY clause when you're selecting from the view:

ALTER VIEW [dbo].[vw_FCoord]
...
GO


SELECT
    *
FROM vw_FCoord
ORDER BY NumProjects ASC

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