这是我当前的mySQL查询:
SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread
FROM TAB_EVENT e
LEFT JOIN TAB_MESSAGE m ON e.id=m.event_id
WHERE ( m.`read` IS NULL OR m.`read` = 0)
GROUP BY e.id
ORDER BY m.datetime DESC, e.id ASC
LIMIT 10;
我得到:所有TAB_EVENT行根本没有TAB_MESSAGE消息或至少有一个未读消息(read = BOOLEAN DEFAULT 0).
我需要:所有TAB_EVENT行,无论其消息和最新未读的日期时间以及每个TAB_EVENT的未读消息数.
可能有数千个TAB_EVENT行和更多TAB_MESSAGE行,我想避免为每个单个事件运行一个子查询.
备用1:使用上面的查询使用UNION加上另一个m.read = 1的查询.
缺点:两个JOINed繁重查询加上另一个临时表.
备用2:SELECT e.*无条件加上另一个SELECT event_id,MAX(),COUNT()FROM TAB_MESSAGE GROUP BY event_id ORDER BY MAX()并合并调用Perl脚本中的所有内容.
缺点:失去服务器端LIMIT的能力.
UPDATE /解决方案
这是我完成工作的最终查询:
SELECT e.*, m.unread_last, m.unread
FROM
TAB_EVENT e LEFT JOIN
(
SELECT event_id,MAX(`datetime`) AS unread_last, COUNT(*) AS unread FROM TAB_MESSAGE
WHERE `read` = 0
GROUP BY event_id
ORDER BY `datetime` DESC
) m
ON e.id=m.event_id
ORDER BY m.datetime DESC, e.id ASC
LIMIT 10;
读取IS NULL条件捕获原始查询中没有任何消息的事件,读取列是布尔值NOT NULL.
此查询现在返回所有事件,并添加最新的未读消息时间戳加上未读消息的数量.对于没有任何完全正常的消息的事件,两个消息列都是NULL(对于日期时间将被转换为“”,对于Perl端的计数将被转换为0).
感谢RolandoMySQLDBA,我的查询基于他的回答.