ORDER BY 절을 사용하여 뷰 생성

ORDER BY 절을 사용하여 뷰 생성

다음을 사용하여 뷰를 작성하려고 합니다.ORDER BY절SQL Server 2012 SP1에서 성공적으로 생성했지만 SQL Server 2008 R2에서 다시 생성하려고 하면 다음 오류가 발생합니다.

Msg 102, 레벨 15, 상태 1, 절차 상위 사용자, 라인 11
'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법적인 방법으로 보기에서(예: a를 추가함으로써)TOP예를 들어, 보기에서 선택하면 됩니다.SELECT * FROM dbo.TopUsersTest;무턱내지 않고ORDER BYSQL 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의미가 없으므로 포함해서는 안 됩니다.

---

AdventureWorks 2012를 사용하는 예는 다음과 같습니다.

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 BYSQL 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이 문제로 인해 작동하지 않습니다.

Sql 2012에서 OFFSET을 사용하여 뷰 및 하위 쿼리의 순서를 강제로 지정할 수 있습니다.

SELECT      C.CustomerID,
            C.CustomerName,
            C.CustomerAge
FROM        dbo.Customer C
ORDER BY    CustomerAge OFFSET 0 ROWS;

경고: OFFSET은 뷰의 추가 조인 또는 필터가 크기를 줄여도 전체 뷰를 평가하도록 강제하기 때문에 작은 목록에서만 사용해야 합니다!

정말로 그리고 정당한 이유로 부작용이 없는 뷰에서 주문을 강요하는 좋은 방법은 없습니다.

이 게시물의 의견 중 하나는 저장 프로시저를 사용하여 데이터를 반환하는 것을 제안합니다.저는 그게 가장 좋은 답이라고 생각합니다.제 경우에는 제가 한 일은View쿼리 로직과 조인을 캡슐화하기 위해, 저는 다음과 같이 썼습니다.Stored Proc데이터를 정렬된 상태로 반환하고, 프로시저에는 데이터 필터링을 위한 매개 변수와 같은 기타 향상 기능도 포함되어 있습니다.

이제 보기를 쿼리하는 옵션을 선택해야 하므로 데이터를 추가로 조작할 수 있습니다.또는 저장된 proc를 실행할 수 있는 옵션이 있으며, 이는 더 빠르고 정확한 출력입니다.

데이터를 쿼리하기 위해 저장된 PROC 실행

VIEW 정의

USE [DBA]
GO

