问题描述:

How would you do to transform a Column in a table from this:

ColumnA ColumnB

2 a

3 b

4 c

5 d

1 a

to this:

ColumnA ColumnB

3 a

6(=3+3) b

10(=4+3+3) c

15(=5+4+3+3) d

I'm interested to see esp. what method you would pick.

网友答案:

Like this:

;WITH cte
AS
(
   SELECT ColumnB, SUM(ColumnA) asum 
   FROM @t 
   gROUP BY ColumnB

), cteRanked AS
(
   SELECT asum, ColumnB, ROW_NUMBER() OVER(ORDER BY ColumnB) rownum
   FROM cte
) 
SELECT (SELECT SUM(asum) FROM cteRanked c2 WHERE c2.rownum <= c1.rownum),
  ColumnB
FROM cteRanked c1;

This should give you:

ColumnA    ColumnB
3             a
6             b
10            c
15            d

Here is a live demo

网友答案:

I'd generally avoid trying to do so, but the following matches what you've asked for:

declare @T table (ColumnA int,ColumnB char(1))
insert into @T(ColumnA,ColumnB) values
(2    ,       'a'),
(3   ,        'b'),
(4  ,         'c'),
(5 ,          'd'),
(1,           'a')

;With Bs as (
    select distinct ColumnB from @T
)
select
    SUM(t.ColumnA),b.ColumnB
from
    Bs b
        inner join
    @T t
        on
            b.ColumnB >= t.ColumnB
group by
    b.ColumnB

Result:

            ColumnB
----------- -------
3           a
6           b
10          c
15          d

For small data sets, this will be fine. But for larger data sets, note that the last row of the table relies on obtaining the SUM over the entire contents of the original table.

网友答案:

Not sure if this is optimal, but how about (SQL Fiddle):

SELECT x.A + COALESCE(SUM(y.A),0) ColumnA, x.ColumnB
FROM
(
    SELECT SUM(ColumnA) A, ColumnB
    FROM myTable
    GROUP BY ColumnB
) x
LEFT OUTER JOIN
(
    SELECT SUM(ColumnA) A, ColumnB
    FROM myTable
    GROUP BY ColumnB
) y ON y.ColumnB < x.ColumnB
GROUP BY x.ColumnB, x.A
网友答案:
create table #T
(
  ID int primary key,
  ColumnA int,
  ColumnB char(1)
);

insert into #T
select row_number() over(order by ColumnB),
       sum(ColumnA) as ColumnA,
       ColumnB
from YourTable
group by ColumnB;

with C as
(
  select ID,
         ColumnA,
         ColumnB
  from #T
  where ID = 1
  union all
  select T.ID,
         T.ColumnA + C.ColumnA,
         T.ColumnB
  from #T as T
    inner join C
      on T.ID = C.ID + 1
)
select ColumnA,
       ColumnB 
from C
option (maxrecursion 0);

drop table #T;
网友答案:

You can use below simple select statement for the same

SELECT COLUMN_A, COLUMN_B, 
(SELECT SUM(COLUMN_B) FROM #TBL T2 WHERE T2.ID  <= T1.ID) as SumofPreviousRow FROM #TBL T1;
相关阅读:
Top