mysql - 选择 5 行，如果它们不在前 3 行

Question

我想从表格中选择 5 篇不是最新的 3 篇文章。

下面是我如何从名为 article 的表中获取 5 篇文章:

SELECT * FROM article WHERE FOREIGN_ID_MENU = '".$id."' AND active = '1' ORDER BY date_created DESC LIMIT 0, 5;

以及我如何从同一张表中获取前 3 篇文章:

SELECT * FROM article WHERE active = '1' ORDER BY date_created DESC LIMIT 0, 3;

我如何结合这些查询来选择 5 篇不是最新的 3 篇文章？我使用 PHP 执行此操作。

Answer 1

您可以使用 UNION 并为查询使用大括号以使用每个查询的限制

(SELECT * FROM article WHERE active = '1' 
AND FOREIGN_ID_MENU != '".$id."'
ORDER BY date_created DESC LIMIT 0, 3)
UNION
(SELECT * FROM article WHERE 
FOREIGN_ID_MENU = '".$id."' AND active = '1' 
ORDER BY date_created DESC LIMIT 0, 5)

编辑 来自评论

SELECT a.* FROM article a WHERE 
a.FOREIGN_ID_MENU = '".$id."' AND a.active = '1'
AND NOT EXISTS 
(SELECT 1 FROM article aa WHERE aa.active = '1' 
AND aa.id =a.id
ORDER BY aa.date_created DESC LIMIT 0, 3) 
ORDER BY a.date_created DESC LIMIT 0, 5