如何在MySQL中选择随机行?

mytable

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

This is my table structure. Here I want to select randomly 4 rows so I use RAND() mysql function in my query my questions is

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

How do I pair to rows. I mean, I wanna select pid 2 and 3 always one ofter another. I need in bellow order. i don't want to break the pair A2 A3

如何对行进行配对。我的意思是，我想选择pid 2和3总是一个接一个。我需要秩序井然。我不想破坏A2 A3

A1 A2 A3 A4 or A2 A3 A4 A1 or A2 A3 A4 A5 or A4 A5 A2 A3 and etc

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

I used the query below but it's not working for me

我使用了下面的查询，但它不适合我。

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

9 个解决方案

#1

turbod was close with his answer, he was just ordering randomly, when it seems you wanted to order by pid, after getting the random rows you wanted in conjunction with the ones concerning A2 and A3:

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`

#2

Generally, using ORDER BY RAND() is not a good idea. Please read the text by Jan Kneschke, showing why: http://jan.kneschke.de/projects/mysql/order-by-rand/

通常，使用RAND()的ORDER不是一个好主意。请阅读Jan Kneschke的文章，说明原因:http://jan.kneschke.de/projects/mysql/order-by-rand/

#3

I ran a heavy test on this, passed.

我做了一个很重的测试，通过了。

(
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"

This will do the job very well. But to make the result even more random, execute that statement with replacing that 0.5 value in 1st line with a random value chosen by your client application code like mt_rand(0, 1000000) / 1000000 in PHP . Make sure it falls between 0 and 1. But do NOT use mysql function rand() in place of that 0.5 because it will make A2 and A3 apart from each other. The trick is assigning a random value for "ordercol" in all rows but keep it same for A2 and A3

这将很好地完成这项工作。但是为了使结果更加随机，执行该语句时，在第一行将0.5的值替换为由客户端应用程序代码(如PHP中的mt_rand(0,1000000) / 1000000)选择的随机值。确保它在0和1之间。但是不要使用mysql函数rand()来代替0.5，因为它将使A2和A3彼此分离。诀窍是在所有行中为“ordercol”分配一个随机值，但在A2和A3中保持相同

EDIT: I believe we can replace the 0.5 value with a LEFT JOIN even instead of relying on discrete value by PHP, as we replace the first segment of the union, so the whole query becomes:

编辑:我相信我们可以用一个左连接代替0.5的值，而不是用PHP来依赖离散值，因为我们替换了union的第一部分，所以整个查询变成:

(
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"

#4

I doubt there is a sane way to this in MySQL only.

我怀疑只有在MySQL中才有这种方法。

I can think of one way of doing it, assuming you are using PHP/MySQL:

我可以想出一种方法，假设你使用的是PHP/MySQL:

Essentially you query everything but A3, then put A3 next to A2

基本上，除了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);
          }
      }

Now $data will contain your results in the desired order.

现在$data将以期望的顺序包含结果。

#5

If you are always selecting all the rows in the table:

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

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;

If you are not always returning all the rows, thus need to check if A3 was returned to know if A2 should be included:

如果不总是返回所有的行，则需要检查是否返回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;

#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()

That should do it.

应该做的。

#7

Here is my solution:

这是我的解决方案:

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()

#8

SELECT *, RAND() "xrand" FROM yourtable A ORDER BY xrand LIMIT 4

SELECT *,兰德()“xrand”从你的桌子上按xrand限额4下订单

#9

-1

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

i think this will satisfy your need.

我想这将满足你的需要。

#1