SQL Server 2014 - SSRS 不支持 OVER SQL 构造

Question

我有一个查询，我正在尝试在 SSRS 中创建数据集，但我收到一个错误说，

The OVER SQL construct or statement is not supported.

我使用的查询如下：

SELECT AM, REP, PRIM_SPEC, SUM(TOT_CALL)
FROM (
       SELECT AM, REP, SUM(TOT_CALL) as TOT_CALL, 
       CASE 
           WHEN ROW_NUMBER() OVER (PARTITION BY REP ORDER BY SUM(TOT_CALL) DESC) > 5 
           THEN 'Other'
           ELSE prim_spec
       END AS prim_spec
       FROM DEMO_CALL
       WHERE PERIOD >= @Mese
       AND (REP IN (@REP)) 
       AND (AM = @AM)
       GROUP BY AM, REP, prim_spec
) A
GROUP BY AM, REP, PRIM_SPEC
ORDER BY 1,2,4 DESC

如何使用我的 OVER SQL 构造来解决此问题？

Answer 1

这不是 SSRS 的问题，而是 SQL 语句本身的问题。在生成ROW_NUMBER之前，您无法对其进行比较。试试这个：

SELECT distinct AM, REP, TOT_CALL, 
CASE 
   WHEN RN > 5 
   THEN 'Other'
   ELSE prim_spec
  END AS prim_spec
FROM (
 SELECT AM, REP, SUM(TOT_CALL) as TOT_CALL, ROW_NUMBER() OVER (PARTITION BY REP ORDER BY SUM(TOT_CALL) DESC) as RN
 FROM DEMO_CALL
 WHERE PERIOD >= @Mese
   AND (REP IN (@REP)) 
   AND (AM = @AM)
 GROUP BY AM, REP
) DEMO_CALL
GROUP BY AM, REP, prim_spec
ORDER BY 1,2,4 DESC