在视图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 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视图,效率不高。大家帮忙优化一下。
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)
Select * from B as t
where not exists(Select * from B where
TransID=t.TransID and TransTime>t.TransTime)
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)
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_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