TSQL-2008 SUM(X)结束(部分...按条款排序) [英] TSQL-2008 SUM(X) OVER (PARTITION ... ORDER BY CLAUSE)

问题描述

我真的需要将CTE的结果取走，然后计算出各组数据的累积值.

I really need to take what I have as a result of a CTE, and calculate the cummulative value of groups of data.

数据集为:

PERIOD  FT  GROUP   DEPT    VALUE
1   Actual  KINDER  MATH    200
2   Actual  KINDER  MATH    363
3   Actual  KINDER  MATH    366
1   Budget  KINDER  MATH    457
2   Budget  KINDER  MATH    60
3   Budget  KINDER  MATH    158
1   Actual  HIGHSCH ENGLISH 456
2   Actual  HIGHSCH ENGLISH 745
3   Actual  HIGHSCH ENGLISH 125
1   Budget  HIGHSCH ENGLISH 364
2   Budget  HIGHSCH ENGLISH 158
3   Budget  HIGHSCH ENGLISH 200
6   Budget  HIGHSCH ENGLISH 502
7   Budget  HIGHSCH ENGLISH 650
1   Actual  COLL    ENGLISH 700
2   Actual  COLL    ENGLISH 540
3   Actual  COLL    ENGLISH 160
1   Budget  COLL    ENGLISH 820
2   Budget  COLL    ENGLISH 630
3   Budget  COLL    ENGLISH 800

我想要的是另外一列，其中包含每个FT，组和部门的累计金额.

What I want is an additional column that holds the cummulative amount for each FT, Group, Dept.

所以基本上，我希望它看起来像这样:

So basically, I would like it to look like this:

PERIOD  FT  GROUP   DEPT    VALUE   ACC VALUE   
1   Actual  KINDER  MATH    200 200 
2   Actual  KINDER  MATH    363 563 
3   Actual  KINDER  MATH    366 929 
1   Budget  KINDER  MATH    457 457 
2   Budget  KINDER  MATH    60  517 
3   Budget  KINDER  MATH    158 675 
1   Actual  HIGHSCH ENGLISH 456 456 
2   Actual  HIGHSCH ENGLISH 745 1201    
3   Actual  HIGHSCH ENGLISH 125 1326    
1   Budget  HIGHSCH ENGLISH 364 364 
2   Budget  HIGHSCH ENGLISH 158 522 
3   Budget  HIGHSCH ENGLISH 200 722 
1   Budget  HIGHSCH ENGLISH 502 502 
2   Budget  HIGHSCH ENGLISH 650 1152    
3   Budget  HIGHSCH ENGLISH 336 1488    
1   Actual  COLL    ENGLISH 700 700 
2   Actual  COLL    ENGLISH 540 1240    
3   Actual  COLL    ENGLISH 160 1400    
1   Budget  COLL    ENGLISH 820 820 
2   Budget  COLL    ENGLISH 630 1450    
3   Budget  COLL    ENGLISH 800 2250    

如果我使用的是SQL 2012，我会使用类似于以下内容的东西:

If I was in SQL 2012, I'd use something similar to this:

SELECT   period
    ,ft
    ,group
    ,dept
    ,value
    ,CASE
        WHEN FT = 'Actual' THEN SUM(value) OVER (PARTITION BY dept, group, ft ORDER BY period)
        ELSE value
        END AS AccValue 
FROM myTable

但是，我在2008年，无法使用此方法，因此对如何为我的数据复制此方法感到困惑.

However, I am on 2008 and can't use this method and am stumped on how to replicate this for my data.

任何人都可以帮忙吗?

推荐答案

简单的INNER JOIN应该可以解决问题.除非我误解了您，否则您想要的是总金额，对吗?

Simple INNER JOIN should do the trick. Unless I'm misunderstanding you, what you want is a running total, correct?

此示例创建一个包含虚拟数据的虚拟表，然后使用内部联接作为运行总计.从性能的角度来看，公用表表达式可能更有效.但是为了简单起见，内部连接是我的优先选择.

This example creates a dummy table with dummy data, then uses an inner join for the running total. From a performance standpoint, the Common Table Expression is likely more efficient. But for simplicity, the inner join my be preferential.

/* Dummy table */    

create table testing1
(col1 int not null identity(1,1),
col2 varchar(5),
col3 int)


insert into testing1
values ('a', 10), ('a', 20), ('a', 30), ('b', 40), ('b', 50)

/* Running total example */

SELECT a.col1
           , a.col2
           , a.col3
           , SUM(b.col3) AS total

FROM testing1 a INNER JOIN testing1 b
     ON  a.col1 >= b.col1
     AND a.col2 = b.col2

GROUP BY a.col1, a.col2, a.col3
ORDER BY a.col1



/* Edit to include Output */
col1    col2    col3    total
1   a   10  10
2   a   20  30
3   a   30  60
4   b   40  40
5   b   50  90

这篇关于TSQL-2008 SUM(X)结束(部分...按条款排序)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！