关于数据库SQL语句的小问题？

[Lion_sj]

例如有表如下：

year month count1
2001 1 100
2001 2 200
2002 1 400
2002 2 100
2002 5 300

现在我想得到这样的表
year month count1 count2 count3
2001 1 100 300 300
2001 2 200 300 300
2002 1 400 500 800
2002 2 100 500 800
2002 5 300 300 800
count2是季度总和，count3是年度总和

请问如何用SQL语句得到？各路大虾帮帮忙

[windindance]

select year, month, count1,
count2 = (select sum(count1) from table b where a.year = b.year and a.month %3 = b.month %3),
count3 = (select sum(count1) from table b where a.year = b.year)
from table a

[Lion_sj]

to windindance(风之舞)　

是从表a得到表b呀

[kuangning]

SELECT a.year, a.Month, (SELECT Sum(k.count) from b as k where (1<=k.month) and (k.month<=3) and (a.year=k.year) ) AS 第一季度, (SELECT Sum(k.count) from b as k where (4<=k.month) and (k.month<=6)and (a.year=k.year) ) AS 第二季度, (SELECT Sum(k.count) from b as k where (7<=k.month) and (k.month<=9) and (a.year=k.year)) AS 第三季度, (SELECT Sum(k.count) from b as k where (10<=k.month) and (k.month<=12) and (a.year=k.year)) AS 第四季度, (SELECT Sum(k.count) from b as k where (a.year=k.year) ) AS 全年
FROM b AS a;

结果：为
year month 第一季度 第二季度 第三季度 第四季度 全年

不是很好吗？

[kuangning]

SELECT a.year, a.month,a.count, (select sum(count) from test b where a.year = b.year and (a.month / 3) = (b.month / 3)) AS count1, (select sum(count) from test b where a.year = b.year) AS count2
FROM test AS a;


注：表名称为“test”

可以得到你的结果！

[zng]

写一个存储过程，就可以了。