sql查询累加统计问题

[saillovemeng]

一个表T
id month Name re
1 1 a 10
2 2 a 20
3 3 a 30
4 1 b 5
5 2 b 5
6 3 b 5
7 7 b 5
8 8 b 5
9 9 b 5
10 1 a 10
11 2 a 20
我想得到的结果是,要按name和month分组
name month re
a 1 20--由1月到这个月的总数
a 2 60--由1月到这个月的总数
a 3 90 --由1月到这个月的总数
b 1 5--由1月到这个月的总数
b 2 10 --由1月到这个月的总数
b 3 15 --由1月到这个月的总数
b 7 20 --由1月到这个月的总数
b 8 25 --由1月到这个月的总数
b 9 30 --由1月到这个月的总数

[昵称被占用了]

a的数据列错了

select name,[month],re=(
   select sum(re) from t where name=a.name and [month]<= a.[month]
   )
from t a

[csdyyr]

DECLARE @TB TABLE(id INT,  month INT,Name VARCHAR(2),  re INT)
INSERT @TB
SELECT 1,  1,  'a',  10 UNION ALL 
SELECT 2,  2,  'a',  20 UNION ALL 
SELECT 3,  3,  'a',  30 UNION ALL 
SELECT 4,  1,  'b',  5 UNION ALL 
SELECT 5,  2,  'b',  5 UNION ALL 
SELECT 6,  3,  'b',  5 UNION ALL 
SELECT 7,  7,  'b',  5 UNION ALL 
SELECT 8,  8,  'b',  5 UNION ALL 
SELECT 9,  9,  'b',  5 UNION ALL 
SELECT 10,  1,  'a',  10 UNION ALL 
SELECT 11,  2,  'a',  20

SELECT MONTH,NAME,RE=(SELECT SUM(RE) FROM @TB WHERE NAME=A.NAME AND MONTH<=A.MONTH)FROM @TB AS A GROUP BY MONTH,NAME
ORDER BY NAME,MONTH
/*
MONTH       NAME RE          
----------- ---- ----------- 
1           a    20
2           a    60
3           a    90
1           b    5
2           b    10
3           b    15
7           b    20
8           b    25
9           b    30
*/

[saillovemeng]

谢谢。就是想得到a的数据是上面的样式，
因为1月a的数据是第1条记录和第10条记录的和，2月的a的数据是1，2，10，11的和

[StrutHibernate]

UP

[fa_ge]

with cte as
(select name , month,SUM(RE)re from # group by name , month)
select name , month , (select SUM(re) from # where Name =a.Name and month <=a.month )re 
from cte  a
/*
name       month       re
---------- ----------- -----------
a          1           20
a          2           60
a          3           90
b          1           5
b          2           10
b          3           15
b          7           20
b          8           25
b          9           30

(9 行受影响)
*/

[saillovemeng]

Thank you！

[StrutHibernate]

DECLARE @TB TABLE(id INT,  month INT,Name VARCHAR(2),  re INT)
INSERT @TB
SELECT 1,  1,  'a',  10 UNION ALL 
SELECT 2,  2,  'a',  20 UNION ALL 
SELECT 3,  3,  'a',  30 UNION ALL 
SELECT 4,  1,  'b',  5 UNION ALL 
SELECT 5,  2,  'b',  5 UNION ALL 
SELECT 6,  3,  'b',  5 UNION ALL 
SELECT 7,  7,  'b',  5 UNION ALL 
SELECT 8,  8,  'b',  5 UNION ALL 
SELECT 9,  9,  'b',  5 UNION ALL 
SELECT 10,  1,  'a',  10 UNION ALL 
SELECT 11,  2,  'a',  20

SELECT NAME,MONTH,

RE=(SELECT SUM(RE) FROM @TB WHERE NAME=A.NAME AND MONTH between 1 and A.MONTH) FROM @TB AS A GROUP BY MONTH,NAME

ORDER BY NAME,MONTH

/*
MONTH       NAME RE          
----------- ---- ----------- 
1           a    20
2           a    60
3           a    90
1           b    5
2           b    10
3           b    15
7           b    20
8           b    25
9           b    30
*/

[StrutHibernate]

如果month是smalldatetime类型的

DECLARE @TB TABLE(id INT identity(1,1),  [date] smalldatetime,Name VARCHAR(2), number INT)
INSERT @TB
SELECT  '2005-1-18',  'a', 10  UNION ALL 
SELECT  '2005-8-26',  'b', 20  UNION ALL 
SELECT  '2006-5-22',  'a', 30  UNION ALL 
SELECT  '2007-4-10',  'b', 5   UNION ALL 
SELECT  '2007-1-23',  'b', 5   UNION ALL 
SELECT  '2007-10-15', 'c', 5   UNION ALL 
SELECT  '2007-10-19', 'c', 5   UNION ALL 
SELECT  '2008-10-24', 'c', 5   UNION ALL 
SELECT  '2008-10-29', 'b', 5   UNION ALL 
SELECT  '2008-10-30', 'a', 10  UNION ALL 
SELECT  '2008-10-31', 'c', 20


/*所要得到的结果是这样的怎么写?
id        date        name    number          
----------------------------------------
1      '2005-1-18'      a       10
2      '2006-5-22'      a       40
3      '2008-10-30'     a       50
4      '2005-8-26'      b       20
5      '2007-4-10'      b       25
6      '2007-1-23'      b       30
7      '2008-10-29'     b       35
8      '2007-10-15'     c       5
9      '2007-10-19'     c       10
10     '2008-10-24'     c       15
11     '2008-10-31'     c       35

*/