如何在唯一id与其他7个查询不匹配的情况下从MySQL查询中提取一行

Question

我有一个新闻故事数据库，我有一个阅读率最高的部分。我的新闻显示在8个故事的区块中。前7篇来自下面的MySQL查询，显示了过去7天阅读量最高的7篇新闻。这和我预期的一样，显示了阅读量最高的7个新闻故事。

$query = "
SELECT * 
    FROM news 
 WHERE STR_TO_DATE(day, '%d %M %Y') >= DATE_SUB(curdate(), INTERVAL 1 WEEK) 
 ORDER 
      BY mostRead DESC 
 LIMIT 7
";

$results = mysqli_query($dbc, $query)or die('Error querying database');

while ($rows = mysqli_fetch_array($results)){

echo news stories

}

我正在为如何形成下一个MySQL查询而苦苦挣扎。我需要显示的第8个新闻故事需要是我的数据库中的第一个故事，而不是阅读量最高的7个新闻故事。这是为了避免8个故事中的重复。

例如，如果一个新的新闻故事有大量的点击量，我不希望它出现在前7名，然后又出现在第8名，因为它仍然是最新的条目。

Answer 1

如果您运行的是MySQL 8.0，则可以使用row_number()或rank()执行以下操作：

order by
    case when rank() over(order by mostRead desc) <= 7,
        then mostRead 
        else 0
    end desc,
    str_to_date(day, '%d %M %Y') desc
limit 8

第一个排序条件中的条件表达式如果当前行排在前7位，则返回mostRead，否则返回0 (因此所有其他行都是并列的)。然后，第二个排序条件使用该日期。剩下要做的就是将结果集限制为8行。

Answer 2

您可以使用UNION向结果集中添加额外的行。这些查询将适用于较旧的MySQL版本(在5.7.26上测试)。

有几个选项可以实现这一点，您可以从表中添加前8行，并让UNION过滤重复项，然后通过将LIMIT 8添加到查询的末尾来仅选择一条记录：

(SELECT *
   FROM news
   WHERE STR_TO_DATE(day, '%d %M %Y') >= DATE_SUB(curdate(), INTERVAL 1 WEEK)
   ORDER BY mostRead DESC
   LIMIT 7)
UNION
  (SELECT *
   FROM news
   ORDER BY str_to_date(day, '%d %M %Y') DESC
   LIMIT 8)
LIMIT 8

或者，您可以自己筛选行，这更适合高级用例。

  (SELECT *
   FROM news
   WHERE STR_TO_DATE(DAY, '%d %M %Y') >= DATE_SUB(curdate(), INTERVAL 1 WEEK)
   ORDER BY mostRead DESC
   LIMIT 7)
UNION
  (SELECT *
   FROM news
   WHERE id NOT IN
       (SELECT id
        FROM
          (SELECT *
           FROM news
           WHERE STR_TO_DATE(day, '%d %M %Y') >= DATE_SUB(curdate(), INTERVAL 1 WEEK)
           ORDER BY mostRead DESC
           LIMIT 7) temp)
   ORDER BY str_to_date(day, '%d %M %Y') DESC
   LIMIT 1)

我们添加了temp表，因为旧版本的MySQL不支持LIMIT &IN/ALL/ANY/SOUN子查询。

Answer 3

，我需要显示的第8个新闻故事需要是我的数据库中的第一个故事，它也不在阅读率最高的7个新闻故事中。

这是你想要的吗？

SELECT n.* 
FROM news n
WHERE STR_TO_DATE(day, '%d %M %Y') >= DATE_SUB(curdate(), INTERVAL 1 WEEK) 
ORDER BY mostRead DESC 
LIMIT 8
------^ this is the change

如果您的应用程序中已经有了7，并且需要获取一个不同的ids，那么您需要处理ids：

SELECT n.* 
FROM news n
WHERE STR_TO_DATE(day, '%d %M %Y') >= DATE_SUB(curdate(), INTERVAL 1 WEEK) AND
     n.id NOT IN ($id1, $id2, $id3, $id4, $id5, $id6, $id7)
ORDER BY mostRead DESC 
LIMIT 1