用户名 金额 时间
a 1 2012-9-22 14:06:58
b 3 2012-9-30 14:06:58
c 2 2012-8-30 14:06:58
d 5 2012-8-14 14:06:58求上上月和上月(不是8月和9月,有可能是9月10月)金额统计 (金额是累加起来的),要考虑跨年 。sql 语句要怎么写
a 1 2012-9-22 14:06:58
b 3 2012-9-30 14:06:58
c 2 2012-8-30 14:06:58
d 5 2012-8-14 14:06:58求上上月和上月(不是8月和9月,有可能是9月10月)金额统计 (金额是累加起来的),要考虑跨年 。sql 语句要怎么写
select sum(金额) 金额, convert(char(6),时间 , 112) 年月 from TB
where datediff(mm,时间,getdate())<3 and datediff(mm,时间,getdate())> 0
group by convert(char(6),时间 , 112)
where datediff(mm,时间,getdate())<3 and datediff(mm,时间,getdate())> 0
group by convert(char(6),时间 , 112),userName
--最猥琐的方法实现的,我也不知道你统计的时候按不按用户分组,嘿嘿!
create table test
(
username varchar(20),
sum decimal(18,5),
CreateDate datetime
)insert into test values
('a', 1 ,GETDATE()-30)
insert into test values
('b', 3 ,GETDATE()-30)
insert into test values
('c', 2 ,GETDATE()-30)
insert into test values
('d', 5 ,GETDATE()-30)
Create PROCEDURE testGetData
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for procedure here
declare @year char(4),@month char(2),@beginDate varchar(10),@endDate varchar(10)
set @year=year(getdate())
set @month=month(getdate())
if(@month=1)
begin
set @beginDate=cast(@year-1 as varchar(4))+'11-01'
set @endDate=cast(@year as varchar(4))+'01-01'
end
else if(@month=2)
begin
set @beginDate=cast(@year-1 as varchar(4))+'12-01'
set @endDate=cast(@year as varchar(4))+'02-01'
end
else
begin
set @beginDate=cast((@year-1)as varchar(10))+'-'+cast((@month-2)as varchar(2))+'-01'
set @endDate=cast(@year as varchar(10))+'-'+cast((@month)as varchar(2))+'-01'
end
print @beginDate
print @endDate
select username, SUM(sum) as totle from test
where CreateDate >= CONVERT(VARCHAR(10),@beginDate,120) and CreateDate< CONVERT(VARCHAR(10),@endDate,120)
group by username
END
GO
username totle
a 1.00000
b 3.00000
c 2.00000
d 5.00000
立马闪人……