--示例数据
create table tb(riqi datetime,shul int)
insert tb select '2004-08-06',7
union all select '2004-08-07',4
union all select '2004-09-07',4
union all select '2004-09-08',2
union all select '2003-07-09',10
union all select '2003-09-08',6
go
--统计
select 日期=case
when grouping(年)=1 then '总计'
when grouping(月)=1 then 年+'年合计'
when grouping(日)=1 then 年+'年'+月+'月小计'
else 日 end
,数量=sum(shul)
from(
select 年=datename(year,riqi)
,月=datename(month,riqi)
,日=convert(char(10),riqi,120),shul
from tb
)a group by 年,月,日 with rollup
go
补充上面的:
如果同时统计:
select convert (char(7),日期,120) as 年月份 ,cast (sum(日销售量) as varchar(10)) 各月累计 from tn group by convert (char(7),日期,120)
union all
select '年份','各年累计'
union all
select cast(year(日期)as varchar(10)) ,cast(sum(日销售量) as varchar(10)) from tn group by cast(year(日期) as varchar(10))
union all
select '日统计','日累计'
union all
select '日统计',cast(sum(日销售量) as varchar(10)) from tn
create table #t (日期 datetime,日销售量 int)
insert into #t
select '2004-08-06',7
union select '2004-08-07',4
union select '2004-09-07',4
union select '2004-09-08',2
union select '2003-07-09',10
union select '2003-09-08',6
union select '2004-08-09',4
union select '2004-09-09',4
union select '2004-09-09',2
union select '2003-07-04',10
union select '2003-09-02',6
select 日期, convert (char(7),日期,120) as 年月份, sum(日销售量) 各月累计 from #t
group by convert (char(7),日期,120),日期 with rollup
----日
select convert (varchar (4),riqi,120)+'年'+substring(convert (varchar (10),riqi,120),6,2)+'月'+
substring(convert (varchar (10),riqi,120),9,2)+'日' as riqi,sum(shul) as shul
from tn
group by convert (varchar (4),riqi,120)+'年'+substring(convert (varchar (10),riqi,120),6,2)+'月'+
substring(convert (varchar (10),riqi,120),9,2)+'日'
-----月
union all
select convert (varchar (4),riqi,120)+'年'+substring(convert (varchar (10),riqi,120),6,2)+'月' as riqi,sum(shul) as shul
from tn
group by convert (varchar (4),riqi,120)+'年'+substring(convert (varchar (10),riqi,120),6,2)+'月'
-----年
union all
select convert (varchar (4),riqi,120)+'年'as riqi,sum(shul) as shul
from tn
group by convert (varchar (4),riqi,120)+'年'
order by riqi
insert into tn select '2004-08-06',7
union select '2004-08-07',4
union select '2004-09-07',4
union select '2004-09-08',2
union select '2003-07-09',10
union select '2003-09-08',6
----日
select convert (varchar (4),riqi,120)+'年'+substring(convert (varchar (10),riqi,120),6,2)+'月'+
substring(convert (varchar (10),riqi,120),8,2)+'日' as riqi,sum(shul) as shul
from tn
group by convert (varchar (4),riqi,120)+'年'+substring(convert (varchar (10),riqi,120),6,2)+'月'+
substring(convert (varchar (10),riqi,120),8,2)+'日'
-----月
union all
select convert (varchar (4),riqi,120)+'年'+substring(convert (varchar (10),riqi,120),6,2)+'月' as riqi,sum(shul) as shul
from tn
group by convert (varchar (4),riqi,120)+'年'+substring(convert (varchar (10),riqi,120),6,2)+'月'
-----年
union all
select convert (varchar (4),riqi,120)+'年'as riqi,sum(shul) as shul
from tn
group by convert (varchar (4),riqi,120)+'年'
order by riqi
不知道你的表是怎么样的,下面自己做了一个例子:
create table tn (日期 datetime,日销售量 int)
insert into tn select '2004-08-06',7
union select '2004-08-07',4
union select '2004-09-07',4
union select '2004-09-08',2
union select '2003-07-09',10
union select '2003-09-08',6
--月份来分
select convert (char(7),日期,120) as 年月份 ,sum(日销售量) 各月累计 from tn group by convert (char(7),日期,120)
--年份来分
--select year(日期) as 年份, sum(日销售量) 各年累计 from tn group by year(日期)