如何在MySQL中选择随机行？

Question

mytable

pid name field
=== ==== =====
1    A1   0
2    A2   1
3    A3   1
4    A4   0   
5    A5   0

这是我的表结构。我想要随机选择4行，所以在查询中使用了MySQL的RAND()函数。 我的问题是：

如何将两行配对。我的意思是，我想始终选择pid 2和3作为一对。 我需要以下的顺序。我不想破坏A2和A3的配对关系。

A1 A2 A3 A4 或 A2 A3 A4 A1 或 A2 A3 A4 A5 或 A4 A5 A2 A3 等等

我使用了下面的查询，但它对我没有用。

SELECT * FROM mytable ORDER BY RAND() ASC limit 0,4

- Gowri

---

一旦您获得了一个数组，那么您就可以随意打乱其中的元素。我可以为此粘贴代码吗？ - xkeshav

你的意思是你不想在字段= 1的地方换行吗？还是说这真的与名称列有关？ - Clodoaldo Neto

是的，你说得对，我不想破坏 field = 1 列... 我也想知道有多少对，请发表你的想法。 - Gowri

使用存储过程。按RAND()排序即使在索引列上也非常缓慢，不是一个好的实践。这是一个没有答案的问题，因为我已经寻找了很长时间（没有PLs）。 - AbiusX

Answer 1

turbod的回答很接近正确，只是他随机排列了结果。看起来你想按pid排序，在得到所需的随机行与A2和A3相关的行之后：

(
    SELECT *
    FROM `mytable`
    WHERE 
        name ='A2' OR 
        name ='A3'
    LIMIT 2
)
UNION
(
    SELECT DISTINCT *
    FROM `mytable`
    WHERE 
        name !='A2' OR 
        name !='A3'
    ORDER BY RAND( ) LIMIT 2
) 
ORDER BY `pid`

- Mike S

---

这将使A2和A3始终位于第一行和第二行。 - doc_id

不，实际上不会。如果A1也被选中，它将首先通过Union中的第二个查询，它将出现在A2和A3之前。话虽如此，如果他希望A2和A3始终处于第二和第三位置，并且随机行既在其之前又在其之后，那么必须修改我的查询以使用两个联合，首先选择一个随机行，然后是A2和A3，然后再选择另一行随机行，并将随机选择限制为1行，并确保第二个随机选择不使用与第一个相同的值。 - Mike S

Answer 2

通常使用ORDER BY RAND()不是一个好主意。请阅读Jan Kneschke的文章，了解为什么：http://jan.kneschke.de/projects/mysql/order-by-rand/

- Isotopp

Answer 3

我对此进行了严格的测试，测试结果通过。

(
SELECT * , 0.5 AS ordercol
FROM `mytable`
WHERE `name`IN ( "A2", "A3" )
LIMIT 2
)
UNION (
SELECT * , rand() AS ordercol
FROM `mytable`
WHERE `name` NOT IN ( "A2", "A3" )
LIMIT 2
)
ORDER BY ordercol, `name` IN ( "A2", "A3" ) , `name` ="A3"

这将非常好地完成工作。但是为了使结果更加随机，可以在第一行中用客户端应用程序代码选择的随机值（如PHP中的mt_rand(0, 1000000) / 1000000）替换0.5值来执行该语句。确保它落在0和1之间。但不要使用mysql函数rand()代替0.5，因为它会使A2和A3相互分离。诀窍是为所有行分配“ordercol”的随机值，但对于A2和A3保持相同 编辑： 我认为我们可以用LEFT JOIN代替依赖于PHP的离散值，因为我们替换了联合查询的第一个部分，所以整个查询变成了：

(
SELECT mt1.* , mt2.ordercol AS ordercol
FROM `mytable` AS mt1
LEFT JOIN (

SELECT RAND( ) AS ordercol
) AS mt2 ON TRUE
WHERE `name`
IN (
"A2", "A3"
)
LIMIT 2
)
UNION (
SELECT * , rand() AS ordercol
FROM `mytable`
WHERE `name` NOT IN ( "A2", "A3" )
LIMIT 2
)
ORDER BY ordercol, `name` IN ( "A2", "A3" ) , `name` ="A3"

- doc_id

Answer 4

我怀疑在MySQL中只有一种明智的方法。

我可以想到一种方法，假设您正在使用PHP/MySQL：

基本上，您查询除A3之外的所有内容，然后将A3放在A2旁边。

