如何在MySQL中将Rand函数与Union结合使用 [英] How to use Rand function combined with Union in MySQL

问题描述

我正在尝试合并3个选择语句的结果，这些语句将从不同的食物类别中选择不同的食物名称.但是，我的表中有很少的"foodItems"实例.我只需要为每个选择语句选择一项，我希望它可以随机完成.我不太确定该如何实现，我尝试在每个select语句的末尾插入ORDER BY RAND() LIMIT 1，但是MySQL不喜欢它.有人可以帮忙吗

I'm trying to combine a result from 3 select statements that will choose different food names from different food categories. However my table has quite few instances of "foodItems". I need to select only 1 item for each of the select statements and I want it to be done randomly. I'm not quite sure how to achieve that I've tried inserting ORDER BY RAND() LIMIT 1 at the end of each select statement but MySQL doesn't like it. Can somebody help

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

推荐答案

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

You could create an outer SQL where the from clause is some sort of temp table like this:

 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    

这篇关于如何在MySQL中将Rand函数与Union结合使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！