Create View V_TEST As Select 用户编号, SUM(Case DateDiff(mm, 销售时间, GetDate()) When 0 Then 销售额 Else 0 End) As 本月销售额, SUM(Case DateDiff(mm, 销售时间, GetDate()) When 1 Then 销售额 Else 0 End) As 上月的销售额, SUM(销售额) As 总销售额 From seal Group By 用户编号 GO
create view vW_seal asselect 用户编号 ,sum(case when convert(varchar(7),销售时间,120)=convert(varchar(7),getdate(),120) then 销售额 else 0 end ) as 本月销售额 ,sum(case when convert(varchar(7),销售时间,120)=convert(varchar(7),datediff(m,1,getdate()),120) then 销售额 else 0 end ) as 上月的销售额 ,sum(销售额) as 总销售额 from seal group by 用户编号
create view vW_seal asselect 用户编号 ,(select sum(销售额) from seal where a.id=id and datediff(mm,getdate(),销售时间)=0 ) as 本月销售额 ,(select sum(销售额) from seal where a.id=id and datediff(mm,getdate(),销售时间)=1 ) as 上月的销售额 ,sum(销售额) as 总销售额 from seal a group by 用户编号
As
Select
用户编号,
SUM(Case DateDiff(mm, 销售时间, GetDate()) When 0 Then 销售额 Else 0 End) As 本月销售额,
SUM(Case DateDiff(mm, 销售时间, GetDate()) When 1 Then 销售额 Else 0 End) As 上月的销售额,
SUM(销售额) As 总销售额
From
seal
Group By
用户编号
GO
create view vW_seal
asselect 用户编号
,sum(case when convert(varchar(7),销售时间,120)=convert(varchar(7),getdate(),120) then 销售额 else 0 end ) as 本月销售额
,sum(case when convert(varchar(7),销售时间,120)=convert(varchar(7),datediff(m,1,getdate()),120) then 销售额 else 0 end ) as 上月的销售额
,sum(销售额) as 总销售额
from seal
group by 用户编号
datediff
应该是:
DATEADD
在向指定日期加上一段时间的基础上,返回新的 datetime 值。语法
DATEADD ( datepart , number, date )
create view vW_seal
asselect 用户编号
,(select sum(销售额) from seal where a.id=id and datediff(mm,getdate(),销售时间)=0 ) as 本月销售额
,(select sum(销售额) from seal where a.id=id and datediff(mm,getdate(),销售时间)=1 ) as 上月的销售额
,sum(销售额) as 总销售额
from seal a
group by 用户编号