在 MS SQL Server 2005 中不支持 OVER? [英] No Support for OVER in MS SQL Server 2005?

问题描述

OVER 我尝试在 MS SQL Server Management Studio 中运行查询时遇到错误.我们在 Windows 2003 服务器上使用 MS SQL Server 2005.

OVERI am getting an error when trying to run a query in MS SQL Server Management Studio. We use MS SQL Server 2005 on a windows 2003 server.

这是查询:

WITH    q AS
        (
        SELECT  *,
                ROW_NUMBER() OVER (PARTITION BY dbo.[1_MAIN - Contacts].Contact_ID ORDER BY dbo.[1_MAIN - Contacts].Contact_ID) AS rn
        FROM    dbo.[1_MAIN - Contacts]
        INNER JOIN
                dbo.Referral
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.Referral.Referral_ID
        INNER JOIN
                dbo.prov_training_records
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.prov_training_records.Contact_ID
        LEFT OUTER JOIN
                dbo.Resource_Center
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.Resource_Center.Contact_ID
        FULL OUTER JOIN
                dbo.Providers
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.Providers.Contact_ID
        )
SELECT  *
FROM    q

当我运行它时，我只会看到一个包含以下信息的弹出窗口:

When I run it I just get a pop up window with the following info:

窗口标题 - 查询定义不同"

Window Title - "Query Definitions Differ"

描述 - 解析 SQL 窗格的内容时遇到以下错误:"

Description - "The following errors were encountered while parsing the contents of the SQL pane:"

错误 - 不支持 OVER SQL 构造或语句."

Error - "The OVER SQL construct or statement is not supported."

然后最后它说 - 查询不能在图表和标准窗格中以图形方式表示."

Then at the end it says - "The query cannot be represented graphically in the Diagram and Criteria Pane."

但是，我没有打开图表或标准窗格.

However, I do not have the Diagram or Criteria panes open.

我需要确保我们有正确的语法.如果语法正确，那么我要么需要解决 Management Studio 抛出此错误的原因，要么找到 OVER 的变通方法并使用不同的方法解决此问题.

I need to make sure we have the correct syntax. If the syntax is correct then I either need to fix why the Management Studio throws this error or find a work-around to OVER and solve this with a different approach.

我知道规范说支持OVER"，但我还是收到了错误.

I know that the specs say "OVER" is supported, nevertheless I get the error.

在一个新的查询窗口中，如果我运行它:

In a new query window if I run this it works:

