MySQL中的数组切片/ group_concat限制

Let's say I have a table:

假设我有一张桌子:

i j
---
a 1
a 2
a 3
a 4
b 5
b 6
b 7
b 8
b 9

Obvoiusly SELECT a, GROUP_CONCAT(b SEPARATOR ',') GROUP BY a would give me

Obvoiusly SELECT a,GROUP_CONCAT(b SEPARATOR',')GROUP BY a会给我

a  1,2,3,4
b  5,6,7,8,9

But what if I want to get only a LIMITED number of results, like 2, for example:

但是,如果我只想获得有限数量的结果,例如2,该怎么办?

a  1,2
b  5,6

Any ideas?

2 个解决方案

#1

Best way is to use SUBSTRING_INDEX() and GROUP_CONCAT().

最好的方法是使用SUBSTRING_INDEX()和GROUP_CONCAT()。

SELECT i, SUBSTRING_INDEX( GROUP_CONCAT(j), ',', 2)
FROM mytable
GROUP BY i;

You don't have to know the length of j field here.

你不必在这里知道j字段的长度。

#2

One solution would be to limit the rows in the group to the top two, before computing the aggregate.

一种解决方案是在计算聚合之前将组中的行限制为前两个。

SELECT t.i, GROUP_CONCAT(t.j)
FROM
  (SELECT t1.i, t1.j
   FROM mytable AS t1
     LEFT JOIN mytable AS t2
       ON (t1.i = t2.i AND t1.j >= t2.j) 
   GROUP BY t1.i, t1.j
   HAVING COUNT(*) <= 2) AS t
GROUP BY t.i;

Another solution, if you know the values in j are of a fixed length, is to simply use SUBSTRING() on the result:

另一种解决方案,如果您知道j中的值具有固定长度,则只需对结果使用SUBSTRING():

SELECT i, SUBSTRING( GROUP_CONCAT(j), 1, 3 )
FROM mytable
GROUP BY i;

#1