--建立视图或cte
create view v_Customer
as
select Orders.OrderDate,Customers.CompanyName as Customer,
[Order Details].UnitPrice* [Order Details].Quantity as SalesAmount,[Order Details].Quantity from Orders  
inner join [Order Details]
on Orders.OrderID=[Order Details].OrderID  
left join Customers  
on Orders.CustomerID=Customers.CustomerID

go
/*
with v_Customer as 
(
select Orders.OrderDate,Customers.CompanyName as Customer,
[Order Details].UnitPrice* [Order Details].Quantity as SalesAmount,[Order Details].Quantity from Orders  
inner join [Order Details]
on Orders.OrderID=[Order Details].OrderID  
left join Customers 
) 
*/

---利用视图或cte查询
select ta.*,
       ta.SalesAmount*1.0/tb.SalesAmount as [环比SalesAmount],
       ta.Quantity*1.0/tb.Quantity as [Quantity]
from 
(select convert(varchar(7),OrderDate,120) as OrderDate,
       max(OrderDate) as OrderDate1,
       Customer,
       sum(SalesAmount) as SalesAmount,sum(Quantity) as Quantity
from v_Customer
group by convert(varchar(7),OrderDate,120),Customer) as ta
left join
(select convert(varchar(7),OrderDate,120) as OrderDate,
       max(OrderDate) as OrderDate1,
       Customer,
       sum(SalesAmount) as SalesAmount,sum(Quantity) as Quantity
from v_Customer
group by convert(varchar(7),OrderDate,120),Customer) as tb
on datediff(mm,tb.OrderDate1,ta.OrderDate1)=1
and ta.Customer=tb.Customer