如何在MySQL中结合使用Rand函数和Union

Question

我试图结合3选择语句的结果，将从不同的食品类别中选择不同的食品名称。然而，我的桌子上有相当多的"食品"实例。我需要为每个选择语句只选择1个项目，我希望它是随机完成的。我不太确定如何实现，我已经尝试在每个选择语句的末尾插入ORDER BY RAND() LIMIT 1，但MySQL不喜欢它。谁能帮帮我?

SELECT foodName
FROM food
WHERE foodCategory_id = 1 OR 2
UNION
SELECT foodName
FROM food
WHERE foodCategory_id = 7 OR 8 OR 9
UNION
SELECT foodName
FROM food
WHERE foodCategory_id = 10 OR 11

Answer 1

您可以创建一个外部SQL，其中from子句是某种临时表，如下所示:

 SELECT food_name from (
  SELECT foodName
  FROM food
  WHERE foodCategory_id = 1 OR 2
  ORDER BY RAND() limit 1) a
 UNION
 SELECT food_name from (
  SELECT foodName
  FROM food
  WHERE foodCategory_id = 7 OR 8 OR 9
  ORDER BY RAND() limit 1) b
 UNION
 SELECT food_name from (
  SELECT foodName
  FROM food
  WHERE foodCategory_id = 10 OR 11
  ORDER BY RAND() limit 1) c    

Answer 2

SELECT foodname FROM food WHERE .. ORDER BY RAND() LIMIT 0,1;