SQL code--建立视图或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
扫一扫
4299
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
