如何在MySQL中选择随机行? [英] How do I select random rows in MySQL?

问题描述

mytable

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

这是我的表结构.在这里我想随机选择4行，因此我在查询中使用RAND()mysql函数 我的问题是

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

如何配对到行.我的意思是，我想始终选择pid 2和3. 我需要按以下顺序. 我不想打破配对A2 A3

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

A1 A2 A3 A4或A2 A3 A4 A1或A2 A3 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

推荐答案

turbod与他的答案很接近，他只是随机排序，当您似乎想要按pid排序时，在获取了您想要的随机行后，关于A2和A3的:

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:

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

这篇关于如何在MySQL中选择随机行?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！