建立一个比较复杂的视图问题
在数据库中建立一个表如:seal(编号,用户编号,销售额,销售时间)
问怎么建立一个视图vW_seal要求视图有字段名:(用户编号,本月销售额,上月的销售额,总销售额)?
请大家多给点指教!
[解决办法]
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
as
select 用户编号
,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
as
select 用户编号
,(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 用户编号
