php - 为 WHERE 条件选择相等数量的记录

Question

我有一个数据库，里面有超过一百万条记录。对于 3 种不同的条件，我想从数据库中获取相同数量的记录。我目前正在使用执行 3 个 MySQL 查询的代码。我想知道是否有可能使用一个 SELECT 查询获得相同的结果？

以下代码为每个 site=X 条件选择相等数量的 20 条记录。

<?php
/*---------*/
$db1 = mysqli_query($connect, "SELECT * FROM news WHERE site='1' ORDER BY id DESC LIMIT 0,20");

$db2 = mysqli_query($connect, "SELECT * FROM news WHERE site='2' ORDER BY id DESC LIMIT 0,20");

$db3 = mysqli_query($connect, "SELECT * FROM news WHERE site='3' ORDER BY id DESC LIMIT 0,20");

/*------------*/

?>

Answer 1

您可以使用 UNION ALL合并多个 Select 查询的结果。我们需要确保在所有 Select 查询中返回的列数相同。

(SELECT * FROM news WHERE site='1' ORDER BY id DESC LIMIT 0,20)
UNION ALL
(SELECT * FROM news WHERE site='2' ORDER BY id DESC LIMIT 0,20)
UNION ALL
(SELECT * FROM news WHERE site='3' ORDER BY id DESC LIMIT 0,20)

现在，请永远记住 UNION 的结果将始终是一个无序数据集。如果您希望最终结果按 id 排序；您可以在末尾添加明确的 Order By。在 Docs 中对此进行了很好的讨论。 :

However, use of ORDER BY for individual SELECT statements implies nothing about the order in which the rows appear in the final result because UNION by default produces an unordered set of rows. Therefore, the use of ORDER BY in this context is typically in conjunction with LIMIT, so that it is used to determine the subset of the selected rows to retrieve for the SELECT, even though it does not necessarily affect the order of those rows in the final UNION result. If ORDER BY appears without LIMIT in a SELECT, it is optimized away because it will have no effect anyway.