使用CTE提供累计总计

Question

我有张桌子上有几个名字：

SELECT * FROM d;

Forename
--------------------------------
Robert
Susan
Frances
Kate
May
Alex
Anna

我想按字母顺序提取姓名长度的累计总和。到目前为止，我已经：

WITH    Names ( RowNum, Forename, ForenameLength )
          AS ( SELECT   ROW_NUMBER() OVER ( ORDER BY forename ) AS RowNum ,
                        Forename ,
                        LEN(forename) AS ForenameLength
               FROM     d
             )
    SELECT  RowNum ,
            Forename ,
            ForenameLength ,
            ISNULL(ForenameLength + ( SELECT    ISNULL(SUM(ForenameLength),0)
                                      FROM      Names
                                      WHERE     RowNum < n.RowNum
                                    ), 0) AS CumLen
    FROM    NAMES n;

RowNum               Forename                         ForenameLength CumLen
-------------------- -------------------------------- -------------- -----------
1                    Alex                             4              4
2                    Anna                             4              8
3                    Frances                          7              15
4                    Kate                             4              19
5                    May                              3              22
6                    Robert                           6              28
7                    Susan                            5              33

但我知道，在CTE内应该可以(递归地)做到这一点。有人知道这是怎么实现的吗？

注:虽然我们正在开发2012年，目前的直播系统是2008年，所以任何解决方案都需要向后兼容，至少在短期内。

Answer 1

您在Server 2012上，应该使用sum() over()。

select row_number() over(order by d.Forename) as RowNum,
       d.Forename,
       len(d.Forename) as ForenameLength,
       sum(len(d.Forename)) over(order by d.Forename rows unbounded preceding) as CumLen
from d
order by d.Forename;

结果：

RowNum   Forename     ForenameLength CumLen
-------- ------------ -------------- -----------
1        Alex         4              4
2        Anna         4              8
3        Frances      7              15
4        Kate         4              19
5        May          3              22
6        Robert       6              28
7        Susan        5              33

更新：

如果出于某种原因，您绝对想要一个递归版本，它可能如下所示：

with C as
(
  select top(1)
    1 as RowNum,
    d.Forename,
    len(d.Forename) as ForenameLength,
    len(d.Forename) as CumLen
  from d
  order by d.Forename      
  union all
  select d.RowNum,
         d.Forename,
         d.ForenameLength,
         d.CumLen      
  from (
       select C.RowNum + 1 as RowNum,
              d.Forename,
              len(d.Forename) as ForenameLength,
              C.CumLen + len(d.Forename) as CumLen,
              row_number() over(order by d.ForeName) as rn
       from d
         inner join C
           on C.Forename < d.Forename
       ) as d
  where d.rn = 1
)
select C.RowNum,
       C.Forename,
       C.ForenameLength,
       C.CumLen
from C;

改编自性能调优整个查询计划由保罗怀特。