USE abtcontacts;
WITH    q AS
        (
        SELECT  dbo.[1_MAIN - Contacts].Contact_ID, dbo.[1_MAIN - Contacts].Date_entered_into_Database, dbo.[1_MAIN - Contacts].Date_of_Initial_Contact, 
                      dbo.[1_MAIN - Contacts].[Company_ Name], dbo.[1_MAIN - Contacts].Key_Contact_Title, dbo.[1_MAIN - Contacts].Key_Contact_First_Name, 
                      dbo.[1_MAIN - Contacts].Key_Contact_Middle, dbo.[1_MAIN - Contacts].Key_Contact_Last_Name, dbo.[1_MAIN - Contacts].Key_Credential, 
                      dbo.[1_MAIN - Contacts].Key_Contact_Occupation, dbo.[1_MAIN - Contacts].Key_Degree_1, dbo.[1_MAIN - Contacts].Key_Degree_2, 
                      dbo.[1_MAIN - Contacts].Key_Degree_3, dbo.[1_MAIN - Contacts].Date_of_Highest_Degree, dbo.[1_MAIN - Contacts].Work_Setting, 
                      dbo.[1_MAIN - Contacts].Website_Address, dbo.[1_MAIN - Contacts].Email_1_Key_Contact, dbo.[1_MAIN - Contacts].Email_2, 
                      dbo.[1_MAIN - Contacts].Email_3, dbo.[1_MAIN - Contacts].Day_Time_Phone_Number, dbo.[1_MAIN - Contacts].Extension, 
                      dbo.[1_MAIN - Contacts].Mobile_Phone_Number, dbo.[1_MAIN - Contacts].Bus_Fax_Number, dbo.[1_MAIN - Contacts].Home_Phone_Number, 
                      dbo.[1_MAIN - Contacts].Home_Fax_Number, dbo.[1_MAIN - Contacts].Mailing_Street_1, dbo.[1_MAIN - Contacts].Mailing_Street_2, 
                      dbo.[1_MAIN - Contacts].Mailing_City, dbo.[1_MAIN - Contacts].Mailing_State, dbo.[1_MAIN - Contacts].[Mailing_Zip/Postal], 
                      dbo.[1_MAIN - Contacts].Mailing_Country, dbo.[1_MAIN - Contacts].[Bad_Address?], dbo.[1_MAIN - Contacts].[PROV/REG?], 
                      dbo.[1_MAIN - Contacts].status_flag, dbo.[1_MAIN - Contacts].status_flag AS status_flag2, dbo.Providers.Referral_Source, dbo.Referral.Contact_Source, 
                      dbo.Resource_Center.cert_start_date, dbo.Resource_Center.cert_exp_date, dbo.prov_training_records.Contact_ID AS Expr2, 
                      dbo.prov_training_records.date_reg_email_sent, dbo.Resource_Center.access, dbo.Providers.Contact_ID AS Expr1,
                ROW_NUMBER() OVER (PARTITION BY dbo.[1_MAIN - Contacts].Contact_ID ORDER BY dbo.[1_MAIN - Contacts].Contact_ID) AS rn
        FROM    dbo.[1_MAIN - Contacts]
        INNER JOIN
                dbo.Referral
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.Referral.Referral_ID
        INNER JOIN
                dbo.prov_training_records
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.prov_training_records.Contact_ID
        LEFT OUTER JOIN
                dbo.Resource_Center
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.Resource_Center.Contact_ID
        FULL OUTER JOIN
                dbo.Providers
        ON      dbo.[1_MAIN - Contacts].Contact_ID = dbo.Providers.Contact_ID
        )
SELECT  *
FROM    q
WHERE   rn = 1

解决这个问题将帮助我解决我的另一个问题 在这里

Solving this problem will help me solve my other problem over here

推荐答案

您是否尝试过在 With 语句前添加分号?(例如 ;With q As)

Have you tried prepending a semicolon in front of your With statement? (e.g. ;With q As)

一些一般注意事项:

1. 您必须在查询而不是设计器中运行它.听起来你已经解决了这个问题.
2. 枚举列而不是使用 *.即，在下面的示例中，将 Col1、Col2 替换为您想要的实际列，并以它们来自的表为前缀.
3. 将 use DatabaseName 放在脚本的顶部，然后是换行符，然后是 GO，然后是另一个换行符.
4. 如果 With 语句之前的唯一语句是 use 语句和查询之间的 GO，则不需要分号，因为这是批处理中的第一条语句.

1. You must run this in a query not the designer. It sounds like you have fixed that.
2. Enumerate the columns instead of using *. I.e., in the example below replace Col1, Col2 with the actual columns you want prefixed by the table from whence they came.
3. Put use DatabaseName at the top of your script followed by a line break, followed by GO, followed by another line-break.
4. If the only statement prior to the With statement is the GO between your use statement and the query, then no semicolon is necessary since it is the first statement in the batch.

use DatabaseName
GO
WITH q AS
        (
        SELECT  TableName.Col1, TableName.Col2,...
            , ROW_NUMBER() OVER (PARTITION BY dbo.[1_MAIN - Contacts].Contact_ID 
                                                        ORDER BY dbo.[1_MAIN - Contacts].Contact_ID ) AS rn
        FROM dbo.[1_MAIN - Contacts]
            INNER JOIN dbo.Referral
                ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.Referral.Referral_ID
            INNER JOIN dbo.prov_training_records
                ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.prov_training_records.Contact_ID
            LEFT OUTER JOIN dbo.Resource_Center
                ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.Resource_Center.Contact_ID
            FULL OUTER JOIN dbo.Providers
                ON dbo.[1_MAIN - Contacts].Contact_ID = dbo.Providers.Contact_ID
        )
SELECT  *
FROM    q

这篇关于在 MS SQL Server 2005 中不支持 OVER?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！