sql 季度统计

统计季度的SQL:

--统计第一季度的数据,FestivaStartlTime字段是Datetime类型.
select * from
(select Quarter=datename(quarter,FestivaStartlTime),* from Festival) as quarterFestival
where Quarter=1

 

 

示例:

use pubs
select * from sale
insert into sale values('面包','食品',500,'2009-01-01')
insert into sale values('面包','食品',500,'2009-02-01')
insert into sale values('面包','食品',2000,'2009-04-01')
insert into sale values('面包','食品',3000,'2009-07-01')
insert into sale values('面包','食品',2000,'2009-10-01')
insert into sale values('核桃','食品',2000,'2009-11-01')
insert into sale values('轿车','汽车',2000,'2009-01-01')

 

select '年'=min(DATEPART(year, saleDate)),'季'=datename(quarter,saleDate),
'类型'=PCat,'本季销售量'=sum(pcount),'截止本季销售量'=sum(pcount)
from sale
group by datename(quarter,saleDate),PCat

--===============

select datename(quarter,saleDate) from sale

select * from sale


select a.*,
(
select sum(PCount) from sale where year(saledate)=a.年
 and PCat=a.类型 and datename(quarter,saleDate)<=a.季) as '截止本季销售量' from
(
select 
year(saledate) as '年',
datename(quarter,saleDate) as '季',
PCat as '类型',
sum(PCount) as '本季销售量'
from sale
group by year(saledate),datename(quarter,saleDate),pcat
) as a

order by 季 asc