Geek Answers Handbook

ORDER BY RAND () for multiple columns (moving the contents of each column vertically)

I am looking for a mysql solution so that multiple columns output a random field from this column.

The query that I have now only randomly selects the entire row, but not randomizes the divided columns.

$sql = "SELECT col1, col2, col3, col4 FROM table ORDER BY RAND() limit 4";

I tried the subqueries, but I am not familiar with this, so if someone can help ...

+4
source share
4 answers

Try the following:

SELECT CASE rnd
          WHEN 1 THEN col1
          WHEN 2 THEN col2
          WHEN 3 THEN col3
          WHEN 4 THEN col4       
       END AS col          
FROM (       
  SELECT col1, col2, col3, col4,
         FLOOR(RAND() * 4) + 1 AS rnd
  FROM mytable 
ORDER BY RAND() ) AS t

The expression FLOOR(RAND() * 4) + 1generates a random number from 1 to 4 (inclusive). An external query uses this number to randomly select one of the 4 columns of the table.

Demo here

Edit:

, :

SELECT CASE FIND_IN_SET(1, rnd)
          WHEN 1 THEN col1
          WHEN 2 THEN col2
          WHEN 3 THEN col3
          WHEN 4 THEN col4
       END AS c1,
       CASE FIND_IN_SET(2, rnd)
          WHEN 1 THEN col1
          WHEN 2 THEN col2
          WHEN 3 THEN col3
          WHEN 4 THEN col4
       END AS c2,
       CASE FIND_IN_SET(3, rnd)
          WHEN 1 THEN col1
          WHEN 2 THEN col2
          WHEN 3 THEN col3
          WHEN 4 THEN col4
       END AS c3,
       CASE FIND_IN_SET(4, rnd)
          WHEN 1 THEN col1
          WHEN 2 THEN col2
          WHEN 3 THEN col3
          WHEN 4 THEN col4
       END AS c4
FROM (  
  SELECT col1, col2, col3, col4, 
         (SELECT GROUP_CONCAT(i ORDER BY RAND()) 
          FROM (SELECT 1 AS i UNION ALL SELECT 2 UNION ALL 
                SELECT 3 UNION ALL SELECT 4) t) AS  rnd
  FROM mytable) AS t

+2

, , 16 ( 4x4).

SELECT 
  (SELECT col1 FROM `table` ORDER BY RAND() LIMIT 1) AS col1,
  (SELECT col2 FROM `table` ORDER BY RAND() LIMIT 1) AS col2,
  (SELECT col3 FROM `table` ORDER BY RAND() LIMIT 1) AS col3,
  (SELECT col4 FROM `table` ORDER BY RAND() LIMIT 1) AS col4
FROM `table`
LIMIT 4
+2

, , PHP, , , , , . 4 / .

GROUP_CONCAT 1,2,3,4, . , FIND_IN_SET. col , ELT().

SELECT 
       ELT(FIND_IN_SET(1,rand_indexes),col1,col2,col3,col4) as col1,
       ELT(FIND_IN_SET(2,rand_indexes),col1,col2,col3,col4) as col2,
       ELT(FIND_IN_SET(3,rand_indexes),col1,col2,col3,col4) as col3,
       ELT(FIND_IN_SET(4,rand_indexes),col1,col2,col3,col4) as col4
FROM 
  (SELECT col1,col2,col3,col4,
     (SELECT GROUP_CONCAT(i ORDER BY RAND()) as indexes FROM
       (SELECT 1 as i UNION SELECT 2 UNION SELECT 3 UNION SELECT 4)indexes
      )as rand_indexes
   FROM `table`
   )T1 
ORDER BY RAND() limit 4

sqlfiddle

UPDATE , , . 4 , 4 ...

SELECT T1.col1,T2.col2,T3.col3,T4.col4
FROM
  (SELECT col1,@order1:=@order1+1 as i 
   FROM (SELECT col1 FROM `table` ORDER BY RAND() LIMIT 4) O1,(SELECT @order1:=0) initialize )T1
  INNER JOIN
  (SELECT col2,@order2:=@order2+1 as i 
   FROM (SELECT col2 FROM `table` ORDER BY RAND() LIMIT 4) O1,(SELECT @order2:=0) initialize )T2
  ON T1.i = T2.i
  INNER JOIN
  (SELECT col3,@order3:=@order3+1 as i 
   FROM (SELECT col3 FROM `table` ORDER BY RAND() LIMIT 4) O1,(SELECT @order3:=0) initialize )T3
  ON T1.i = T3.i
  INNER JOIN
  (SELECT col4,@order4:=@order4+1 as i 
   FROM (SELECT col4 FROM `table` ORDER BY RAND() LIMIT 4) O1,(SELECT @order4:=0) initialize )T4
  ON T1.i = T4.i

sqlfiddle

+1

, , :

select * from
    (select col1 from table order by rand()) as a, 
    (select col2 from table order by rand()) as b,
    (select col3 from table order by rand()) as c,
    (select col4 from table order by rand()) as d
order by rand()
limit 4;

- 4 ( n ^ 4 ), 4 .

, a/b/c/d:

a, b .. ( ) 4 () . , . , MySQL (, , ) .

, , uniqe, , :

select concat(a.col1, ' ', c.col1), b.col2, d.col2 from
    (select col1 from table order by rand()) as a, 
    (select col2 from table order by rand()) as b,
    (select col1 from table order by rand()) as c,
    (select col2 from table order by rand()) as d
where b.col2 > d.col2
order by rand()
limit 4;

a to d , . col1 1- 3- .

@PaulSpiegel answer , , limit 1 .

0

More articles:


All Articles