与ORDER BY子句创建一个视图(Create a view with ORDER BY clau

2019-07-20 22:00发布

站内文章 / 移动开发

163 0

放荡不羁爱自由

女 | 书童

私信

我试图创建具有一个视图ORDER BY子句。我有SQL服务器上成功创建2012 SP1，但SQL Server 2008 R2上，当我尝试重新创建它，我得到这个错误：

消息102，级别15，状态1，过程TopUsers，11号线
附近的“偏移”不正确的语法。

创建视图的代码是

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和秩序这一结果说明，所以与上顶最大的成果用户。

Answer 1:

我不知道你怎么想这个ORDER BY的完成？即使你把ORDER BY在视图中的法律途径（通过添加例如TOP子句），如果你只是从视图，如选择SELECT * FROM dbo.TopUsersTest; 没有ORDER BY子句，SQL 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是没有意义的，以便甚至不应该包括在内。

为了说明，使用AdventureWorks2012，这里有一个例子：

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 BY已经被完全忽略，而SQL 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中引用它们的查询。 如果排序是昂贵的，你可以考虑添加/更改索引来支持它。

Answer 2:

我已经成功迫使使用订购的观点

SELECT TOP 9999999 ... ORDER BY something

不幸的是使用SELECT TOP 100 PERCENT不会因为这个问题的工作在这里。

Answer 3:

从2012年的Sql你可以强制在视图和子查询排序随偏移距

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

警告：这应该只在小名单，因为OFFSET强制众目睽睽之下甚至要评估是否进一步加入或视图上的过滤器减少其大小可以用！

有给力的视图排序没有副作用真的有很好的理由没有什么好办法。

Answer 4:

由于在此张贴一个评论建议使用存储过程返回数据...我认为这是最好的答案。在我来说，我所做的是写了一个View来封装查询逻辑和连接，然后我写了一个Stored Proc返回的数据进行排序和proc还包括其他的增强功能，如过滤数据参数。

现在，你必须选择查询视图，可以让您进一步处理数据。或者，你必须执行存储的过程，这是更快，更精确的输出选项。

存储过程的执行查询数据

视图定义

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

存储过程定义

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

Answer 5:

只需使用在选择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

Answer 6:

错误是： 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

Answer 7:

请尝试以下逻辑。

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

Answer 8:

为了加一个ORDER BY到视图下进行

CREATE VIEW [dbo].[SQLSTANDARDS_PSHH]
AS


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

Answer 9:

使用程序

创建PROC MyView的作为开始SELECT TOP 99999999999999列1，列2 FROM dbo.Table排序列1月底

执行程序

EXEC MyView的

文章来源: Create a view with ORDER BY clause

标签： sql-server database tsql view sql-server-2008-r2

放荡不羁爱自由

女 | 书童

私信

收藏的人(0)

Ta的文章更多文章

0条评论

还没有人评论过~