联接表的最后一条记录

Question

我正在寻找连接2个表并仅显示明细表的最后一条记录的正确SQL代码。

我有一个带有2个表的数据库，

Deals 
   DealID
   Dealname
   DealDetails

DealComments
   dcID
   DealID
   CommentTime
   CommentPerson
   Comment

每个交易都有多个评论，但是我想创建一个显示所有交易的视图，并且仅显示每个交易的最后一条评论（由CommentTime确定）字段

Answer 1

select a.dealid
, a.dealname
, a.dealdetails
, b.dcid
, b.commenttime
, b.commentperson
, b.comment
from deals a, dealcomments b
where b.dealid = a.dealid
  and b.commenttime = (select max(x.commenttime)
                       from dealcomments x
                       where x.dealid = b.dealid)

编辑：我没有足够接近地阅读初始问题，也没有注意到视图中需要所有DEALS行。以下是我的修改后的答案：

select a.dealid
, a.dealname
, a.dealdetails
, b.dcid
, b.commenttime
, b.commentperson
, b.comment
from deals a left outer join (select x.dcid
, x.dealid
, x.commenttime
, x.commentperson
, x.comment
from dealcomments x
where x.commenttime = (select max(x1.commenttime)
                       from dealcomments x1
                       where x1.dealid = x.dealid)) b
on (a.dealid = b.dealid)