问个sql查询的问题

[jojoova]

现有两张表
表taritem是收费项目表
tr_rowid tr_item_code tr_item_desc
1000 xy100323 阿莫西林
1001 xy200365 葡糖糖
表tr_price表是价格表
tp_rowid tr_rowid tp_startdate tp_price
1000||1 1000 2001-09-08 10
1000||2 1000 2002-01-05 11.5
1001||1 1001 2003-05-08 50
1001||2 1001 2005-01-05 50.5
1001||3 1001 2008-04-25 52
两张表是通过tr_rowid关联的，sql怎么写能把价格表里最近的开始日期tp_startdate对应的价格取出来

得到如下结果
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
如果在程序里中处理是不难的，但我想在数据库sql文里直接查询得到上述结果该怎么写啊？

[人生无悔]

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;

[jojoova]

我的数据库居然不支持，我倒了

[--小F--]

[Quote=引用 2 楼 的回复:]
我的数据库居然不支持，我倒了
[/Quote]
是什么数据库？
2000？
还是MYSQL还是其他什么

[唐诗三百首]

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)
*/

[kavaye]

代码如下：

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

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

[kavaye]

前面发错了；
重新发一下

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

[jojoova]

是cache数据库，通过ODBC连接的，我现在用其他方法解决了，谢谢各位