sql-server - 在 Microsoft SQL Server Management Studio 中创建 View 时遇到问题

Question

我是 sql 的新手，正在努力制作 View 。这有效并将我需要的正确数据作为表格提取，但是当我将其作为 View 尝试时，出现错误:

SQL text cannot be represented in the grid pane and diagram pane.

SELECT [Data_Collector_ID],
       [Batch_Info_ID],
       [AssetTag],
       [DateTimeStamp],
       [Dust_Collector_DP]
FROM (
    SELECT [Data_Collector_ID],
           [Batch_Info_ID],
           [AssetTag],
           [DateTimeStamp],
           [Dust_Collector_DP],
           ROW_NUMBER() OVER (PARTITION BY [Batch_Info_ID] ORDER BY [DateTimeStamp] DESC) rn
    FROM [PLCLogging].[dbo].[Coater_Data_Collector]
) tmp
WHERE rn = 1 AND ([DateTimeStamp] > DATEADD(DAY, -7, GETDATE()))
    OR rn = 2 AND ([DateTimeStamp] > DATEADD(DAY, -7, GETDATE()))
ORDER BY DateTimeStamp DESC

知道如何让它作为 View 工作吗？

我试图为每个 Batch_Info_ID 提取两个最近的值在过去一周。如果我需要提供更多详细信息，请告诉我。

Answer 1

以下article讨论 View 设计器无法解释复杂的查询。

打开一个新的查询窗口并复制并粘贴以下文本并执行:

CREATE VIEW MyViewName
AS
    SELECT [Data_Collector_ID],
           [Batch_Info_ID],
           [AssetTag],
           [DateTimeStamp],
           [Dust_Collector_DP]
    FROM (
        SELECT [Data_Collector_ID],
               [Batch_Info_ID],
               [AssetTag],
               [DateTimeStamp],
               [Dust_Collector_DP],
               ROW_NUMBER() OVER (PARTITION BY [Batch_Info_ID] ORDER BY [DateTimeStamp] DESC) rn
        FROM [PLCLogging].[dbo].[Coater_Data_Collector]
    ) tmp
    WHERE rn = 1 AND ([DateTimeStamp] > DATEADD(DAY, -7, GETDATE()))
        OR rn = 2 AND ([DateTimeStamp] > DATEADD(DAY, -7, GETDATE()))
    ORDER BY DateTimeStamp DESC