如何在当前表中引用窗口函数

Question

我在一个更大的查询中有这个部分，它消耗了很多RAM：

TopPerPost as
(
   select Id,
      CloseReasonTypeId,
      Name,
      ReasonsPerPost.TotalByCloseReason,
      row_number() over(partition by Id order by TotalByCloseReason desc) seq -- Get the most common Id (The most common close Reason)
   from ReasonsPerPost
   where Name is NOT NULL and TopPerPost.seq=1 -- Remove useless results here, instead of doing it later
)

但我得到了无法绑定多部分标识符"TopPerPost.seq">
最后一个详细信息。。。我只使用该表稍后的INNER JOIN中的Name列。

Answer 1

不能在同一查询的where中引用窗口函数。只需创建第二个cte。

with TopPerPost as
(
   select Id,
      CloseReasonTypeId,
      Name,
      ReasonsPerPost.TotalByCloseReason,
      row_number() over(partition by Id order by TotalByCloseReason desc) seq -- Get the most common Id
   from ReasonsPerPost
   where Name is NOT NULL 
)
, OnlyTheTop as
(
    select *
    from TopPerPost
    where seq = 1
)

或者你可以这样做。

select * from 
(
   select Id,
      CloseReasonTypeId,
      Name,
      ReasonsPerPost.TotalByCloseReason,
      row_number() over(partition by Id order by TotalByCloseReason desc) seq -- Get the most common Id
   from ReasonsPerPost
   where Name is NOT NULL 
) s
where seq = 1

这是另一个选项，应该可以消除返回这么多行的需要。

select Id,
      CloseReasonTypeId,
      Name,
      s.TotalByCloseReason
   from ReasonsPerPost rpp
   cross apply
   (
        select top 1 TotalByCloseReason
        from ReasonsPerPost rpp2
        where rpp2.Id = rpp.Id
        order by TotalByCloseReason desc
   ) s
   where Name is NOT NULL 

尝试#4…如果使用sql fiddle，这会容易得多。

select Id,
      CloseReasonTypeId,
      Name,
      s.TotalByCloseReason
   from ReasonsPerPost rpp
   inner join
   (
        select top 1 TotalByCloseReason
        from ReasonsPerPost rpp2
        where rpp2.Id = rpp.Id
        and Name is NOT NULL
        order by TotalByCloseReason desc
   ) s on s.Id = rpp.Id
   where Name is NOT NULL

Answer 2

以下内容可能适合您的需要。但如果不看数据，很难说它会不会。

;with t as
(
  Select Id, max(totalbyclosereason) TC from reasonsperpost where name is not null group by id
)
Select T.id,t.tc,c.closereasontypeid,c.name 
From t join reasonsperpost c on t.id = c.id and t.tc = c.totalbyclosereason