Mysql:从加入的表格中获取最后一行
我知道这个问题已被询问了很多次,但我在实施时遇到了问题。 MySQL在这里并不是我最大的优势。Mysql:从加入的表格中获取最后一行
我为我的网站构建了一个电子邮件系统,我想要做的是当用户查看他们的收件箱他们看到他们的消息,但他们看到最新的发件人,日期和用户的详细信息,而不是原始发件人,日期和用户详细信息。
我的表结构如下:
Message_Header:
-----------------------
messageid INT
type VARCHAR
subject VARCHAR
Message_Recipient:
-----------------------
id INT
messageid INT
userid INT
isread ENUM
isspam ENUM
isdelete ENUM
Message_Detail:
--------------------
dtlid INT
messageid INT
from_userid INT
hash VARCHAR
type SMALLINT
body TEXT
title VARCHAR
subtitle VARCHAR
content TEXT
thumb VARCHAR
url TEXT
images VARCHAR
time_sent DATETIME
timestamp INT
我觉得像这样的工作:
select mh.messageid, mh.subject, mh.type, mr.isread, msg_dtl.from_userid, msg_dtl.firstname, msg_dtl.lastname, msg_dtl.gender, msg_dtl.avatar, msg_dtl.hash, msg_dtl.body, msg_dtl.time_sent
from message_header mh
inner join message_recipient mr on mr.messageid = mh.messageid
inner join (
select md.messageid, md.from_userid, u.firstname, u.lastname, u.gender, u.avatar, md.hash, md.body, md.time_sent
from message_detail md
inner join users u on u.userid = md.from_userid
order by md.time_sent desc
limit 1
) as msg_dtl ON mh.messageid = msg_dtl.messageid
where mr.userid = 5
and mr.isspam = '0'
and mr.isdelete = '0'
但很显然,它限制了整个查询到只有1行!
我身边看着广泛和我鹅卵石这个在一起,但我很担心它不是最优化的,将是一个猪:
SELECT msg_dtl.type, msg_dtl.subject, msg_dtl.isread, u.firstname, u.lastname, u.gender, u.avatar, md.body, md.timestamp
FROM message_detail md
INNER JOIN users u on u.userid = md.from_userid
INNER JOIN
(
SELECT mr.userid, mh.type, mh.subject, mr.isspam, mr.isdelete, mr.isread, md.messageid, md.body, max(timestamp) as timestamp
FROM message_detail md
inner join message_header mh on mh.messageid = md.messageid
inner join message_recipient mr on mr.messageid = mh.messageid
GROUP BY md.messageid
) as msg_dtl
USING (timestamp, messageid)
where msg_dtl.userid = 5
and msg_dtl.isspam = '0'
and msg_dtl.isdelete = '0'
我将不胜感激优化这个任何指针或某人。如果您需要更多信息,请让我知道!
这可能有助于一个例外... IsRead标志。首先,我将根据单个消息(加入到详细信息表),消息中最近的序列(通过max(mr.id))获取“PreQuery”,并以最新的仅针对给定用户的非垃圾信息且未删除的记录,创建DETAIL条目(假定通过Max(md.DtlID)自动递增详细信息)。所以这给了我们在MOST,每个消息ID一个记录。
话虽这么说,我们现在能做的直接连接回的消息ID 1头:1加入
然后,1:1上收到的最新的时序回邮件收件人加入.. 。如果一个链接消息来回10次以上,这里是我假设你将会收到与消息ID相关联的最新电子邮件RECEIVED。所以,这是我们获得“IsRead”标志的记录......尽管有人可以阅读更近的文章,但从来没有在电子邮件链中读过。
接下来,1:1连接到MESSAGE DETAIL表(通过Max(md.DtlID)),以获得给定消息的最后一个消息详细信息。我们不必加入消息ID,因为我们直接具有消息的详细ID ...
最后,我们的详细信息可以加入到用户表中以获取“From”用户信息。
希望这有助于,包括澄清如何派生结果集...
SELECT STRAIGHT_JOIN
PreQuery.MessageID,
mr2.IsRead,
mh.Type,
mh.Subject,
md.from_UserID,
md.isread,
md.body,
md.timestamp,
u.firstname,
u.lastname,
u.gender,
u.avatar
from
(SELECT
mr.MessageID,
MAX(mr.ID) as LastMessageSequence,
MAX(md.DtlID) as LastDetail
from
Message_Recipient mr
join Message_Detail md
ON mr.messageid = md.messageid
where
mr.userid = 5
and mr.isspam = '0'
and mr.isdelete = '0'
group by
mr.MessageID) PreQuery
JOIN Message_Recipient mr2
ON PreQuery.LastMessageSequence = mr2.ID
JOIN Message_Header mh
ON PreQuery.MessageID = mh.MessageID
JOIN Message_Detail md
ON PreQuery.LastDetail = md.DtlID
JOIN users u
on md.from_userid = u.userid
order by
md.timestamp desc
哇,非常感谢。我非常感谢你为答案付出的努力。 isread标志实际上只会随着messages_detail表的每一个添加而更新,并且message_recipient与用户具有1-1关系。我已经修改了查询来反映这一点,你会说是保持在你已经完成的优化? http://pastebin.com/H1ZzYyEP – PaulM 2011-06-16 23:16:58
@PaulM,我不这么认为......看起来一个消息ID有多个可能的条目,因此它有一个ID字段也表明了它的唯一ID。如果message_recipient表中的同一个用户对同一个消息ID有多个条目,那么我认为你的版本不会像我的那样有效地进行调整......但是我也可能错误地看不到实际的数据。我只想确保message_recipient表的索引(userid,isspam,isdelete)来优化查询甚至更多... – DRapp 2011-06-16 23:34:53
真棒,非常感谢您的帮助。我已经使查询完全按照我的需要工作。 – PaulM 2011-06-17 10:57:28