ORDER BY子句在视图、内联函数、派生表、子查询和普通表表达式中是无效的

本文是小编为大家收集整理的关于ORDER BY子句在视图、内联函数、派生表、子查询和普通表表达式中是无效的的处理/解决方法，可以参考本文帮助大家快速定位并解决问题，中文翻译不准确的可切换到English标签页查看源文。

中文
English

问题描述

从子句中的顺序在视图，内联函数中无效表格，子查询和公共表格表达式，除非顶部偏移或为XML指定.

我在尝试执行以下查询时会遇到上述错误.有人可以看看，告诉我我在这里做错了什么?

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

其他推荐答案

ORDER BY column OFFSET 0 ROWS

令人惊讶地使它起作用，这是一个奇怪的功能.

一个更大的示例与CTE是一种临时"存储"长期查询以稍后重新排序的方式:

;WITH cte AS (
    SELECT .....long select statement here....
)

SELECT * FROM 
(
    SELECT * FROM 
    ( -- necessary to nest selects for union to work with where & order clauses
        SELECT * FROM cte WHERE cte.MainCol= 1 ORDER BY cte.ColX asc OFFSET 0 ROWS 
    ) first
    UNION ALL
    SELECT * FROM 
    (  
        SELECT * FROM cte WHERE cte.MainCol = 0 ORDER BY cte.ColY desc OFFSET 0 ROWS 
    ) last
) as unionized
ORDER BY unionized.MainCol desc -- all rows ordered by this one
OFFSET @pPageSize * @pPageOffset ROWS -- params from stored procedure for pagination, not relevant to example
FETCH FIRST @pPageSize ROWS ONLY -- params from stored procedure for pagination, not relevant to example

因此，我们得到MainCol

订购的所有结果

但是MainCol = 1的结果由ColX

订购

和MainCol = 0的结果由ColY

订购

本文地址：https://www.itbaoku.cn/post/597571.html

百度翻译此文有道翻译此文

问题描述

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

其他推荐答案

ORDER BY column OFFSET 0 ROWS

Surprisingly makes it work, what a strange feature.

A bigger example with a CTE as a way to temporarily "store" a long query to re-order it later:

;WITH cte AS (
    SELECT .....long select statement here....
)

SELECT * FROM 
(
    SELECT * FROM 
    ( -- necessary to nest selects for union to work with where & order clauses
        SELECT * FROM cte WHERE cte.MainCol= 1 ORDER BY cte.ColX asc OFFSET 0 ROWS 
    ) first
    UNION ALL
    SELECT * FROM 
    (  
        SELECT * FROM cte WHERE cte.MainCol = 0 ORDER BY cte.ColY desc OFFSET 0 ROWS 
    ) last
) as unionized
ORDER BY unionized.MainCol desc -- all rows ordered by this one
OFFSET @pPageSize * @pPageOffset ROWS -- params from stored procedure for pagination, not relevant to example
FETCH FIRST @pPageSize ROWS ONLY -- params from stored procedure for pagination, not relevant to example

So we get all results ordered by MainCol

But the results with MainCol = 1 get ordered by ColX

And the results with MainCol = 0 get ordered by ColY

上一篇：PHPExcel和文本包装

下一篇：从同一服务器上的另一个数据库的备份中创建新的数据库？