英文:
Random rows from MySql
问题
我有一个包含数千条记录的MySql数据库表。结构如下：
这里的列"difficulty"有三个可能的值，分别是A、B和C。
问题是我需要从整个表中检索10行随机记录，其中4行属于难度类型A，4行属于难度类型B，剩下的2行属于难度类型C。
我希望通过一次查询来实现这个目标。我不擅长编写SQL查询，正在寻找一些指导。
谢谢。
英文:
I have a table in MySql database with thousands of records. The structure is as below
Here the column "difficulty" has three possible values, which are A, B, and C.
Question is I need to retrieve 10 random rows from the entire table in which 4 rows are of one of difficulty type A, 4 rows are of difficulty type B and the remaining 2 rows of difficulty type C.
I want to achieve this with a single call. I am not good in writing SQL queries and looking for some guidance.
Thanks,
答案1
得分: 1
不确定我是否完全理解你的问题，但我认为这是你想要的。
(
从your_table中选择*
WHERE difficulty = 'A'
ORDER BY RAND()
LIMIT 4
)
UNION ALL
(
从your_table中选择*
WHERE difficulty = 'B'
ORDER BY RAND()
LIMIT 4
)
UNION ALL
(
从your_table中选择*
WHERE difficulty = 'C'
ORDER BY RAND()
LIMIT 2
)
这由三个单独的SELECT语句组成，通过UNION连接在一起。每个SELECT语句检索相应难度类型的指定数量的随机行。
英文:
Not sure if I fully understand your problem, but I think this is what you want.
(
SELECT * FROM your_table
WHERE difficulty = 'A'
ORDER BY RAND()
LIMIT 4
)
UNION ALL
(
SELECT * FROM your_table
WHERE difficulty = 'B'
ORDER BY RAND()
LIMIT 4
)
UNION ALL
(
SELECT * FROM your_table
WHERE difficulty = 'C'
ORDER BY RAND()
LIMIT 2
)
This consists of three separate SELECT statements connected by the UNION. Each SELECT statement retrieves the specified number of random rows for the corresponding difficulty type.
答案2
得分: -2
尝试类似以下方式：
(
SELECT * FROM table
WHERE difficulty='A'
ORDER BY RAND()
LIMIT 4
)
UNION ALL
(
SELECT * FROM table
WHERE difficulty='B'
ORDER BY RAND()
LIMIT 4
)
UNION ALL
(
SELECT * FROM table
WHERE difficulty='C'
ORDER BY RAND()
LIMIT 2
);
英文:
Try something like this:
<pre>(
SELECT * FROM table
WHERE difficulty='A'
ORDER BY RAND()
LIMIT 4
)
UNION ALL
(
SELECT * FROM table
WHERE difficulty='B'
ORDER BY RAND()
LIMIT 4
)
UNION ALL
(
SELECT * FROM table
WHERE difficulty='C'
ORDER BY RAND()
LIMIT 2
);
</pre>
通过集体智慧和协作来改善编程学习和解决问题的方式。致力于成为全球开发者共同参与的知识库，让每个人都能够通过互相帮助和分享经验来进步。
评论