在一行中从 MySQL 中选择随机用户 [英] SELECT random users from MySQL in one row

问题描述

我正在优化查询

(从 1 个表中随机选择 2 个用户)

(select 2 random users from 1 table)

id  | name |  total | img
------------------------ --
1    user1   500      1
2    user2   600      2
3    user3   650      3

需要结果和 ABS(total1 -total2) <200

need result and ABS(total1 -total2) < 200

id1  | id2|  name1 | name2 | img1 | img2 |  total1 | total2
------------------------ -------------------------------------
1       3    user1   user3    1      3        500      650

为了获得更好的性能，重写这个:

for more performance rewrite this:

SELECT
  C1.id AS id1, C1.img AS img1, C1.name AS name1,
  C2.id AS id2, C2.img AS img2, C2.name AS name2,
  C1.total AS total1, C2.total AS total2
FROM users C1, users C2
WHERE C1.id <> C2.id 
  AND ABS(C1.total - C2.total) < 200
ORDER BY RAND()
LIMIT 1

到

$dbh = new PDO ("mysql:host=$hostname;dbname=$dbname","$username","$pw");
$rs = $dbh->query(
"SELECT COUNT(*) AS 'count'
 FROM users C1, users C2
 WHERE C1.id <> C2.id
   AND ABS(C1.total - C2.total) < 200");
$target = rand(0,$rs[0]['count']);
$rs = $dbh->query(
  "SELECT 
   C1.id AS id1, C1.img AS img1, C1.name AS name1,
   C2.id AS id2, C2.img AS img2, C2.name AS name2,
   C1.total AS total1, C2.total AS total2
  FROM users C1, users C2
  WHERE C1.id <> C2.id
   AND ABS(C1.total - C2.total) < 200
  LIMIT ?,1",
array($target));


foreach ($rs as $row) 
                    { 
                     print $row['name1']; 
                     }

返回 empry 结果，我错过了什么，与数据库的连接正常(另一个查询正在工作)

return empry result, what I missed, connection to db is ok (another queries are working)

推荐答案

这是您的解决方案:

SELECT u1.id1, u2.id2, u1.name1, u2.name2, u1.total1, u2.total2, u1.img1, u2.img2 
FROM 
(
    SELECT u.id id1, u.name name1, u.total total1, u.img img1 
    FROM users u 
    ORDER BY RAND()
) u1
CROSS JOIN
(
    SELECT u.id id2, u.name name2, u.total total2, u.img img2 
    FROM users u 
    ORDER BY RAND()
) u2
WHERE u1.id1 != u2.id2 AND ABS(u1.total1 - u2.total2) < 200
LIMIT 0,1;

演示

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