34,591
社区成员
发帖
与我相关
我的任务
分享
create table #t
(sale_rq datetime,
sale_sl int default 0
)
insert into #t
select '20050101',100
union all select '20050201',100
union all select '20050301',100
union all select '20050401',100
union all select '20060401',100
union all select '20060501',100
union all select '20060801',100
union all select '20070801',100
union all select '20070901',100
union all select '20080901',100
Select Case When (Grouping(convert(varchar(4),Sale_rq,120))=1) Then '总计' Else Isnull(convert(varchar(4),Sale_rq,120), '总计') End As 年度,
Case When (Grouping(convert(varchar(2),month(Sale_rq),0))=1) Then '小计' Else Isnull(convert(varchar(2),month(Sale_rq),0), '小计') End As 月份,
SUM(Sale_sl) 销售数量
from #t
where Sale_rq between '2005-04-06' and '2010-04-20' group by convert(varchar(4),Sale_rq,120),convert(varchar(2),month(Sale_rq),0) with rollup
drop table #t
select MONTH(Sale_rq) 年份 , sum(Sale_sl) 销售数量 from T_Sale where Sale_rq between '2005-04-06' and '2010-04-20' group by MONTH(Sale_rq)
select year(Sale_rq) 年份 , sum(Sale_sl) 销售数量 from T_Sale where Sale_rq between '2005-04-06' and '2010-04-20' group by year(Sale_rq)
不知道行不???select convert(varchar(7),getdate(),120)
/*
-------
2010-04
(所影响的行数为 1 行)
*/
--月份:
select convert(char(7),Sale_rq,23) 月份,sum(Sale_sl) 销售数量
from T_Sale
where Sale_rq between '2005-04-06' and '2010-04-20'
group by convert(char(7),Sale_rq,23)
--年份:
select year(Sale_rq) 年份,sum(Sale_sl) 销售数量
from T_Sale
where Sale_rq between '2005-04-06' and '2010-04-20'
group by year(Sale_rq)
--如果你需要存储过程,则如下,把时间做为参数传进去。
create procedure my_proc @dt1 datetime,@dt2 datetime
as
begin
select convert(varchar(7),Sale_rq,120) 月份 , sum(Sale_sl) 销售数量 from T_Sale where Sale_rq between @dt1 and @dt2 group by convert(varchar(7),Sale_rq,120)
select year(Sale_rq) 年份 , sum(Sale_sl) 销售数量 from T_Sale where Sale_rq between @dt1 and @dt2 group by year(Sale_rq)
end
go
exec my_proc '2005-04-06' , '2010-04-20'
select convert(varchar(7),Sale_rq,120) 月份 , sum(Sale_sl) 销售数量 from T_Sale where Sale_rq between '2005-04-06' and '2010-04-20' group by convert(varchar(7),Sale_rq,120)
select year(Sale_rq) 年份 , sum(Sale_sl) 销售数量 from T_Sale where Sale_rq between '2005-04-06' and '2010-04-20' group by year(Sale_rq)