主页 / dba / 问题 / 55878
Accepted
dotancohen
Asked: 2014-01-01 03:22:40 +0800 CST

从列表中获取所有项目，这些项目在日志表中没有“已发送”的成功日志条目

  • 772

如何检索在加入时没有特定字段 ( sent)，或者如果它们确实有该字段 ( sent) 然后failure_reason每一sent行都有一个附加字段 ( ) 的项目？

考虑一个有两个表的数据库：

list
  id INT
  name VARCHAR

log
  id INT
  list_id INT REFERENCES (list.id)
  action VARCHAR
  failure_reason VARCHAR

典型list内容：

ID | Name

1  | John
2  | Paul
3  | George
3  | Atwood

典型log内容：

ID | List_id | Action  | Failure Reason
---+---------+---------+---------------
1  | 1       | entered | NULL
2  | 1       | sent    | NULL

3  | 2       | entered | NULL
4  | 2       | sent    | Connection Error
5  | 2       | sent    | NULL

6  | 3       | entered | NULL
7  | 3       | sent    | Cosmic Ray

8  | 4       | entered | NULL

可以看出 List_id1有 和 的日志条目entered，sent没有failure_reason. 因此，此项目已由我负责。

同样，List_id2有一个log条目用于entered，还有两个条目用于sent。这是因为第一次sent失败了。我们知道第一次发送失败，因为failure_reason它不是 NULL。既然有sent排成功，这一项也就交给我负责了。

但是，List_id3有一个log条目entered，但唯一的sent一行是失败的。因此，这个项目仍然是我的责任，应该在查询中检索。

此外，List_id4有一个log条目entered，但没有sent行。因此，这个项目仍然是我的责任，应该在查询中检索。

我曾尝试使用一些子查询魔术来根据sent值获取/排除行，但是随着该表变得越来越大（预计每天有数千条新记录），我需要避免需要返回整个数据库中所有结果的查询。

这是在 CentOS 6.x 上的 MySQL 5.1 中。

mysql join

1 个回答

  1. Best Answer

    查询本身似乎非常简单（根据提供的数据样本，我假设带有 action:sent 、 failure_reason:NULL 的记录是给定 list_id 的最终状态）：

    SELECT a.*
FROM list a
WHERE NOT EXISTS (SELECT NULL FROM log b WHERE b.list_id = a.list_id
and b.action='Sent' and b.failure_reason IS NULL)

    然而，如果表很大，即使你在日志表中有覆盖索引，性能也会受到影响，这可能不是很好——名称“日志”意味着你主要是向其中插入数据（并且插入必须尽可能快） , 所以每个额外的索引都会破坏这个表的主要目的。
    在我看来，尽管模型中缺少日期/日期时间字段；我想您通常希望在特定的（可能相对较小的）日期间隔内执行失败的任务。

    • 2

相关问题