time count
2007-01-22 00:00:00.000 300
2007-01-23 00:00:00.000 400
2007-05-24 00:00:00.000 200
2007-08-25 00:00:00.000 100
2007-08-26 00:00:00.000 200
2008-01-27 00:00:00.000 100
2008-01-28 00:00:00.000 100 输出结果2007第一季度
2007第二季度
2007第三季度
2007第四季度
2008第一季度
2008第二季度
2007-01-22 00:00:00.000 300
2007-01-23 00:00:00.000 400
2007-05-24 00:00:00.000 200
2007-08-25 00:00:00.000 100
2007-08-26 00:00:00.000 200
2008-01-27 00:00:00.000 100
2008-01-28 00:00:00.000 100 输出结果2007第一季度
2007第二季度
2007第三季度
2007第四季度
2008第一季度
2008第二季度
from tb
group by datename(yy,time) + datepart(qq,time)
insert into tb values('2007-01-22 00:00:00.000', 300 )
insert into tb values('2007-01-23 00:00:00.000', 400 )
insert into tb values('2007-05-24 00:00:00.000', 200 )
insert into tb values('2007-08-25 00:00:00.000', 100 )
insert into tb values('2007-08-26 00:00:00.000', 200 )
insert into tb values('2008-01-27 00:00:00.000', 100 )
insert into tb values('2008-01-28 00:00:00.000', 100 )
go
select cast(datename(yy,time) as varchar) + cast(datepart(qq,time) as varchar) 季度, sum(count) count
from tb
group by cast(datename(yy,time) as varchar) + cast(datepart(qq,time) as varchar)drop table tb/*
季度 count
------------------------------------------------------------ -----------
20071 700
20072 200
20073 300
20081 200(所影响的行数为 4 行)
*/
left(convert(varchar(10),time,120),4)+case (DATEPART(mm,time)-1)/3
when 0 then '第一季度'
when 1 then '第二季度'
when 2 then '第三季度'
when 3 then '第四季度' end as [季度],
sum(count)
from tb
group by
left(convert(varchar(10),time,120),4)+case (DATEPART(mm,time)-1)/3
when 0 then '第一季度'
when 1 then '第二季度'
when 2 then '第三季度'
when 3 then '第四季度' end
insert into tb values('2007-01-22 00:00:00.000', 300 )
insert into tb values('2007-01-23 00:00:00.000', 400 )
insert into tb values('2007-05-24 00:00:00.000', 200 )
insert into tb values('2007-08-25 00:00:00.000', 100 )
insert into tb values('2007-08-26 00:00:00.000', 200 )
insert into tb values('2008-01-27 00:00:00.000', 100 )
insert into tb values('2008-01-28 00:00:00.000', 100 )
goselect 季度,[count] = sum(count) from
(
select cast(datename(yy,time) as varchar) +
case datepart(qq,time)
when 1 then '第一季度'
when 2 then '第二季度'
when 3 then '第三季度'
when 4 then '第四季度'
end as 季度
, count
from tb
) t
group by 季度drop table tb/*
季度 count
-------------------------------------- -----------
2007第二季度 200
2007第三季度 300
2007第一季度 700
2008第一季度 200(所影响的行数为 4 行)
*/
insert into tb values('2007-01-22 00:00:00.000', 300 )
insert into tb values('2007-01-23 00:00:00.000', 400 )
insert into tb values('2007-05-24 00:00:00.000', 200 )
insert into tb values('2007-08-25 00:00:00.000', 100 )
insert into tb values('2007-08-26 00:00:00.000', 200 )
insert into tb values('2008-01-27 00:00:00.000', 100 )
insert into tb values('2008-01-28 00:00:00.000', 100 )
goselect cast(datename(yy,time) as varchar) +
case datepart(qq,time)
when 1 then '第一季度'
when 2 then '第二季度'
when 3 then '第三季度'
when 4 then '第四季度'
end as 季度
, [count] = sum([count])
from tb
group by cast(datename(yy,time) as varchar) +
case datepart(qq,time)
when 1 then '第一季度'
when 2 then '第二季度'
when 3 then '第三季度'
when 4 then '第四季度'
enddrop table tb/*
季度 count
-------------------------------------- -----------
2007第二季度 200
2007第三季度 300
2007第一季度 700
2008第一季度 200(所影响的行数为 4 行)
*/
when month(date) in(4,5,6) then cast(year(date) as varchar)+'第二季度'
when month(date) in(7,8,9) then cast(year(date) as varchar)+'第三季度'
else cast(year(date) as varchar)+'第四季度'
end as quarte,
sum(tb.count) as [count]
from
(select dateadd(day,t1.id+t2.id+t3.id+t4.id+t5.id+t6.id+t7.id+t8.id+t9.id+t10.id,'2007-01-01') as date
from (select 0 as id union all select 1) t1,
(select 0 as id union all select 2) t2,
(select 0 as id union all select 4) t3,
(select 0 as id union all select 8) t4,
(select 0 as id union all select 16) t5,
(select 0 as id union all select 32) t6,
(select 0 as id union all select 64) t7,
(select 0 as id union all select 128) t8,
(select 0 as id union all select 256) t9,
(select 0 as id union all select 512) t10) t inner join tb on t.date=tb.time
group by case when month(date) in(1,2,3) then cast(year(date) as varchar)+'第一季度'
when month(date) in(4,5,6) then cast(year(date) as varchar)+'第二季度'
when month(date) in(7,8,9) then cast(year(date) as varchar)+'第三季度'
else cast(year(date) as varchar)+'第四季度'
end