sql - Join表的最后一条记录

Question

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

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

Deals 
   DealID
   Dealname
   DealDetails

DealComments
   dcID
   DealID
   CommentTime
   CommentPerson
   Comment

每个交易有多个评论，但我想创建一个 View ，显示所有交易和每个交易的最后一条评论(由 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)

编辑:我没有仔细阅读最初的问题，也没有注意到 View 中需要所有交易行。以下是我修改后的答案:

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)