mysql - 在按 group by 子句分组的每个组中仅选择 3 个具有 uniq 属性的第一个加入的项目

Question

我有简单的 sql:

SELECT foos.*, bars.* FROM foos 
  LEFT JOIN bars ON bars.foo_id = foos.id
  WHERE foos.id = 1;

=>

+------------------------------------+
| foos.id | bars.id | bars.author_id |
+------------------------------------+
|    1    |    1    |       10       |
|    1    |    3    |       10       |
|    1    |    5    |       3        |
|    1    |    6    |       10       |
|    1    |    7    |       10       |
|    1    |    8    |       10       |
|    1    |    44   |       11       |
|    1    |    32   |       10       |
+------------------------------------+

现在我需要返回的不是所有连接的 bars，而是每个 bars.author_id 的前三个(切片)，因此它可以有效地返回类似这样的东西

+------------------------------------+
| foos.id | bars.id | bars.author_id |
+------------------------------------+
|    1    |    1    |       10       |
|    1    |    3    |       10       |
|    1    |    5    |       3        |
|    1    |    6    |       10       |
|    1    |    44   |       11       |
+------------------------------------+

Answer 1

我有一个很好的解决方案:

For, selecting 1st 3 record within each group 
1) sorting the results asc/desc by applying `order by bars.id` within group_concat()
2) limiting the records by passing the 3rd parameter as the records to limit to SUBSTRING_INDEX(str,'match str','no of records to limit') 

SELECT foos.id, 
       SUBSTRING_INDEX(GROUP_CONCAT(bars.id
                       order by bars.id),',',3),
       bars.author_id

FROM foos LEFT JOIN bars ON bars.foo_id = foos.id

WHERE foos.id = 1
GROUP BY bars.author_id

结果是:

+------------------------------------+
| foos.id | bars.id | bars.author_id |
+------------------------------------+
|    1    |    1,3,6    |       10   |
|    1    |    5        |       3    |
|    1    |    44       |       11   |
+------------------------------------+

稍后，在应用程序端，您可以通过 ',' 分解它并使用它。