这是我的表
id | question |
id被分组为
1000 -> 1050
2000 -> 2030
3000 -> 3099
4000 -> 4500
5000 -> 5010
我必须提取5条记录,每组一个id。
如何仅用一个查询执行此操作?
SELECT f1, f2, ... FROM tablename WHERE id BETWEEN 1000 AND 1050 ORDER BY RAND() LIMIT 1
UNION
SELECTS f1, f2, ... FROM tablename WHERE id BETWEEN 2000 AND 2030 ORDER BY RAND() LIMIT 1
UNION
SELECTS f1, f2, ... FROM tablename WHERE id BETWEEN 2000 AND 2030 ORDER BY RAND() LIMIT 1
和两个以上的选择与UNION
SELECT *, IF (id > 1000 AND id < 1050, 1,
IF (id > 2000 AND id < 2030, 2,
IF (id > 3000 AND id < 3099, 3,
IF (id > 4000 AND id < 4500, 4,
IF (id > 5000 AND id < 5010, 5, 0))))) AS param
FROM questions WHERE param > 0 GROUP BY param
只需一个查询(不使用NedretRecap建议的UNION
),您可以将group_id
(或类似列)添加到您的表中,并使用简单的... GROUP BY group_id
。
假设保留的组大小是总是相同(有些组将从以前的+1000
开始,而其他组将从+300
开始是不可能的),您可能可以使用这个简单的hack:
SELECT id
FROM table
GROUP BY FLOOR(id/1000)