主页 / dba / 问题 / 13581
Accepted
Sebastian
Asked: 2012-02-22 08:18:00 +0800 CST

将 WHERE 限制为 MAX() 和 COUNT()

  • 772

这是我当前的 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 消息或至少有一个未读消息（读取 = BOOLEAN DEFAULT 0）。

我需要：所有 TAB_EVENT 行，无论它们的消息是什么，以及最新未读的日期时间和每个 TAB_EVENT 的未读消息的计数。

可能有成千上万的 TAB_EVENT 行和更多的 TAB_MESSAGE 行，我想避免为每个事件运行一个子查询。

备选方案 1：使用上面的查询加上另一个带有 m 的 UNION。read= 1. 缺点：两个 JOINed 重查询加上另一个临时表。

备选方案 2：SELECT e.* unconditional 加上另一个 SELECT event_id, MAX(), COUNT() FROM TAB_MESSAGE GROUP BY event_id ORDER BY MAX() 并合并调用 Perl 脚本中的所有内容。缺点：失去服务器端 LIMIT 的能力。

更新/解决方案

这是我完成这项工作的最后一个查询：

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，我的查询是基于他的回答。

mysql join

2 个回答

  1. 尝试加入子查询：

    SELECT e.*, m.datetime AS unread_last, m.id AS unread
  FROM TAB_EVENT e
  LEFT JOIN (
    SELECT event_id, MAX(datetime) AS `datetime`, COUNT(id) AS id
    FROM TAB_MESSAGE
    WHERE read IS NULL OR read = 0
    GROUP BY event_id
  ) m ON e.id=m.event_id
  ORDER BY m.datetime DESC, e.id ASC
  LIMIT 10;
    • 3
  2. Best Answer

    这是您对问题的原始查询

    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_MESSAGE 收集必要的列
    • 对从 TAB_MESSAGE 收集的行应用 LIMIT 10
    • 运行 JOIN
    • 最后应用 MAX() 和 COUNT()

    这是我的提议

    SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread 
FROM
(
    SELECT * FROM
    (SELECT id,event_id,datetime FROM TAB_MESSAGE
    WHERE read IS NULL OR read = 0
    ORDER BY datetime DESC) mm
    LIMIT 10
) m
LEFT JOIN TAB_EVENT e 
ON e.id=m.event_id
ORDER BY m.datetime DESC, e.id ASC;

    试试看 ！！！

    更新 2012-02-21 17:06 EDT

    SELECT e.*, MAX(m.datetime) AS unread_last, COUNT(m.id) AS unread 
FROM
TAB_EVENT e LEFT JOIN
(
    SELECT * FROM
    (SELECT id,event_id,datetime FROM TAB_MESSAGE
    WHERE read IS NULL OR read = 0
    ORDER BY datetime DESC) mm
    LIMIT 10
) m
ON e.id=m.event_id
ORDER BY m.datetime DESC, e.id ASC;

    @Sebastian，我将查询放回原始连接顺序。请也试试这个！！！

    更新 2012-02-21 17:11 EDT

    确保日期时间字段已编入索引

    ALTER TABLE TAB_MESSAGE ADD INDEX read_datetime_ndx (read,datetime);
    • 2

相关问题