mysql - 根据没有分组依据的列值组限制接收到的结果数

Question

下面是我查询的当前输出:SELECT vans,icollaborate_id FROM mytable;

+==========+=====================+
|   vans   |   icollaborate_id   |
+==========+=====================+
| sachin   |   10                |
| cricket  |   10                |
| chess    |   10                |
| male     |   11                |
| 31-40    |   11                |
| sachin   |   11                |
| female   |   12                |
| tennis   |   12                |
| 21-30    |   12                |
+----------+---------------------+

现在我想生成一个返回相同输出但有限制的结果。例如，如果用户指定 limit=2，则输出应如下所示:

限制为 2 的预期输出

+==========+=====================+
|   vans   |   icollaborate_id   |
+==========+=====================+
| sachin   |   10                |
| cricket  |   10                |
| chess    |   10                |
| male     |   11                |
| 31-40    |   11                |
| sachin   |   11                |
+----------+---------------------+

限制为 3 的预期输出

+==========+=====================+
|   vans   |   icollaborate_id   |
+==========+=====================+
| sachin   |   10                |
| cricket  |   10                |
| chess    |   10                |
| male     |   11                |
| 31-40    |   11                |
| sachin   |   11                |
| female   |   12                |
| tennis   |   12                |
| 21-30    |   12                |
+----------+---------------------+

请指教。

Answer 1

您可以使用 join 执行此操作:

SELECT vans, icollaborate_id 
FROM t join
     (select distinct icollaborate_id
      from t
      order by icollaborate_id
      limit 3
     ) tokeep
     on t.icollaborate_id = tokeep.icollaborate_id
order by icollaborate_id;

编辑:

顺便说一下，以下查询可能会执行您想要的操作:

select icollaborate_id, group_concat(vans)
from t
group by icollaborate_id
limit 3;

这会将所有 vans 放在逗号分隔列表中的一行中。