Server :错误MSG 102和MSG 156

Question

请帮帮我。我写的查询

with cte as
(   
    select 
        *, 
        row_number() over ( partition by product order by date desc ) as rownumber
    from 
        saleslist
    where 
        datediff( month, date, getdate() ) < 2 
    )
    select 
        product, 
        ((max(case when rownumber = 1 then price end) - 
          max(case when rownumber = maxn then price)) /
          max(case when rownumber = maxn then price end)
       )
  from 
      (select cte.*, max(rownumber) over (partition by product) as maxn   
       from cte) 
group by product

我收到了以下信息

Msg 102，15级，状态1，13线 在“)”附近不正确的语法。 Msg 156，15级，状态1，18线 关键字“group”附近的语法不正确。

谁能告诉我怎么解决这个问题吗？

Answer 1

with cte as
(   
    select *, 
    row_number() over ( partition by product order by date desc ) as rownumber
    from saleslist
    where datediff( month, [date], getdate() ) < 2 
    )
select product, 
     (
     (max(case when rownumber = 1 then price end) - 
         max(case when rownumber = maxn then price end) --< missing end here 
        )  /
         max(case when rownumber = maxn then price end)
       )
from 
(select cte.*, max(rownumber) over (partition by product) as maxn 
   from cte ) t  --< needs an alias here
group by product

Answer 2

server 2014支持第一个/最后一个值

with cte as 
  (   
      select *,
           product, 
           price as first_price, 
           row_number() over (partition by product order by date) as rownumber,
           last_value(price) -- price of the row with the latest date
           over (partition by product 
                 order by date rows
                 rows between unbounded preceding and unbounded following) as last_price
           count(*) over (partition by product) as maxrn
      from saleslist sl
      where datediff( month, date, getdate() ) < 2 
   )
select product, 
       (last_price - first_price) / first_price
from cte
where rownumber = 1;

Answer 3

您需要子查询的表别名。但是，查询过于复杂。最大行号是行的count(*)：

with cte as (   
      select sl.*, 
             row_number() over (partition by product order by date desc) as rownumber,
             count(*) over (partition by product) as maxrn
      from saleslist sl
      where datediff( month, date, getdate() ) < 2 
    )
select product, 
     (
     (max(case when rownumber = 1 then price end) - 
         max(case when rownumber = maxn then price)
        )  /
         max(case when rownumber = maxn then price end)
       )
from cte
group by product;