如何将这3个查询合并为一个？

Hi i am trying to select 10x3 random id's from 3 different categories, but is there a smarter way to achieve this in just one query instead of 3 different queries?

$sql = "SELECT id FROM rating WHERE category IN (1) ORDER BY RAND() LIMIT 10";
$result1 = $dbCon->query($sql);

$arr;
while ($obj = $result1->fetch_object()) {
$arr[] = $obj->id;
}

$sql = "SELECT id FROM rating WHERE category IN (2) ORDER BY RAND() LIMIT 10";
$result1 = $dbCon->query($sql);

while ($obj = $result1->fetch_object()) {
$arr[] = $obj->id;
}

$sql = "SELECT id FROM rating WHERE category IN (3) ORDER BY RAND() LIMIT 10";
$result1 = $dbCon->query($sql);

while ($obj = $result1->fetch_object()) {
$arr[] = $obj->id;
}


var_dump($arr);

You can use UNION ALL, e.g.:

SELECT id FROM rating WHERE category IN (1) ORDER BY RAND() LIMIT 10

UNION ALL

SELECT id FROM rating WHERE category IN (2) ORDER BY RAND() LIMIT 10

UNION ALL

SELECT id FROM rating WHERE category IN (3) ORDER BY RAND() LIMIT 10

This is not easy. Your method might be the best approach. I mean, union all between the queries is marginally better, but given the overhead of doing the ORDER BY rand(), it is not going to be much improvement.

One alternative method in MySQL is to use variables:

SELECT id
FROM (SELECT r.*,
             (@rn := if(@c = category, @rn + 1,
                        if(@c := category, 1, 1)
                       )
             ) as rn
      FROM rating r CROSS JOIN
           (SELECT @c := -1, @rn := 0) params
      WHERE category IN (1, 2, 3)
      ORDER BY category, RAND()
     ) r
WHERE rn <= 10;

The advantage of this approach is that you don't have to specify the categories that you want explicitly. You can add new categories just by changing the IN list, or remove the WHERE clause entirely and get up to 10 random rows for all categories.