NorthWind 数据库：
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
我想写一个连续环比的表， 客户的月销售额的环比曲线。
请高手帮助， 谢谢！
------解决方案--------------------
- SQL code
--建立视图或ctecreate view v_Customerasselect 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.CustomerIDgo/*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 Quantityfrom v_Customergroup by convert(varchar(7),OrderDate,120),Customer) as taleft join(select convert(varchar(7),OrderDate,120) as OrderDate, max(OrderDate) as OrderDate1, Customer, sum(SalesAmount) as SalesAmount,sum(Quantity) as Quantityfrom v_Customergroup by convert(varchar(7),OrderDate,120),Customer) as tbon datediff(mm,tb.OrderDate1,ta.OrderDate1)=1and ta.Customer=tb.Customer