select taritem.*,t1.tp_startdate,t1.tp_price from taritem,
    (select row_number() over(partition by tr_rowid order by tp_startdate desc) rn,
    tr_rowid,tp_startdate,tp_price from tr_price) t1
where taritem.tr_rowid=t1.tr_rowid and t1.rn=1;

------解决方案--------------------
SQL code

create table taritem
(tr_rowid int, 
 tr_item_code varchar(10), 
 tr_item_desc varchar(15))

insert into taritem
select 1000, 'xy100323', '阿莫西林' union all
select 1001, 'xy200365', '葡糖糖'

create table tr_price
(tp_rowid varchar(10), tr_rowid int, 
 tp_startdate date, tp_price decimal(5,1))

insert into tr_price
select '1000||1', 1000, '2001-09-08', 10 union all
select '1000||2', 1000, '2002-01-05', 11.5 union all
select '1001||1', 1001, '2003-05-08', 50 union all
select '1001||2', 1001, '2005-01-05', 50.5 union all
select '1001||3', 1001, '2008-04-25', 52


select a.tr_rowid, a.tr_item_code, a.tr_item_desc,
       b.tp_startdate,b.tp_price
from taritem a
left join
(select c.tr_rowid,max(c.tp_startdate) 'tp_startdate',
 (select tp_price from tr_price d 
  where d.tr_rowid=c.tr_rowid and d.tp_startdate=max(c.tp_startdate)) 'tp_price'
 from tr_price c group by c.tr_rowid) b
on a.tr_rowid=b.tr_rowid

/*
tr_rowid    tr_item_code tr_item_desc    tp_startdate tp_price
----------- ------------ --------------- ------------ ------------
1000        xy100323     阿莫西林            2002-01-05   11.5
1001        xy200365     葡糖糖              2008-04-25   52.0

(2 row(s) affected)
*/

------解决方案--------------------
代码如下：
SQL code
select a.tr_rowid,a.tr_item_code,a.tr_item_desc,tp_startdate,tp_price
 from (select a.tr_rowid,a.tr_item_code,a.tr_item_desc,tp_startdate,tp_price,
 ROW_NUMBER()over(partition by a.tr_rowid,a.tr_item_code,a.tr_item_desc order by tp_startdate desc) as n
 from taritem a inner join tr_price b on b.tr_rowid=a.tr_rowid) p
 where n=1

------解决方案--------------------
前面发错了；
重新发一下
SQL code

select tr_rowid,tr_item_code,tr_item_desc,tp_startdate,tp_price
 from (select a.tr_rowid,a.tr_item_code,a.tr_item_desc,tp_startdate,tp_price,
 ROW_NUMBER()over(partition by a.tr_rowid,a.tr_item_code,a.tr_item_desc order by tp_startdate desc) as n
 from taritem a inner join tr_price b on b.tr_rowid=a.tr_rowid) p
 where n=1

tr_rowid    tr_item_code tr_item_desc    tp_startdate tp_price
----------- ------------ --------------- ------------ ---------------------------------------
1000        xy100323     阿莫西林           2002-01-05   11.5
1001        xy200365     葡糖糖             2008-04-25   52.0