ORDER BY子句在视图中无效

小编典典

ORDER BY子句在视图中无效

sql

我收到此错误消息：
除非还指定了TOP或FOR XML，否则ORDER BY子句在视图，内联函数，派生表，子查询和公用表表达式中无效。
尝试从视图中订购时，请在下面的代码中进行操作。我该如何解决？

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

阅读 249

2021-04-15

共1个答案

小编典典

您不能ORDER BY在视图中指定子句。在不ORDER BY使用的情况下创建视图，并ORDER BY在从视图中进行选择时指定该子句：

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


SELECT
    *
FROM vw_FCoord
ORDER BY NumProjects ASC

2021-04-15