连接表的最后一条记录
我正在查找正确的SQL代码来连接两个表并仅显示详细信息表的最后一条记录。连接表的最后一条记录
我有2个表的数据块,
Deals
DealID
Dealname
DealDetails
DealComments
dcID
DealID
CommentTime
CommentPerson
Comment
有每笔交易多份意见,但我想创建一个显示所有优惠只在最后一评论的来自每一笔交易视图(由CommentTime)字段确定
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)
编辑:我没看过最初的问题足够接近,并没有注意到,都需要在查看所有优惠券行。下面是我的修订答案:
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)
这个效果很好,但它只返回有评论的交易。我还想看到没有评论的交易 – 2009-07-04 20:27:27
@Khalid - 我编辑了我的答案,新的SQL应返回所有交易行。 – 2009-07-04 23:08:33
优秀,正是我所需要的。感谢John – 2009-07-05 05:24:17
试试这个..
SELECT D.*,DC1.Comment
FROM Deals AS D
INNER JOIN DealComments AS DC1
ON D.DealId = DC1.DealID
INNER JOIN
(
SELECT
DealID,
MAX(CommentTime) AS CommentTime
FROM DealComments AS DC2
GROUP BY DealID
) AS DC2
ON DC2.DealId = DC.DealId
AND DC2.CommentTime = DC1.CommentTime
不是很优雅,但在甲骨文的工作原理:
select dealid,
dealname,
dealdetails,
comment,
from
(
select a.dealid,
a.dealname,
a.dealdetails,
b.commenttime,
b.comment,
max(commenttime) over (partition by a.dealid) as maxCommentTime
from deals a inner join dealcomments b on b.dealid = a.dealid
)
where comment = maxCommentTime
为什么你将其描述为“不太优雅”? – 2009-11-03 15:15:47
试试这个:
CREATE VIEW DealsWithLastComment
AS
SELECT
D.*, DC.*
FROM
Deals D INNER JOIN DealComments DC
ON D.DealID = DC.DealID
GROUP BY D.DealID, DC.CommentTime
HAVING DC.CommentTime = MAX(DC.CommentTime)
select
d.DealID, dc1.dcID, dc1.Comment, dc1.CommentPerson, dc1.CommentTime
from
Deals d
inner join
DealComments dc1 on dc1.DealID = d.DealID
where
dc1.CommentTime = (select max(dc2.CommentTime) from DealsComments dc2 where dc2.DealId = dc1.DealId)
强制性无子查询无处回答:
select d.*
, dc.*
from Deals d
left outer join DealComments dc
on d.DealID = dc.DealID
left outer join DealComments dc1
on d.DealID = dc1.DealID
and
dc1.CommentTime > dc.CommentTime
where dc1.CommentTime is null
告诉我一切都在Deals
和DealComments
当存在比任何给定的时间注释为特定DealID
大不CommentTime
。
编辑:Alex Kuznetsov在评论中敏锐地指出:OP要求显示所有的交易 - 交易是否有评论与否。所以我把第一个JOIN
从INNER
更改为LEFT OUTER
。
这个查询怎么样。
select * from Deals
left join DealComments on Deals.DealID = DealComments.DealID and DealComments.CommentTime = (SELECT CommentTime FROM DealComments WHERE DealComments.DealID = Deals.DealID ORDER BY CommentTime DESC limit 1)
你想要什么,如果对于一些交易有两个或更多最后的评论与完全相同的时间? – 2009-07-04 23:22:58