$res = mysql_query("SELECT name, field FROM mytable WHERE name <> 'A3' ORDER BY RAND()");
$res2 = mysql_query("SELECT name, field FROM mytable WHERE name = 'A3'");

$data = array();
while($row = mysql_fetch_array($res))
      {
      array_push($data, $row);

      if ($row['name'] == "A2")
          {
          $row2 = mysql_fetch_array($res2);
          array_push($data, $row2);
          }
      }

现在$data将包含你想要的顺序中的结果。

- nico

---

这是一个在PHP中很棒的解决方案。干得好！ - doc_id

Answer 5

如果您总是选择表中的所有行：

SELECT pid, name, field, idx
FROM (
    SELECT pid, name, field,
        @pos := IF(name = 'A3', @idx, @pos),
        @idx := @idx + IF(name = 'A3', 2, 1), idx
    FROM mytable, (SELECT @pos = -1, @idx := 0) dm
    WHERE name <> 'A2'
    ORDER BY RAND()
)
UNION SELECT pid, name, field, @pos + 1 idx
FROM mytable
WHERE name = 'A2'
ORDER BY idx;

如果您并非总是返回所有行，则需要检查是否已返回 A3 以判断是否应包括A2：

SELECT pid, name, field, idx
FROM (
    SELECT pid, name, field,
        @pos := IF(name = 'A3', @idx, @pos),
        @idx := @idx + IF(name = 'A3', 2, 1), idx
    FROM mytable, (SELECT @pos = -1, @idx := 0) dm
    WHERE name <> 'A2'
    ORDER BY RAND()
    LIMIT 4
)
UNION SELECT pid, name, field, @pos + 1 idx
FROM mytable
WHERE @pos != -1 AND name = 'A2'
ORDER BY idx;

- The Scrum Meister

Answer 6

SELECT * FROM (
  SELECT DISTINCT name FROM mytable
  WHERE name <> 'A2' AND name <> 'A3'
  ORDER BY RAND()
  LIMIT 0,2
UNION 
  SELECT DISTINCT name FROM mytable
  WHERE name = 'A2' OR name = 'A3'
  ORDER BY name
)whateverQueryAlias
ORDER BY RAND()

应该可以了。

- fancyPants

---

您的查询显示出了“UNION和ORDER BY的使用不正确”的错误。 - Gowri

哦，我发现另一个错误。最后的ORDER BY RAND()可能会破坏A2 A3组合。现在有其他事情要做。稍后再回来深入思考一下。 - fancyPants

有什么建议吗？我迫不及待地想知道。 - Gowri

除了MySQL，你还使用什么？PHP？在任何编程语言中都可以更容易地完成。我想知道是否仅使用SQL就可以实现。抱歉，我现在没有时间去弄清楚这个问题。 - fancyPants

即使修复了错误，这个查询仍然会将A2/A3作为最后两个结果... - nico

Answer 7

这是我的解决方案：

SELECT *
FROM `mytable`
WHERE name ='A2'
OR name ='A3'
LIMIT 2
UNION
(SELECT DISTINCT *
FROM `mytable`
WHERE name !='A2'
OR name !='A3'
ORDER BY RAND( ) LIMIT 2) ORDER BY RAND()

- turbod

---

就像tombom的回答一样，你的查询总是将A2和A3作为第一个结果返回，这不是OP所要求的。 - nico

好的...现在你正在重新洗牌，所以A3不能保证在A2之后。 - nico

Answer 8

从你的表A中选择*, RAND() "xrand"，然后按照xrand进行排序，并限制结果返回4条。

- root

Answer 9

SELECT * FROM `mytable` order by rand(), name asc limit 4.

我认为这将满足您的需求。

- Sakthi

---

这是什么问题？我尝试过了才发布的。 - Sakthi

@Sakthi：这从表格中选择四个随机值。准确阅读问题：OP希望在“A2”之后始终拥有“A3”，而您的解决方案根本没有考虑到这一点。 - nico

我看了那个问题，也尝试了同样的例子。我得到了输出。如果你给定了名称asc，那么你肯定会得到输出。 - Sakthi

@Sakthi：我试过了，我得到了例如A4，A1，A6，A2和A6，A2，A7，A3。（请注意，如果您想使其真正有意义，您需要向表中添加几行）（使用MySQL v14.14） - nico

哦..！现在我明白了你的意思。感谢你的指导，兄弟。 - Sakthi