ORDER BY 子句在视图、内联函数、派生表、子查询和公用表表达式中无效

2022-01-03 00:00:00 sql database sql-server sql-server-2012

ORDER BY 子句在视图、内联函数、派生函数中无效表、子查询和公用表表达式，除非 TOP、OFFSET或 FOR XML 也被指定.

The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

我在尝试执行以下查询时遇到上述错误.任何人都可以看看并告诉我我在这里做错了什么吗?

I am getting the above said error while trying to execute the following query. Can anyone please have a look and tell me what am I doing wrong here?

SELECT 
    * 
FROM (
    SELECT 
        Stockmain.VRNOA, 
        item.description as item_description, 
        party.name as party_name, 
        stockmain.vrdate, 
        stockdetail.qty, 
        stockdetail.rate, 
        stockdetail.amount, 
        ROW_NUMBER() OVER (ORDER BY VRDATE) AS RowNum
    FROM StockMain 
    INNER JOIN StockDetail 
        ON StockMain.stid = StockDetail.stid 
    INNER JOIN party 
        ON party.party_id = stockmain.party_id 
    INNER JOIN item 
        ON item.item_id = stockdetail.item_id 
    WHERE stockmain.etype='purchase' 
    ORDER BY VRDATE DESC
) AS MyDerivedTable
WHERE 
    MyDerivedTable.RowNum BETWEEN 1 and 5

推荐答案

在WHERE子句之后的内部查询中不需要使用ORDER BY，因为你已经使用了它在 ROW_NUMBER() OVER (ORDER BY VRDATE DESC).

You do not need to use ORDER BY in inner query after WHERE clause because you have already used it in ROW_NUMBER() OVER (ORDER BY VRDATE DESC).

SELECT 
    * 
FROM (
    SELECT 
        Stockmain.VRNOA, 
        item.description as item_description, 
        party.name as party_name, 
        stockmain.vrdate, 
        stockdetail.qty, 
        stockdetail.rate, 
        stockdetail.amount, 
        ROW_NUMBER() OVER (ORDER BY VRDATE DESC) AS RowNum  --< ORDER BY
    FROM StockMain 
    INNER JOIN StockDetail 
        ON StockMain.stid = StockDetail.stid 
    INNER JOIN party 
        ON party.party_id = stockmain.party_id 
    INNER JOIN item 
        ON item.item_id = stockdetail.item_id 
    WHERE stockmain.etype='purchase' 
) AS MyDerivedTable
WHERE 
    MyDerivedTable.RowNum BETWEEN 1 and 5

相关文章