一个复杂的查询结果，不知道能否用SQL写出来，谢谢了- 统计每季度的累加值。
表结构里的数据，是安月存放，每个月1条记录，现在要统计，每个季度的数据，就是累加3个月的值，这个sql要怎么写？？
id value time
1 11 2007.01.01
2 12 2007.02.01
3 13 2007.03.01
..............
就是累加每3个月，value的值。
[解决办法]
declare @t table(id int,sl int,d datetime)
insert @t select 1,200, '2007-01-01 ' union
select 1,100, '2007-02-01 ' union
select 1,200, '2007-03-01 ' union
select 1,300, '2007-04-01 ' union
select 1,200, '2007-05-01 ' union
select 1,600, '2007-06-01 ' union
select 1,200, '2007-07-01 ' union
select 1,400, '2007-08-01 ' union
select 1,200, '2007-09-01 ' union
select 1,900, '2007-10-01 ' union
select 1,200, '2007-11-01 '
select sum(sl) as sumsl, datepart(quarter,d) as jd
from @t
group by datepart(quarter,d)
/*
sumsl jd
----------- -----------
500 1
1100 2
800 3
1100 4
*/
[解决办法]
declare @t table(id int,sl int,d datetime)
insert @t select 1,200, '2007-01-01 ' union
select 1,100, '2007-02-01 ' union
select 1,200, '2007-03-01 ' union
select 1,300, '2007-04-01 ' union
select 1,200, '2007-05-01 ' union
select 1,600, '2007-06-01 ' union
select 1,200, '2007-07-01 ' union
select 1,400, '2007-08-01 ' union
select 1,200, '2007-09-01 ' union
select 1,900, '2007-10-01 ' union
select 1,200, '2007-11-01 '
select sum(sl) as sumsl,DATEDIFF (qq ,DATEADD(yy,DATEDIFF(yy,1,d),0) ,d)
from @t
group by DATEDIFF (qq ,DATEADD(yy,DATEDIFF(yy,1,d),0) ,d)
[解决办法]
create table test(ids int,value int,times datetime)
insert into test values(1,11, '2007.01.01 ')
insert into test values(2,12, '2007.02.01 ')
insert into test values(3,13, '2007.03.01 ')
insert into test values(4,11, '2007.04.01 ')
insert into test values(5,11, '2007.05.01 ')
insert into test values(6,11, '2007.06.01 ')
insert into test values(7,11, '2007.07.01 ')
insert into test values(8,12, '2007.08.01 ')
insert into test values(9,13, '2007.09.01 ')
select sum(value) from test group by DATEPART ( qq, times )
drop table test
[解决办法]
declare @t table(id int,sl int,d datetime)
insert @t select 1,200, '2007-01-01 ' union
select 1,100, '2007-02-01 ' union
select 1,200, '2007-03-01 ' union
select 1,300, '2007-04-01 ' union
select 1,200, '2007-05-01 ' union
select 1,600, '2007-06-01 ' union
select 1,200, '2007-07-01 ' union
select 1,400, '2007-08-01 ' union
select 1,200, '2007-09-01 ' union
select 1,900, '2007-10-01 ' union
select 1,200, '2007-11-01 '
select sum(sl) as sumsl, datepart(quarter,dateadd(mm,-1,d)) as jd
from @t
group by datepart(quarter,dateadd(mm,-1,d)),datepart(yy,dateadd(mm,-1,d))
（所影响的行数为 11 行）
sumsl jd
----------- -----------
200 4
600 1
1000 2
1500 3
200 4
（所影响的行数为 5 行）
[解决办法]
create table test(ids int,value int,times datetime)
insert into test values(1,10, '2007.01.01 ')
insert into test values(2,12, '2007.02.01 ')
insert into test values(3,13, '2007.03.01 ')
insert into test values(4,11, '2007.04.01 ')
insert into test values(5,11, '2007.05.01 ')
insert into test values(6,11, '2007.06.01 ')
insert into test values(7,19, '2007.07.01 ')
insert into test values(8,12, '2007.08.01 ')
insert into test values(9,13, '2007.09.01 ')
insert into test values(10,13, '2007.10.01 ')
insert into test values(11,13, '2007.11.01 ')
insert into test values(12,13, '2007.12.01 ')
select sum(case when ids in(2,3,4) then value end),sum(case when ids in(5,6,7) then value end),sum(case when ids in(8,9,10) then value end),sum(case when ids in(11,12,1) then value end) from test
--select sum(value) from test group by DATEPART ( qq, times )
drop table test
[解决办法]
duibudui() ( ) 信誉：100 Blog 加为好友 2007-06-06 10:59:44 得分: 0
如果第1季度从2月开始，就是2，3，4月累加，5，6，7累加，依次。要怎么改？
---------------------------------------------
select sum(case when ids in(2,3,4) then value end),sum(case when ids in(5,6,7) then value end),sum(case when ids in(8,9,10) then value end),sum(case when ids in(11,12,1) then value end) from test
--===================================================
--如果第1季度从1月开始
select sum(value) from test group by DATEPART ( qq, times )