/****** Object:  View [olap].[vwUsageStatsLogSessionsRollup]    Script Date: 2/19/2019 10:10:06 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


--USE DBA
-- select * from olap.UsageStatsLog_GCOP039 where CubeCommand='[ORDER_HISTORY]'
;

ALTER VIEW [olap].[vwUsageStatsLogSessionsRollup] as
(
    SELECT --*
        t1.UsageStatsLogDate
        , COALESCE(CAST(t1.UsageStatsLogDate AS nvarchar(100)), 'TOTAL- DATES:') AS UsageStatsLogDate_Totals
        , t1.ADUserNameDisplayNEW
        , COALESCE(t1.ADUserNameDisplayNEW, 'TOTAL- USERS:') AS ADUserNameDisplay_Totals
        , t1.CubeCommandNEW
        , COALESCE(t1.CubeCommandNEW, 'TOTAL- CUBES:') AS CubeCommand_Totals
        , t1.SessionsCount
        , t1.UsersCount
        , t1.CubesCount
    FROM
    (
        select 
            CAST(olapUSL.UsageStatsLogTime as date) as UsageStatsLogDate
            , olapUSL.ADUserNameDisplayNEW
            , olapUSL.CubeCommandNEW
            , count(*) SessionsCount
            , count(distinct olapUSL.ADUserNameDisplayNEW) UsersCount
            , count(distinct olapUSL.CubeCommandNEW) CubesCount
        from 
            olap.vwUsageStatsLog olapUSL
        where CubeCommandNEW != '[]'
        GROUP BY CUBE(CAST(olapUSL.UsageStatsLogTime as date), olapUSL.ADUserNameDisplayNEW, olapUSL.CubeCommandNEW )
            ----GROUP BY 
            ------GROUP BY GROUPING SETS
            --------GROUP BY ROLLUP
    ) t1

    --ORDER BY
    --  t1.UsageStatsLogDate DESC
    --  , t1.ADUserNameDisplayNEW
    --  , t1.CubeCommandNEW
)
;


GO

저장된 PROC 정의

USE [DBA]
GO

/****** Object:  StoredProcedure [olap].[uspUsageStatsLogSessionsRollup]    Script Date: 2/19/2019 9:39:31 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author:      BRIAN LOFTON
-- Create date: 2/19/2019
-- Description: This proceedured returns data from a view with sorted results and an optional date range filter.
-- =============================================
ALTER PROCEDURE [olap].[uspUsageStatsLogSessionsRollup]
    -- Add the parameters for the stored procedure here
    @paramStartDate date = NULL,
    @paramEndDate date = NULL,
    @paramDateTotalExcluded as int = 0,
    @paramUserTotalExcluded as int = 0,
    @paramCubeTotalExcluded as int = 0
AS

BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
    SET NOCOUNT ON;

    DECLARE @varStartDate as date 
        = CASE  
            WHEN @paramStartDate IS NULL THEN '1900-01-01' 
            ELSE @paramStartDate 
        END
    DECLARE @varEndDate as date 
        = CASE  
            WHEN @paramEndDate IS NULL THEN '2100-01-01' 
            ELSE @paramStartDate 
        END

    -- Return Data from this statement
    SELECT 
        t1.UsageStatsLogDate_Totals
        , t1.ADUserNameDisplay_Totals
        , t1.CubeCommand_Totals
        , t1.SessionsCount
        , t1.UsersCount
        , t1.CubesCount
        -- Fields with NULL in the totals
            --  , t1.CubeCommandNEW
            --  , t1.ADUserNameDisplayNEW
            --  , t1.UsageStatsLogDate
    FROM 
        olap.vwUsageStatsLogSessionsRollup t1
    WHERE

        (
            --t1.UsageStatsLogDate BETWEEN @varStartDate AND @varEndDate
            t1.UsageStatsLogDate BETWEEN '1900-01-01' AND '2100-01-01'
            OR t1.UsageStatsLogDate IS NULL
        )
        AND
        (
            @paramDateTotalExcluded=0
            OR (@paramDateTotalExcluded=1 AND UsageStatsLogDate_Totals NOT LIKE '%TOTAL-%')
        )
        AND
        (
            @paramDateTotalExcluded=0
            OR (@paramUserTotalExcluded=1 AND ADUserNameDisplay_Totals NOT LIKE '%TOTAL-%')
        )
        AND
        (
            @paramCubeTotalExcluded=0
            OR (@paramCubeTotalExcluded=1 AND CubeCommand_Totals NOT LIKE '%TOTAL-%')
        )
    ORDER BY
            t1.UsageStatsLogDate DESC
            , t1.ADUserNameDisplayNEW
            , t1.CubeCommandNEW

END


GO

, 모가말했이듯두,이▁as.ORDER BY 서에 "사두syntax" 있는 는 유효하지 않습니다.VIEW선언.그러나 보고할 행 범위를 제한하여 SQL이 ORDER BY가 있는 보기를 수락하도록 속일 수 있습니다.물론 진정한 해결책은 테이블을 반환하는 저장 프로시저를 사용하는 것입니다.그러나 OP에 답변하기 위해 다음과 같은 것을 제안합니다.

WITH CTE (a,b,c)
AS (SELECT a,b,c FROM Table1)
SELECT TOP n * FROM cte
ORDER BY cte.A

여기서 'n'은 모든 데이터를 보고할 수 있을 만큼 충분히 큰 임의의 숫자입니다. 사용하지 TOP 100%그것이 걱정되는 바와 같이

오류: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

아래의 논리를 시도해 보세요.

SELECT TOP(SELECT COUNT(SNO) From MyTable) * FROM bar ORDER BY SNO

보기에 ORDER BY를 추가하려면 다음을 수행합니다.

CREATE VIEW [dbo].[SQLSTANDARDS_PSHH]
AS


SELECT TOP 99999999999999
Column1,
Column2
FROM
dbo.Table
Order by
Column1

절차 사용

proc MyView 작성을 시작으로 TOP 99999999999999 열 1, 열 2 FROM dbo를 선택합니다.1열 끝별 테이블 순서

절차를 밟다

exec MyView

선택:에서 TOP 100%를 사용하면 됩니다.

     CREATE VIEW [schema].[VIEWNAME] (
         [COLUMN1],
         [COLUMN2],
         [COLUMN3],
         [COLUMN4])
     AS 
        SELECT TOP 100 PERCENT 
         alias.[COLUMN1],
         alias.[COLUMN2],
         alias.[COLUMN3],
         alias.[COLUMN4]
        FROM 
           [schema].[TABLENAME] AS alias
          ORDER BY alias.COLUMN1
     GO

언급URL : https://stackoverflow.com/questions/15187676/create-a-view-with-order-by-clause