mysql - 从具有 2 个条件的表中删除不需要的行

Question

我有一个多对多关系表，我需要删除不需要的行。lastviews 表的结构是:

| user (int) | document (int) | time (datetime) |

此表记录了最后查看文档的用户。 (user, document) 是唯一的。我只显示文档的最后 10 个 View ，直到现在我删除了不需要的，如下所示:

DELETE FROM `lastviews` WHERE `document` = ? AND `user` NOT IN (SELECT * FROM (SELECT `user` FROM `lastviews` WHERE `document` = ? ORDER BY `time` DESC LIMIT 10) AS TAB)

但是，现在我还需要显示用户 查看过的最后 5 个 文档。这意味着我不能再使用以前的查询删除行，因为它可能会删除我需要的信息(假设用户在 5 分钟内没有查看文档并且行被删除)

总而言之，我需要删除所有不在这两个查询结果中的记录:

SELECT ... FROM `lastviews` WHERE `document` = ? ORDER BY `time` DESC LIMIT 10

和

SELECT * FROM `lastviews` WHERE `user` = ? ORDER BY `time` DESC LIMIT 0, 5

我需要逻辑。

Answer 1

查询中的 OR 能否解决您的问题？

像这样:

DELETE FROM `lastviews` WHERE `document` = ? AND (`user` NOT IN (SELECT * FROM (SELECT `user` FROM `lastviews` WHERE `document` = ? ORDER BY `time` DESC LIMIT 10) AS TAB) OR 'user' NOT IN (SELECT * FROM (SELECT 'user' FROM `lastviews` WHERE `user` = ? ORDER BY `time` DESC LIMIT 0, 5) AS TAB))

所以它应该只删除不包含在你需要的两个 SELECT 中的记录