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我想写一个连续环比的表, 客户的月销售额的环比曲线。
请高手帮助, 谢谢!
[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我想写一个连续环比的表, 客户的月销售额的环比曲线。
请高手帮助, 谢谢!
解决方案 »
- 求助一个关于MSSQL2000数据库迁移的问题
- 这一句错在哪里:SELECT name FROM sys.databases WHERE name = N'Test' ; Invalid object name 'sys.databases'.
- 由数字组成的字段建立全文索引后无法搜索
- 为什么在SQL里新建SQL注册时会出现sql server不存在或访问被拒绝
- 存储过程问题,进来帮我看看,解决后立刻结贴,谢谢
- perl在iis下不能连接ODBC?
- 如何设计这样的一个表,查询效率优先
- 一个算法问题 ,百思不得其解
- 循环中的union
- 排序问题:SQL_Latin1_General_CP1_CI_AS" and "Chinese_PRC_CI_AS"
- 允许输入相同的行数据
- 瞧一瞧,看一看,不要错过,小弟跪求一个问题,已经困扰几天了,请各位高手帮忙
这用不着动态,例如你可以查询一个结果集如下格式:客户 ------ 日期 -------- 增长
A -------- 201001 -------- 10
B -------- 201001 -------- 34
A -------- 201002 -------- 20
B -------- 201002 -------- 16
A -------- 201003 -------- 24这样可以直接查询出相邻月份的环比增长!
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.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 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