求最新2次采购价格，物料代码及单价相同的只取一个，根据时间降序排列，这样可以知道最近的降价是什么时候，并把这2次采购单的数据输出。
数据为：
A表： B表:
pono datex pono code price
0809010001 2008-09-01 0809010001 a123 1.5
0901010001 2009-01-01 0809010001 b123 5
0902010001 2009-02-01 0809010001 c123 4
0901010001 a123 1.5
0901010001 b123 3
0901010001 c123 3.5
0902010001 a123 1.2
0902010001 b123 2.5
0902010001 c123 3.5
输出结果为：
pono datex code price
0902010001 2009-02-01 a123 1.2
0901010001 2009-01-01 a123 1.5
0902010001 2009-02-01 b123 2.5
0901010001 2009-01-01 b123 3
0902010001 2009-02-01 c123 3.5
0809010001 2008-09-01 c123 4
------解决方案--------------------
- SQL code
----------------------------------- Author: htl258(Tony)-- Date : 2009-07-10 15:55:44-----------------------------------> 生成测试数据表:aIf not object_id('[a]') is null Drop table [a]GoCreate table [a]([pono] nvarchar(10),[datex] Datetime)Insert aSelect '0809010001','2008-09-01' union allSelect '0901010001','2009-01-01' union allSelect '0902010001','2009-02-01'Go--Select * from a--> 生成测试数据表:bIf not object_id('[b]') is null Drop table [b]GoCreate table [b]([pono] nvarchar(10),[code] nvarchar(4),[price] decimal(18,1))Insert bSelect '0809010001','a123',1.5 union allSelect '0809010001','b123',5 union allSelect '0809010001','c123',4 union allSelect '0901010001','a123',1.5 union allSelect '0901010001','b123',3 union allSelect '0901010001','c123',3.5 union allSelect '0902010001','a123',1.2 union allSelect '0902010001','b123',2.5 union allSelect '0902010001','c123',3.5Go--Select * from b-->SQL查询如下:select pono,datex,code,pricefrom ( select a.pono,a.datex,b.code,b.price, rn=row_number() over(partition by code order by datex desc) from a join (select * from b t where not exists(select 1 from b where code=t.code and price=t.price and [pono]>t.[pono])) b on a.pono=b.pono) as twhere rn<=2/*pono datex code price---------- ----------------------- ---- ---------------------------------------0902010001 2009-02-01 00:00:00.000 a123 1.20901010001 2009-01-01 00:00:00.000 a123 1.50902010001 2009-02-01 00:00:00.000 b123 2.50901010001 2009-01-01 00:00:00.000 b123 3.00902010001 2009-02-01 00:00:00.000 c123 3.50809010001 2008-09-01 00:00:00.000 c123 4.0(6 行受影响)*/
------解决方案--------------------
- SQL code
declare @a table (pono nvarchar(10),datex datetime)insert into @a select '0809010001','2008-09-01' union all select '0901010001','2009-01-01' union all select '0902010001','2009-02-01'declare @b table (pono nvarchar(10),code nvarchar(10),price money)insert into @b select '0809010001','a123',1.5 union all select '0809010001','b123',5 union all select '0809010001','c123',4 union all select '0901010001','a123',1.5 union all select '0901010001','b123',3 union all select '0901010001','c123',3.5 union all select '0902010001','a123',1.2 union all select '0902010001','b123',2.5;with China as(select a.datex,b.* from @a a join @b b on a.pono=b.pono )select * from China a where datex in (select top 2 datex from China where a.code=code order by datex desc ) order by price---datex pono code price----------------------- ---------- ---------- ---------------------2009-02-01 00:00:00.000 0902010001 a123 1.202009-01-01 00:00:00.000 0901010001 a123 1.502009-02-01 00:00:00.000 0902010001 b123 2.502009-01-01 00:00:00.000 0901010001 b123 3.002009-01-01 00:00:00.000 0901010001 c123 3.502008-09-01 00:00:00.000 0809010001 c123 4.00(6 行受影响)