mysql - 如何选择2个随机类别数据

Question

我的表结构如下(含数据):

+------------------------------------+
|id    fname     category  city      |
|1     ram       cook      kathmandu |
|2     shyam     cook      lalitpur  |
|3     krishna   maid      patan     |
|4     jay       maid      kupondole |
|5     rajan     maid      ratnapark |
+------------------------------------+

查询应该一次返回 2 行(cook 和 maid)，其中有随机的 cook 和 maid。我该怎么做？

Answer 1

两种选择:

(SELECT * FROM TableName WHERE category='cook' ORDER BY rand() limit 1)
UNION ALL
(SELECT * FROM TableName WHERE category='maid' ORDER BY rand() limit 1)

在 SQL Fiddle 中查看结果.

或

SELECT * FROM TableName 
WHERE category IN ('cook','maid') 
GROUP BY category 
ORDER BY rand() limit 2

在 SQL Fiddle 中查看结果.

结果:

ID  FNAME   CATEGORY    CITY
1   ram     cook        kathmandu
5   rajan   maid        ratnapark