从MySQL的一行中随机选择用户 - SELECT random users from MySQL in one row

SELECT random users from MySQL in one row

本文关键字：一行随机选择用户 MySQL | 更新日期: 2023-09-27

我正在优化查询

(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

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

为提高性能重写如下:

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']; 
                     }

返回空结果，我错过了什么，连接到db是ok的(另一个查询正在工作)

解决方案如下:

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;

演示

为了随机选择两条记录，我通常使用

// Whatever your query is
order by rand() 
limit 2

然后你可以从这两个随机记录中做任何你需要的计算。

尝试使用prepare()和execute():

$stmt = $dbh->prepare(
  "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");
$stmt->execute(array($target));
$rs = $stmt->fetchAll();

单个查询而不是两个查询如何:减少rand()计算

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(select *, rand() randval from users) C1, users C2
  WHERE C1.id <> C2.id AND ABS(C1.total - C2.total) < 200
  ORDER BY C1.randval DESC
  LIMIT 1;