从具有 2 个条件的表中删除不需要的行
我有一个多对多关系表，我需要删除不需要的行。 lastviews 表的结构为：
| user (int) | document (int) | time (datetime) |
该表记录最后查看文档的用户。 （用户，文档）是唯一的。我只显示文档的最后 10 个视图，到目前为止我删除了不需要的内容，如下所示：
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
我需要逻辑。
I have a many-to-many relations table and I need to DELETE the unneeded rows.
The lastviews table's structure is:
| user (int) | document (int) | time (datetime) |
This table logs the last users which viewed the document. (user, document) is unique. I show only the last 10 views of a document and until now I deleted the unneeded like this:
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)
However, now I need to also show the last 5 documents a user has viewed. This means I can no longer delete rows using the previous query because it might delete information I need (say a user didn't view documents in 5 minutes and the rows are deleted)
To sum up, I need to delete all the records that aren't in the results of these 2 queries:
SELECT ... FROM `lastviews` WHERE `document` = ? ORDER BY `time` DESC LIMIT 10
and
SELECT * FROM `lastviews` WHERE `user` = ? ORDER BY `time` DESC LIMIT 0, 5
I need the logic.
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
您的查询中的 OR 可以解决您的问题吗？
像这样的东西：
所以它应该只删除您需要的两个 SELECT 中未包含的记录
can an OR in your query resolve your issue?
something like this:
so it should delete only the records that aren't contained in the two SELECT that you need
我要结束这个问题了。我没有找到我正在寻找的答案，但最终以另一种方式做了。
我决定删除这些 2 天前的记录，这样数据库中就不会有任何未使用的行。
I'm closing this question. I didn't find the answer I was looking for but ended doing it another way.
I decided to remove these records that are 2 days old so there won't be any unused rows in the database.