mysql - 如何在单个查询中使用连接和聚合函数更新表中的多行

Question

我有一个表，用于存储一段时间内添加到同一数据库中不同表的记录数的统计信息。

这是统计表的简化架构。

+------+-------------+---------+-------+--------+
|  id  |  programId  |  start  |  end  | count  |
+------+-------------+---------+-------+--------+

IRL 该表包含多个计数和使用中等复杂查询计算的日期。每个 programId 都将存在于表中，其 end 值为 null，这就是给定程序的统计信息存储位置。运行 cron 作业以定期关闭时间片(即将 end 列设置为有效日期)。下次更新统计数据时，将创建新的程序行，并使用 start 值并将 end 设置为 null。

这是消息表的简化模式，它包含在统计表中汇总的数据。此表包含在统计表中汇总的数据。

+------+-------------+------------+--------+
|  id  |  messageId  | programId  |  time  |
+------+-------------+------------+--------+

我想用一个查询更新所有程序消息计数。以下查询不起作用，因为 where 语句中不允许使用聚合函数 max。此外，我还阅读了有关在 where 子句中使用 coalesce 函数的警告。此外，在单个查询中一遍又一遍地连接相同的两个表似乎错误。我不是 sql 专家(很明显)，但这个查询对我提出了各种危险信号 - 即使它有效，我也会在这里发帖寻找改进它的方法。

UPDATE stats a
INNER JOIN messages b on a.programId = b.programId
SET a.numberReceived = 
    ( SELECT COUNT(c.id) 
      FROM messages c LEFT JOIN stats d ON c.programId = d.programId 
      WHERE c.datetime >= coalesce(max(d.end), '1970-01-01 00:00:01)
    )
WHERE end IS NULL

逻辑是只计算在最后一个关闭时间片之后到达的消息(如果存在的话)，否则计算给定程序的所有消息(即，如果这是程序第一次出现在统计表中)。

我尝试将此计数逻辑添加到触发器中，但是，计算的统计数据比这更多(这是一个简化的示例)，并且一次将许多行插入到消息表中。结果是，在 after insert 触发器中包含此逻辑会导致事务错误，从而导致插入失败。

我知道我可以通过循环程序并针对数据库发出许多 sql 语句来以编程方式完成此操作，但我认为这可以在单个语句中完成。

Answer 1

方法是这样的:

UPDATE 
    stats a
INNER JOIN 
    (
    SELECT m.programId, count(*) as cnt
    FROM 
        messages m
    LEFT JOIN 
        (
        SELECT programId,max(end) as end 
        FROM 
            stats
        WHERE end IS NOT NULL
        GROUP BY programId
        )as s
    ON m.programId=s.programId
    WHERE (s.end IS NULL OR m.datetime > s.end)
    GROUP BY m.programId
    ) b 
ON a.programId = b.programId
SET a.numberReceived = b.cnt
WHERE a.end IS NULL

您需要一个子查询 s 来获取每个程序的最后结束日期，然后使用它来过滤结束日期之后的所有新消息。最后获取子查询 b 以通过 programId 计算新消息，然后您可以通过简单的更新子句进行更新。