sql - 消息 102，级别 15，状态 1，第 28 行“订单”附近的语法不正确

Question

我正在尝试以下查询，但出现错误。我正在尝试在同一个查询中计算 YTD 和 Previous YTD。

消息 102，级别 15，状态 1，第 28 行“订单”附近的语法不正确。

WITH
    grouped_by_date AS
(
    SELECT
        [Sales_Organization],
        [Market_Grp],
        [Delivery_Year],
        [Delivery_Month],
        [Invoicing_Day],
        SUM(QTY_UoM)             AS Weight
    FROM
        tmp.factsales s
    GROUP BY
        [Sales_Organization],
        [Market_Grp],
        [Delivery_Year],
        [Delivery_Month],
        [Invoicing_Day]
),
    cumulative_sum_for_ytd AS
(
    SELECT
        *,


        SUM([Weight]) OVER (PARTITION BY [Delivery_Year] ORDER BY [Delivery_Month], [Invoicing_Day]
                           )
                              AS Weight_YTD
    FROM
        grouped_by_date
),


    hack_to_do_lag AS
(
    SELECT
        *,
        CASE
            WHEN [Delivery_Year]%2=1
            THEN MAX(CASE WHEN [Delivery_Year]%2=0 THEN [Weight_YTD] END) OVER (PARTITION BY ([Delivery_Year]+0)/2)
            ELSE MAX(CASE WHEN [Delivery_Year]%2=1 THEN [Weight_YTD] END) OVER (PARTITION BY ([Delivery_Year]+1)/2)
        END
            AS Weight_PreviousYTD
    FROM
        cumulative_sum_for_ytd
)
SELECT
    *
FROM
    hack_to_do_lag

我在谷歌上搜索似乎问题链接了我使用的版本，事实上：

SELECT @@VERSION

Microsoft SQL Server 2008 R2 (SP3) - 10.50.6220.0 (X64) 2015 年 3 月 19 日 12:32:14 版权所有 (c) Microsoft Corporation Enterprise Edition (64-bit) o​​n Windows NT 6.3 (Build 9600:) (Hypervisor)

我该如何解决我的问题？我无法更改版本。

标签： sqlsql-servertsqlsql-server-2008select

Answer 1

SQL Server 2008 不支持累积窗口函数，因此您需要进行不同的计算。子查询 orapply是一个典型的方法：

WITH grouped_by_date AS (
      SELECT Sales_Organization, Market_Grp,
             Delivery_Year, Delivery_Month, Invoicing_Day,
             SUM(QTY_UoM) as Weight
      FROM tmp.factsales s
      GROUP BY Sales_Organization, Market_Grp,
               Delivery_Year, Delivery_Month, Invoicing_Day
     )
SELECT gbd.*,
       (SELECT SUM(gbd2.Weight)
        FROM grouped_by_date gbd2
        WHERE gbd2.Delivery_Year = gbd.Delivery_Year AND
              (gbd2.Delivery_Month < gbd.Delivery_Month OR
               gbd2.Delivery_Month = gbd.Delivery_Month AND
               gbd2.Invoicing_Day <- gbd.Invoicing_Day
              )
       ) as weight_ytd
FROM grouped_by_date gbd;