错误:SQL 视图在我尝试保存它时给我 ORDER BY 错误，然后在保存时删除 WITH 语句.为什么?

Question

我目前有一个工作SQL视图，可以从表中查询一些数据。 数据都在同一个表中，但需要更改列才能获得我需要的正确格式。 下面的代码在执行时按计划工作。 当我尝试保存视图时出现问题。 它给了我 ORDER BY 子句错误" 警告：ORDER BY 子句仅用于确定视图定义中的 TOP 子句返回的行...">

如果我单击确定并保存，它将保存视图但删除我的 WITH 语句，使代码在 SQL Server 管理工作室中无法执行。 有趣的是，如果我通过 excel 或 R 连接到 sql 视图，它仍然正常运行代码并为我提供正确的数据。

在不给你们任何数据的情况下，我的语法是否有问题可能导致这种情况？

WITH a AS 
(
SELECT        OrderID, Test, DoneDate AS SampleReceived, row_number() 
OVER (Partition BY OrderID ORDER BY OrderID) AS rn
FROM            SMSU.DepartmentChain
WHERE        (Department = 'Sample Receiving')
), 
b AS
(
SELECT OrderID, DoneDate AS SampleReported, row_number() OVER (Partition 
BY OrderID ORDER BY OrderID) AS rn
FROM            SMSU.DepartmentChain
WHERE        (Department = 'Reporting')
)
SELECT  a.OrderID, a.Test, a.SampleReceived, b.SampleReported, a.rn
FROM    a INNER JOIN b ON a.OrderID = b.OrderID AND a.rn = b.rn
WHERE   (a.rn = 1)

Answer 1

您如何创建视图？ 如果您使用这样的脚本：

create view dbo.MyView as 
WITH a AS 
(
SELECT        OrderID, Test, DoneDate AS SampleReceived, row_number() 
OVER (Partition BY OrderID ORDER BY OrderID) AS rn
FROM            SMSU.DepartmentChain
WHERE        (Department = 'Sample Receiving')
), 
b AS
(
SELECT OrderID, DoneDate AS SampleReported, row_number() OVER (Partition 
BY OrderID ORDER BY OrderID) AS rn
FROM            SMSU.DepartmentChain
WHERE        (Department = 'Reporting')
)
SELECT  a.OrderID, a.Test, a.SampleReceived, b.SampleReported, a.rn
FROM    a INNER JOIN b ON a.OrderID = b.OrderID AND a.rn = b.rn
WHERE   (a.rn = 1)

它应该有效。