这样的SQL语句怎么写？

[icyer]

主要有两个表：t_good_fundData、t_tnst_price
t_good_fundData用到的字段有：fnCode，rgstCcy
t_tnst_price用到的字段有：priceDate，prdCode(对应于上面的rgstCcy)，price
数据：
t_good_fundData
fnCode rgstCcy
0101 RMB
0102 TWD

t_tnst_price
priceDate prdCode price
2003-1-1 RMB 8.10
2003-1-2 RMB 8.20
2003-1-3 RMB 8.30
2003-1-1 TWD 1.10
2003-1-2 TWD 1.20
2003-1-3 TWD 1.30

现在我想得到这样的查询：
fnCode price
0101 8.30
0102 1.30
也就是说，查询出t_good_fundData里fnCode对应的rgstCcy在t_tnst_price中的日期最大的price

似乎可以这样写：
SELECT f.fnCode, f.rgstCcy,price.price FROM t_good_fundData f
LEFT OUTER JOIN
(SELECT TOP 1 * FROM t_tnst_price WHERE t_tnst_price.prdCode=f.rgstCcy ORDER BY priceDate DESC) price
ON price.prdCode=f.rgstCcy
但是遗憾的是子查询里不能读取外层的f

[pengdali]

select fncode,(select top 1 price from t_tnst_price where prdcode=t_good_fundData.rgstccy order by pricedate desc) from t_good_fundData

[Rewiah]

select fncode,(select price from t_tnst_price where prdcode=t_good_fundData.rgstccy AND pricedate=(SELECT MAX(pricedate) FROM t_tnst_price where prdcode=t_good_fundData.rgstccy)) from t_good_fundData

[愉快的登山者]

select B.fnCode, A.price from t_tnst_price as A
left join t_good_fundData as B on A.prdCode = B.rgstCcy
where A.priceDate = (select max(priceDate)
from t_tnst_price where prdCode = A.prdCode)

[newdongkui]

SELECT a.fnCode,d.price FROM t_good_fundData a
inner join
(
select b.prdCode,b.price FROM t_tnst_price b inner join
(SELECT prdCode,max(priceDate) as priceDate FROM t_tnst_price group by prdCode ) as c on b.prdcode = c.prdcode and b.priceDate=c.priceDate
) as d on a.rgstCcy = d.prdCode