---------------------------------
--  Author: htl258(Tony)
--  Date  : 2009-07-10 15:55:44
---------------------------------
--> 生成测试数据表:a

If not object_id('[a]') is null
    Drop table [a]
Go
Create table [a]([pono] nvarchar(10),[datex] Datetime)
Insert a
Select '0809010001','2008-09-01' union all
Select '0901010001','2009-01-01' union all
Select '0902010001','2009-02-01'
Go
--Select * from a

--> 生成测试数据表:b

If not object_id('[b]') is null
    Drop table [b]
Go
Create table [b]([pono] nvarchar(10),[code] nvarchar(4),[price] decimal(18,1))
Insert 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 union all
Select '0902010001','c123',3.5
Go
--Select * from b

-->SQL查询如下:
select pono,datex,code,price
from (
    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 t
where rn<=2
/*
pono       datex                   code price
---------- ----------------------- ---- ---------------------------------------
0902010001 2009-02-01 00:00:00.000 a123 1.2
0901010001 2009-01-01 00:00:00.000 a123 1.5
0902010001 2009-02-01 00:00:00.000 b123 2.5
0901010001 2009-01-01 00:00:00.000 b123 3.0
0902010001 2009-02-01 00:00:00.000 c123 3.5
0809010001 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