select sum(销售金额) from 销售记录表 where convert(char(6),销售时间,112) between convert(char(6),dateadd(month,-3,getdate()),112) and convert(char(6),dateadd(month,-1,getdate()),112)
我写个简表,楼主看看吧CREATE TABLE [dbo].[testCount]( [ID] [int] IDENTITY(1,1) NOT NULL, [dt] [datetime] NULL,--时间 [money] [float] NULL --钱 ) insert into testCount values('2010-4-5',45.00) insert into testCount values('2010-4-22',33.00) insert into testCount values('2010-5-11',88.00) insert into testCount values('2010-5-12',66.00) insert into testCount values('2010-6-5',77.00) insert into testCount values('2010-7-5',100.00)select sum(money) from testCount where datediff(m,dt,getDate())<=3 结果为331 5,6,7月总值
比如说 现在是8月 我要求5,6,7月的数据 select Zonename,sum(Stock) from V_DealerStock_ByZone where convert(char(6),'201006',112) between convert(char(6),dateadd(mm,-3,getdate()),112) and convert(char(6),dateadd(mm,-1,getdate()),112) and Zonename<>'直供区' group by Zonename 但是这样报错
where convert(char(6),销售时间,112) between convert(char(6),dateadd(month,-3,getdate()),112) and convert(char(6),dateadd(month,-1,getdate()),112)
[ID] [int] IDENTITY(1,1) NOT NULL,
[dt] [datetime] NULL,--时间
[money] [float] NULL --钱
)
insert into testCount values('2010-4-5',45.00)
insert into testCount values('2010-4-22',33.00)
insert into testCount values('2010-5-11',88.00)
insert into testCount values('2010-5-12',66.00)
insert into testCount values('2010-6-5',77.00)
insert into testCount values('2010-7-5',100.00)select sum(money) from testCount where datediff(m,dt,getDate())<=3
结果为331 5,6,7月总值
select Zonename,sum(Stock)
from V_DealerStock_ByZone
where convert(char(6),'201006',112) between convert(char(6),dateadd(mm,-3,getdate()),112) and convert(char(6),dateadd(mm,-1,getdate()),112)
and Zonename<>'直供区'
group by Zonename
但是这样报错
201001 华北区 37035574.412133634
201004 华东区 127095354.35153391
201002 直供区 8794340.4645412285
201007 华西区 73367585.620399982
201003 华南区 200079998.59193698
200912 华东区 80388068.194803223
201003 直供区 5162359.0402151244
表里的内容是这样的