帮忙看个查询

[IMBIRDMAN]

在视图A中从试图B中查寻纪录。包含TransID,TransTime,Name,Payment等字段。查询的结果中会出现TransID相同的纪录，目前想得到的结果是对于TransID相同的纪录只取TransTime最大的那一条。也就是每一条TransID只能出现一条记录。
我目前的视图写法如下
SELECT TransID,TransTime,Name,Payment
FROM
{
SELECT TransID,TransTime,Name,Payment
FROM B AS B_1 INNER JOIN
{
SELECT MAX(TransTime) TransTime,TransID
FROM B
GROUP BY TransID
} AS B_2
ON B_1.TransTime=B_2.TransTime AND B_1.TransID=B_2.TransID
}
这样的话，需要两次查询B视图，效率不高。大家帮忙优化一下。

[子陌红尘]

select
t.TransID,t.TransTime,t.Name,t.Payment
from
B t
where
not exists(select 1 from B where TransID=t.TransID and TransTime>t.TransTime)

[WangZWang]

--如：
Select * from B as t
where not exists(Select * from B where
TransID=t.TransID and TransTime>t.TransTime)

[子陌红尘]

select
t.TransID,t.TransTime,t.Name,t.Payment
from
B t
where
t.TransTime=(select max(TransTime) from B where TransID=t.TransID)

select
t.TransID,t.TransTime,t.Name,t.Payment
from
B t
where
t.TransTime=(select top 1 TransTime from B where TransID=t.TransID order by TransTime desc)

[OracleRoob]

SELECT TransID,TransTime,Name,Payment
FROM B AS B_1 INNER JOIN
(
SELECT MAX(TransTime) TransTime,TransID
FROM B
GROUP BY TransID
) AS B_2
ON B_1.TransTime=B_2.TransTime AND B_1.TransID=B_2.TransID

[OracleRoob]

SELECT
B_1.TransID,
B_1.TransTime,
B_1.Name,
B_1.Payment
FROM B AS B_1 INNER JOIN
(
SELECT MAX(TransTime) TransTime,TransID
FROM B
GROUP BY TransID
) AS B_2
ON B_1.TransTime=B_2.TransTime AND B_1.TransID=B_2.TransID

[IMBIRDMAN]

where子句中和inner join子句中对B的查询，对整个试图在效率上有什么影响？请大虾赐教。

[IMBIRDMAN]

红尘：我刚才作了个实验，一共查出45万条数据，用inner join的花了40-51秒，where子句的查询用了1分40-1分58秒。有没有更高效率的方法？

[子陌红尘]

对视图似乎没有太好的办法。

[IMBIRDMAN]

噢，了解。
多谢各位。