某一种产品的价格表ID P_NAME PRICE DATE
----------------------------
1 GOLF1 100 2007/1/1
2 GOLF1 200 2007/2/1
3 GOLF2 300 2007/3/1
4 GOLF1 200 2007/4/1
5 GOLF1 400 2007/5/1
6 GOLF2 4000 2007/4/6
7 GOLF1 90 2006/12/11.求这个月和上一个月价格的差价.用一条SQL语句怎么写?2.这个月的最后时间的价格自动INSERT下一个月的价格,怎么自动INSERT最后时间的价格为下一个月的价格?还有得判断下一个月价格已经有的,就过滤,没有的自动INSERT.
----------------------------
1 GOLF1 100 2007/1/1
2 GOLF1 200 2007/2/1
3 GOLF2 300 2007/3/1
4 GOLF1 200 2007/4/1
5 GOLF1 400 2007/5/1
6 GOLF2 4000 2007/4/6
7 GOLF1 90 2006/12/11.求这个月和上一个月价格的差价.用一条SQL语句怎么写?2.这个月的最后时间的价格自动INSERT下一个月的价格,怎么自动INSERT最后时间的价格为下一个月的价格?还有得判断下一个月价格已经有的,就过滤,没有的自动INSERT.
*,
PRICE - IsNull((Select PRICE From 价格表 Where P_NAME = A.P_NAME And DateDiff(mm, [DATE], A.[DATE]) = 1), 0 ) As 差价
From 价格表 A
*,
PRICE - IsNull((Select TOP 1 PRICE From 价格表 Where P_NAME = A.P_NAME And DateDiff(mm, [DATE], A.[DATE]) = 1 Order By [DATE] Desc), 0 ) As 差价
From 价格表 A
select a.P_Name,a.PRICE-b.PRICE from tab a
left join tab b on a.P_Name=b.P_Name and datediff(mm,b.date,a.date)=1
where datediff(mm,a.date,getdate())=0 2
insert into tab
select P_Name,Price,dateadd(mm,1,getdate()) from tab a
left join (select P_Name,max(date) date from tab group by p_Name)b on a.P_Name=b.P_Name and a.date=b.date
where datediff(mm,b.date,getdate())>=0 and datediff(mm,a.date,getdate())=0未测试
试试以下语句,可能需要调整一下,呵呵,没弄数据进行测试
Select id,P_NAME,PRICE,DATE,
(select top 1 price from your_table where id > T1.id and P_NAME=T1.P_NAME order by id) as 上月价格,
abs(PRICE-(select top 1 price from your_table where id > T1.id and P_NAME=T1.P_NAME order by id)) as 价格差
from your_table as T1
order by id