统计季度的SQL:
--
统计第一季度的数据,FestivaStartlTime字段是Datetime类型.
select * from
( select Quarter = datename (quarter,FestivaStartlTime), * from Festival) as quarterFestival
where Quarter = 1
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
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