VIEW中的ORDER BY返回不同的结果SQL [英] ORDER BY in VIEW returns different results SQL

问题描述

这是我的观点:

CREATE VIEW [STD_USER].[view_TransInvoice]
AS

SELECT TOP 999999 Customernr, Referensnr,'2' as a, InvoiceRowData, FileHead
    FROM [STD_USER].[Transexport]
    WHERE InvoiceRowData IS NOT NULL
    UNION 
SELECT TOP 999999 Customernr, Referensnr,'1' AS a , InvoiceHead , FileHead
    FROM [STD_USER].[Transexport]
    WHERE InvoiceHead IS NOT NULL
    UNION 
SELECT TOP 999999 Customernr, Referensnr,'3' AS a , InvoiceFoot , FileHead 
    from [STD_USER].[Transexport]
    WHERE InvoiceFoot IS NOT NULL
    ORDER BY Customernr, Referensnr, 3

当我在服务器 (Microsoft SQL Server Standard Edition v. 8.00.2055) x64 上运行它时，我以正确的顺序得到了我想要的结果.

When I run it on the server (Microsoft SQL Server Standard Edition v. 8.00.2055) x64 I get the result that I want in the right order.

但是当我在 (Microsoft SQL Server Standard Edition v.10.50.1702.0) x86 上运行它时，我没有得到相同的结果.当我运行 VIEW 时，它就像忽略 ORDER BY 语句一样.如果我只是运行 SELECT 语句，另一方面我会以正确的顺序获得正确的结果.两台服务器上的数据库和脚本完全相同.

But when I run it on (Microsoft SQL Server Standard Edition v.10.50.1702.0) x86 I do not get the same result. It’s likes it ignores the ORDER BY statement when I run the VIEW. If I just run the SELECT statements I do on the other hand get the RIGHT result with the right order. The databases are exactly the same and the scripts as well on both the servers.

请帮帮我！

推荐答案

如果您需要 ORDER BY 的结果，您需要在 ORDER BY 中放入 ORDER BYcode>SELECT 从视图中.

If you need an ORDER BY for the results you need to put an ORDER BY in the SELECT from the view.

View 中的 ORDER BY 仅用于控制 TOP 适用于 [STD_USER].[Transexport] 分支的内容，而不是用于针对视图的选择操作中的最终结果顺序.

The ORDER BY inside the View only serves to control what the TOP applies to for the [STD_USER].[Transexport] branch not for the eventual order of results in select operations against the view.

请参阅 TOP 100% ORDER BY 视为有害. 对此的更多解释.

See TOP 100 Percent ORDER BY Considered Harmful. for more explanation of this.

编辑 有趣的是，最终 ORDER BY 的作用会根据它是否在 View 中而改变.当 SELECT 在视图之外运行时，它用于对整个结果进行排序，并且它的作用是限制 UNIONTOP> 消失了.

Edit It is quite interesting though that the role of the final ORDER BY changes depending upon whether it is in a View or not. When the SELECT is run outside of a View it serves to order the entire results and it's role in limiting the TOP for the final branch of the UNION disappears.

编辑 2 在 这个最近的连接项

CREATE TABLE A (C VARCHAR(100))
CREATE TABLE B (C VARCHAR(100))


SELECT TOP 1000 C 
FROM A
UNION ALL
SELECT TOP 1000 C
FROM B
ORDER BY C

GO

CREATE VIEW V
AS
SELECT TOP 1000 C 
FROM A
UNION ALL
SELECT TOP 1000 C
FROM B
ORDER BY C

GO

SELECT *
FROM V

GO

DROP TABLE A
DROP TABLE B
DROP VIEW V

这篇关于VIEW中的ORDER BY返回不同的结果SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！