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
*/
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)
(所影响的行数为 11 行)sumsl
----------- -----------
500 0
1100 1
800 2
1100 3(所影响的行数为 4 行)
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 2,100,'2007-02-01' union
select 3,200,'2007-03-01' union
select 4,300,'2007-04-01' union
select 5,200,'2007-05-01' union
select 6,600,'2007-06-01' union
select 7,200,'2007-07-01' union
select 8,400,'2007-08-01' union
select 9,200,'2007-09-01' union select 10,900,'2007-10-01' union
select 11,200,'2007-11-01'
declare @i int,@j int
set @i = 0
set @j = 0
update @t
set id = @i,@i = case when @j%3 = 0 then @i+ 1 else @I end ,@j = @j + 1select sum(sl) as sumsl,id
from @t
group by id
/*
sumsl id
----------- -----------
600 1
1000 2
1500 3
200 4*/
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 行)
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
如果第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 )